Sending Email With Picture Using Outlook through Excel VBA Macro

Rating 4.50 out of 5
[?]

One of my collegues wanted me to write an Excel VBA Macro to send same or similar (differing in the salutation, etc) emails taking one address at a time which he has added in an MS-Excel file. So in short, we need a program that reads something called a To List from the an MS-Excel file, modifies the mail slightly by changing the subject and the salutation etc. The code is simple and can be obtained from any website which gives tutorials in MS-Excel VBA. I wanted to go a step furthur and add pictures to the email body that we are sending. MS-Excel VBA is more kind of a self taught thing for me and you can do same by creating objects belonging to different class and exploring the attributes and function attached to them.

Coming back to the issue in hand. I have presented the code below which I have added as a Worksheet level macro to the only Worksheet that is present in the MS-Excel file (just to simplify stuff, you can complicate the same at your level or create a new code all together). The code I have written sends an HTML message (the workaround I thought of to kind of embed the image in the email). HTML also gave me greater flexibility for designing the mail in anyway I wanted to. The message is almost identical for each of the recipients whose email Id has been specified in a Range that is named “ToList” in the code and the email is sent individually to each of the recipients using a For Each Loop. I have messed up the code with lots of inline comments, which I feel should help even though the code is self explanatory. Following is the code

Sub emailingProgram()
    Dim olapp As Outlook.Application
    Dim objmail As Outlook.mailitem
    Dim pos As Integer
    Set olapp = Outlook.Application
    For Each xcell In Sheets("Sheet1").Range(Range("tolist"), _
        Range("tolist").End(xlDown))
        msgText = Range("Msg")
        xcell.Activate
        ActiveCell.Offset(0, 1).Select
        'If you think that the email ID is in the pattern firstname.lastname@mail.com use this if block
        'The code will go into the else statement if the First Name is not mentioned
        If Selection.Value = "" Then
            pos = InStr(1, xcell.Value, ".")
            Fname = Mid$(xcell.Value, 1, InStr(1, xcell.Value, ".") - 1)
        Else
            'If you have mentioned the first names in the First Name column this part will read it directly
            Fname = Selection.Value
        End If
        'For each of the cells present in the To List we create a MailItem and send it
        Set objmail = olapp.CreateItem(olMailItem)
        objmail.BodyFormat = olFormatRichText
        'Setting the subject, I have kept a Happy Birthday, Change as per your wish
        objmail.Subject = "Happy Birthday " + UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2)
        
        'Uncomment the following line of code in case you want to send a plain message
        'objmail.Body = "Hi " + UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) + "," + Chr(13) + Chr(10) + msgText
        
        'For using an image in your mail or an HTML body for styling
        objmail.HTMLBody = "<p><font size='6' face='arial' color='red'><i>Dear " & UCase(Mid$(Fname, 1, 1)) + Mid$(Fname, 2) & "<br></font></p><br><p align='CENTER'><font size='5' face='COMIC SANS' color='RED'>Wishing you a Wonderful Birthday</p><br><br></font><p align='CENTER'><a href='http://www.abrahamsarah.com'><img src='http://www.abrahamsarah.com/bilder/Happy-Birthday005.png' width=450 height=412 border=0></a></a><br><br><br><p align='left'>Thanks & Regards <br><br/> _<p><p align='left'><br>Anshuman Pandey<br>http://www.anshumusing.co.in/</p>"
        objmail.To = xcell.Value
        objmail.Send
        Set objmail = Nothing
        
    Next xcell
End Sub

Just incase you get confused with the code and the Ranges I have named as it is not a stand alone module, here is a MS-Excel file that uses this code.

Word of Caution: To execute this code, you need to choose additional libraries. For choosing libraries, from the menu bar in the VBE go to Tools -> References and make sure that the check boxes are marked as shown below:

 

 

Anshuman

About Anshuman Pandey

Computer Engineer ... Endurance Runner ... Physics Geek ... Genius
Bookmark the permalink.

