Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to send an embedded image in email from excel

Tags:

excel

outlook

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")
like image 564
aronp Avatar asked Apr 23 '12 12:04

aronp


2 Answers

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

like image 88
JMax Avatar answered Sep 19 '22 12:09

JMax


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.

like image 35
s-heins Avatar answered Sep 21 '22 12:09

s-heins