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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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 🙂