21 Comments


  1. thx for the code ,really usefull ,
    BR Igor


  2. Very useful code. Thanks!


  3. Thanks this is very useful and simple code.

  4. Pingback: Help needed to insert the Image on Body of the mail


  5. Thanks for the awesome code


  6. Thanks for the Gud code……..


  7. grt post. It will be helpful… but, how to send a mail based on a condition?

    i have a Master Sheet which contains employee names and their time sheet names along with their email’id’s. This master sheet consists of all the time sheets of employees in it. if it is missing anyone of the employee’s time sheet, then an automatic mail should be sent to that particular employee regarding the submission of Time sheet. How to Accomplish dis?


  8. Hi Kishore, what i can make out of your problem statement is that there is some kind of a table where for each employee three details are kept – Name, email ID, timesheet ID. And you want to trigger a mail when the Timesheet ID is blank.
    For this add an if statement just after a statement like
    timesheetID=ActiveCell.Offset(0, ).
    so if the timesheetID is two columns to the right of the To_list then the statement will be like
    timesheetID=ActiveCell.Offset(0, 2). If the column is on the left use -2
    Now put the if statement
    if timesheetID =”" then

    end if

    Hope this helps. I would really want you to try once coding, because what you require is really simple. Else just email me the code(use contact Me page) you made and I’ll help you.

    Cheers,


  9. Hi Please help me for this scenario,
    I want a macro to copy a certain range of data from my excel sheet to outlook msgbody area automatically. i tried lots of codes in my project but that all codes were not help me that much. i dont want to sent my whole worksheet automatically i just want to send certain data using outlook, Please help me for this scenario,


    • Hey Karthikeyan,
      What you are aiming for is actually simple and I have made a provision in the code for this already. For this you need to check out the Line No. 8, I have initialized this variable but have commented the use of it in line no. 26. You can uncomment this line and comment the line that has the HTML body in it, i.e. Line No.30.

      I hope this helps
      Cheers


  10. I have the follwoing information to be listed in an e-mail,
    e.g Name | designation|PERIOD|REAMARKS| picked up from the excel-sheet:
    It comes like this:
    xxxxxxxxxxxxxxxxxx|yyyyyyy|88-88-88|zzzzzzzz
    xxxxxxx|yyyyyyy|88-88-88|zzzzzzzzzzzzz
    xxxxxxxxxxx|yyyyyyyy|88-88-88|zzzzzz
    xxxxxxxxxxxxxx|yyyyyyy|88-88-88|zzzzzzz

    How can I format this to:
    xxxxxxxxxxxxxxxxxx|yyyyyyy |88-88-88|zzzzzzzz
    xxxxxxx |yyyyyyy |88-88-88|zzzzzzzzzzzzz
    xxxxxxxxxxx |yyyyyyyy|88-88-88|zzzzzz
    xxxxxxxxxxxxxx |yyyyyyy |88-88-88|zzzzzzz
    I am using .hrmlbody, thank you for your feedback.
    cheers


    • Hey Ramesh,
      What I could make out of your query was that you can just make four variables name, designation, period and remarks and since these will appear in different columns themselves in excel put a counter on column too.

      Cheers


  11. How can I copy an image from the Excel itself and send it in an outlook mail?


  12. hi Anushuman

    i have a file in which it contains both DOB as well as DOA what i have to do is i need to create a file in which if i click birthday macro button it should send only to the person who celebrates the birthday, if i click anniversary then it should send to the specific person. the subject, msg body, every thing should automatically change. can you assists me in this

    Appreciate your help

    Thanks in advance

    Regards

    Sara Ruksana


    • Hi Ruksana,

      Well, what I think as a best solution is just an IF statement, take any cell and use the formula =TODAY or =NOW and run a for loop through the various rows of DOB or DOA and see if the value of the one in the loop is equal to the value in the cell that has today’s date.
      As for DOB and DOA different execution, you could just have 2 macros and 2 different buttons to call them. In DOB macro you compare DOB column values with =TODAY and in DOA macro you use DOA column values and compare them with =TODAY.


  13. I’ve tried using the same syntax for changing the size of the font but anytime I include the size code in the attribute the font size become 36. If I remove that portion of the code I am still able to change the font but then the size defaults to 12. I need the size to be 11 – I don’t understand why this is working for everyone else and not me:

    is what I’m using (I’ve determined that no apostrophes or quotes are needed with all of the trials I’ve run).

    Is there another setting I have to change? I’ve made sure all of the libraries were added.

Liked it? Share Your Thoughts!