I would like to send a Excel chart in the body of an email (Outlook) (not as attachment) from VB, anyone know how to do this?
Solved:
Just to add a bit more detail to answer below
you'll need the following (could do with some improvement).
Sheets(2).ChartObjects(1).Chart.Export "C:\temp\Chart2.png"
....
.HTMLBody = "<html xmlns:o='urn:schemas-microsoft-com:office:office'" & _
"xmlns: x = 'urn:schemas-microsoft-com:office:excel'" & _
"xmlns='http://www.w3.org/TR/REC-html40'> " & _
"<head></head><body><img src='Chart2.png'></body></html>"
and
.Attachments.Add ("C:\temp\Chart2.png")
Seems like the best way is to export the chart:
Sheets(1).ChartObjects("Chart 1").Chart.Export "C:\Chart1.png"
And then add the image into your mail HTML body:
.HTMLBody = .HTMLBody & "< img src='c:\folder\filename.png'>"
Confirmed by both technet and mrexcel
I thought I'd add my solution here in addition to the one above since it is using both a sub and a temporary file path, in case you are interested.
I changed the code from here to insert a chart using a temporary path (they do the same thing using a range of cells in the workbook):
Sub createGraph(nameSheet As String, nameChart As String, nameFile As String)
ThisWorkbook.Activate
Worksheets(nameSheet).Activate
With ThisWorkbook.Worksheets(nameSheet).ChartObjects(nameChart)
.Activate
.Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
End With
End Sub
In the HTML body put:
Call createGraph("Graphs", "Chart 1", "filename")
TempFilePath = Environ$("temp") & "\"
.Attachments.Add TempFilePath & "filename.jpg", olByValue, 0
.HTMLBody = .HTMLBody & "<img src='cid:filename.jpg'>"
By using "olByValue, 0" the attachment is added at position 0 and not visible in the email as an attachment.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With