VBA – send an e-mail from Excel, using MS Outlook and HTML

With this article I will simply present you with a solution, combining HTML and VBA, exporting a graph from excel to MS Outlook e-mail.

The HTML part is a little complicated and not very flexible, but anyhow – it is just an example that HTML can be incorporated in VBA.

So here comes the code:

Sub mailHTMLsend()
    Dim olMail As MailItem
    Dim objOL As Object
    Dim chrtpth As String
    Dim bdy As String
    Dim startmsg As String
    Dim endmsg As String

    ' create a unique Name
    chrtpth = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    'Change chart name which you want to export
    Sheets("Sheet1").ChartObjects("Chart 1").Chart.Export chrtpth

    ' add the mail content and chart to the outlook mail body

    bdy = "<p align='Left'><img src=""cid:" & Mid(chrtpth, InStrRev(chrtpth, "\") + 1) & """  width=700 height=500 > <br> <br>"
    startmsg = "<font size='5' color='black'> Hi Leo," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
    endmsg = "<font size='4' color='black'> Many Thanks," & "<br>" & "Cristiano Ronaldo" & "<br> <br> </font>"

    ' send the email

    Set objOL = CreateObject("Outlook.Application")
    Set olMail = objOL.CreateItem(olMailItem)

    With olMail
        .To = "leomessi@barcelona.com"
        .Subject = "Add Chart in outlook mail body"
        .Attachments.Add chrtpth
        .HTMLBody = startmsg & bdy & endmsg
        .Display
    End With

    ' delete the exported chart
    Kill chrtpth

    Set olMail = Nothing
    Set olApp = Nothing

End Sub

It is not my best piece of code, with a lot of hard coding in it, but the idea is that it should be just an example.

Here are a couple of things, you should do before running this code:

1. Add the MS Outlook library to MS Excel. Simply open the VB Editor, click on Tools, then References.

2. Then select “Microsoft Outlook 14.0. Object Library (or something that sounds similar, if you are not using Office 2010)

3. Open MS Outlook and leave it opened.

4. Create an empty Excel file and simply save it somewhere. (it should be saved, the macro does not work otherwise)

5. Create some simple chart on the first sheet and do not change the name of the first sheet.

Then the macro works!

If you are tired/lazy/unwilling to do the five last steps, you may download a working version of this tool from here.

That is it! Enjoy the file and do not become spammers 🙂