+ Reply to Thread
Results 1 to 24 of 24

Macro to send email from excel using outlook

  1. #1
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Macro to send email from excel using outlook

    Hi All,

    I have found a few VBA scripts online to send an email from excel, however I ran into some problems. The script I have doesn't include text in the subject line of the email. I also would like to choose who the email is being sent to by writing their email address in A1. Is anyone able to write a script to do this?

    Key things to include:
    I want the email to be sent to the email addresses in column A1:A10
    I want the cc to be the email addresses in column B1:B10
    I would like the subject of the email to be the text from C2
    I would like the body of the email to include whatever is in D:D

    I am almost positive this is possible, but I can't find the exact criteria with a google search.

    Thanks

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Actually including all 1.04 million cells in Column D doesn't seem reasonable, so I scaled it down to 500 cells. You may adjust the ranges as needed.

    Please Login or Register  to view this content.
    Last edited by daffodil11; 02-05-2015 at 07:38 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Slight changes, for cosmetic effect and useability:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    Scaling it down to 500 was a great idea. I also appreciate the sub name haha. The computer I will be using this on is at work. I will test it out in the morning. Scanning through the script I can see my criteria is all there, so as long as it goes through properly I am sure it will be perfect. Thanks for the quick reply!

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Hurray for fun codes! Glad I could help.

  6. #6
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    I am getting a runtime error. It appears the below is an issue?

    For Each cell In Range("AX1:AX500")
    If cell = "" Then Exit For
    If Len(strMailBody) = 0 Then
    strMailBody = cell
    Else: strMailBody = strMailBody & " " & cell
    End If

    I changed the cell ranges around, but I didn't touch anything else. Could this cause an issue?

    AX1:AX500 is the range for the body.
    AZ2:Az11 is the mailto.
    BA2:BA11 is the cc.
    BB2 is the subject.

    Also, is there a way to make it send automatically?

  7. #7
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    The automatic part was commented into the code, silly.

    Please Login or Register  to view this content.
    Every line that appears green in code is a comment. These are just extra directions and notes. The comment in my code lets you know you can change .Display to .Send to just send without notifice.

    Please Login or Register  to view this content.
    You can even take it a step further and encapsulate the whole procedure with ScreenUpdating disabled, so you don't even see windows pop up or anything.


    Find code, with sample attached.

    Please Login or Register  to view this content.
    My example still has .Display instead of .Send because I don't change that until I'm positive everything works.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    I have to fix my workbook. Then I can include it on here and maybe you could place it in there for me. I am having run time errors pop up.
    Attached Files Attached Files
    Last edited by Teblol; 02-06-2015 at 12:28 PM.

  9. #9
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    I figured out how to get it to work, and it is perfect except for one thing. Can I have the body of the email stack? I have included my workbook so I can describe what I mean.
    I would like the body of the email to read:

    Can I move to 1001?
    223CR 24
    256P 4
    FB5X 8
    HC221RGB 4
    HP15RB 6
    NM8X 4
    SU12-3PK 3
    TH404 2
    Can I move to 1002?
    SWJ1 4
    Can I move to 1011?
    Can I move to 1031?
    Out of stock
    2012HTCS
    BE47X
    BE87CR
    CB433SCS
    CD117BCR
    CS32BX
    GMT8RCS
    HC900R
    NT1
    WW28N
    WW405N
    Not enough to move to 1011

    This is what I would like it to look like.
    Email Example for Help forum.JPG

    EASY VA05 New.xlsm

  10. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Oh hey, I remember working on this workbook in a previous thread. I didn't post because I couldn't come up with a good solution for modifying the index(..small(if.

    Anywho, just replace the blank space concatenation with chr(10). Character 10 is the Enter key, so we can instead concatenate line breaks between items.

    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    It took me a while to come up with a solution for removing the blanks. I kind of stumbled upon the solution.

    The macro works perfectly! I appreciate you walking me through the process. I am a lot better with formulas... I am far away from fully understanding macro's.

  12. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    I'll take a crack at the removing the unnecessary headers as well, probably with code.

    I only started diving into VBA last year, mostly by taking apart other subroutines and finding how to stitch them back together.

    I still can't freehand the Outlook stuff, but just pull from existing codes I've used. It's pretty useful stuff.
    Two weeks ago I made an Excel workbook with a query linked to Access that pulls fresh data everytime the file is opened, then refreshes a linked PivotTable, SaveAs .xlsx with date appended to filename, mails itself via Outlook, and then shuts down, and is keyed to open with Windows Task Scheduler every Monday morning at 5am. Bam, instant hands-free reporting.

  13. #13
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    Wow, that is awesome. I am still amazed at how much can be done automatically with macros. I appreciate you looking into the header issue. Let me know what you come up with. I figure the new results will be listed in another column.

  14. #14
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    This could probably have been done a little bit cleaner with the use of coded arrays, but it's sort of a weak point.

    Instead, I went with more explicit instructions. This procedure will omit all of the dropdown choices if they contain no data below.

    Please Login or Register  to view this content.
    It produced the following Body:
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    You are the man. Seriously, let me know if you are in NJ sometime because I owe you a beer. This is exactly what I was looking for.

  16. #16
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Glad I could help out.

    It was a good distraction to the daily grind.

  17. #17
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    daffodil11,

    After some testing, I have found the body of the email hard to read without spaces. Do you think it is possible to have a space before each of the "headers"? Also, if the headers could be bold as well that would be great.

    Thanks
    Last edited by Teblol; 02-09-2015 at 04:36 PM.

  18. #18
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Hmm. Not quite sure if we can accomplish the bold with late binding, but I'll give it a shot.

    As for the extra spacing, we just tie in

    find the following lines of code:

    Please Login or Register  to view this content.
    and add in an extra "Chr(10) &"

    Please Login or Register  to view this content.
    This only applies to this line of code, and not the one after it. The first line of concatenation applies to headers, and the second applies to normal stuff.

  19. #19
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    Perfect! Don't stress yourself over making them bold. The spacing was the major problem. How does it know to only space before one of the headers? Because we specified them in the code?

  20. #20
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    Took a little bit of research, but I found out the best way: we convert to HTML and use <br> in place of Chr(10), and <b>/</b> for bolds.

    Please Login or Register  to view this content.
    As for the bold and spacing, yes because we called them out earlier in the code. The IF that identifies if a given cell is a header, it adds a preceding blank line and then bolds the word.

  21. #21
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    This is beautiful. The text is a bit ugly. Is there a quick way to adjust the style?

  22. #22
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    No idea, what would you like it to be?

    It's all unexplored territory for me. Posters here are the visionaries, and I'm just the engineer that says "huh, never thought of that. we can probably do that."

  23. #23
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Macro to send email from excel using outlook

    Style = Calibri and Size of font = 12. I spend hours trying to find solutions myself, but unfortunately vba code does not come as easy to me as formulas do.

  24. #24
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Macro to send email from excel using outlook

    A little more research, and we have results!

    We add some code to the final output of the body:


    Please Login or Register  to view this content.
    becomes


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert excel sheet to pdf using macro and send that pdf via email using outlook
    By suresh mongam in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2015, 08:51 AM
  2. Excel macro to send email through outlook when expiry nearing
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2014, 08:07 PM
  3. [SOLVED] Need a Macro to send email from excel through Outlook basis of different conditions
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-11-2014, 10:21 PM
  4. Macro to send notification from excel to my Outlook Email
    By savethisid in forum Excel General
    Replies: 18
    Last Post: 03-27-2014, 02:25 PM
  5. Replies: 1
    Last Post: 10-03-2012, 07:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1