Adding a chart as an attachment or part of the body in an email via Excel VBA

Question

I want to attach a chart in the email when the user clicks on a button.

The code isn't adding the chart.

The naming is correct and I am not receiving any errors (except ones I've implemented to help test).

If ChartNameLine = "" Then
    GoTo ErrorMsgs
Else
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xChartName As String
    Dim xPath As String
    Dim xChart As ChartObject
    Dim xChartPath As String
    
    On Error Resume Next
    xChartName = Application.InputBox("Please Enter the Chart name: ", "KuTools for Excel", , , , , , 2)
    'xChartName = ChartNameLine
    Set xChart = Worksheets(.HTMLBody).ChartObjects(xChartName)
    xChart.Chart.ChartArea.Copy
    
    errorCode = 101
    'If xChart Is Nothing Then GoTo ErrorMsgs
    
    xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xChartPath = ThisWorkbook.path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    xPath = "<p align='Left'><img src= " / "cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """ width = 800 height = 500> <br> <br>"
    xChart.Chart.Export xChartPath
    With xOutMail
        .To = ToLine
        .Subject = SubjectLine
        .Attachments.Add xChartPath
        .HTMLBody = xPath
        .Display
    End With
    Kill xChartPath
    'Set xOutMail = Nothing
    'Set xOutApp = Nothing
End If

Using code from "Extend Office"

You might consider commenting out the `On Error Resume Next` to debug. If this is the entire code block, there is likely an issue with the `Set xChart` line, since it does not appear to be within a `With` block (what should `.HTMLBody` be?) — Professor Pantsless, Jan 19 '22 at 20:18
@CDP1802 The email is being sent. It just doesn't set the chart properly — Breezy1, Jan 19 '22 at 20:29
@ProfessorPantsless The .HTMLBody is to grab the sheet name, which can be changed with "Test" — Breezy1, Jan 19 '22 at 20:30
Change `Worksheets(.HTMLBody)` to `Worksheets("test")`. Also change `src= " / "cid:" ` to `src=""cid:" ` check that the BMP is created. — CDP1802, Jan 19 '22 at 20:46
@CDP1802 Nothing changes when i do that. It still ends up creating an email with all the info including the data table except the chart — Breezy1, Jan 19 '22 at 21:31
I can confirm the problems were fixed in the comment from CDP1802. "Change `Worksheets(.HTMLBody)` to `Worksheets("test")`. Also change `src= " / "cid:"` to `src=""cid:"` ...". If you still have `On Error Resume Next` in your code remove it to see if errors are being hidden. — niton, Jan 20 '22 at 00:01
When I remove/comment out the error resume next, I can see that the xchartname stays blank and so the xchart is nothing — Breezy1, Jan 20 '22 at 01:06
xchartname cannot be blank you entered it with `xChartName = Application.InputBox("Please Enter the Chart name: ", "KuTools for Excel", , , , , , 2)`. Edit the question to add the updated code. Include the error message and indicate the highlighted line. — niton, Jan 20 '22 at 19:01
is this what you’re trying to do? https://stackoverflow.com/a/48897439/4539709 — 0m3r, Jan 21 '22 at 03:06
@niton that's the problem i was having. But it may have been the order i implemented the code. The new issue i am being presented is the following: 'The linked image cannot be displayed. The file may have been moved, renamed or deleted. Verify that the link points to the correct file and location.' — Breezy1, Jan 21 '22 at 15:32
@0m3r, not exactly i want to send a chart that I've made in excel. However it either doesn't appear in the email or I run into the error I wrote above. I am not writing it as a sub but within a function — Breezy1, Jan 21 '22 at 15:44

score 0 · Answer 1 · answered Jan 19 '22 at 22:24

First of all, the HTMLBody property returns or sets a string representing the HTML body of the specified item. If you need to add an image generated in Excel you most probably need to insert it at some point in the message, not substitute the whole message by setting it to the paragraph HTML tag. So, find a suitable place in the HTML document and insert the generated HTML piece there instead of replacing the whole message body.

Second, make sure a correct image is generated and saved to the disk. And there were no problems with image generation process from the Excel side.

Third, you may need to set the PR_ATTACH_CONTENT_ID property on the attached image so Outlook could easily recognize the embedded image.

Could you further explain what you mean when talking about inserting the image in the message. Keep in mind it is still a chart value. I havent saved the chart as an image or nothing it is within the excel File. — Breezy1, Jan 20 '22 at 15:29

score 0 · Accepted Answer · answered Jan 21 '22 at 15:58

Create a chart in a new workbook with a sheet named "test". The chart should be named "Chart 1".

With no other code in the new workbook.

Option Explicit ' Consider this mandatory
' Tools | Options | Editor tab
' Require Variable Declaration
' If desperate declare as Variant

Sub AddWorksheetTestChartToMail()
    
    Dim xOutApp As Object
    Dim xOutMail As Object
    
    Dim xChartName As String
    Dim xPath As String
    Dim xChart As ChartObject
    Dim xChartPath As String
            
    xChartName = "Chart 1"
    
    ' "test", not .HTMLBody
    Set xChart = Worksheets("test").ChartObjects(xChartName)
    
    xChart.Chart.ChartArea.Copy
    
    ' Set was missing
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    xChartPath = ThisWorkbook.Path & "\" & Environ("USERNAME") & VBA.Format(VBA.Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
    Debug.Print xChartPath
    
    ' suggested fix in comment on the question post - src=""cid:"
    xPath = "<p align='Left'><img src=""cid:" & Mid(xChartPath, InStrRev(xChartPath, "\") + 1) & """  width=700 height=500 > <br> <br>"
    
    xChart.Chart.Export xChartPath
    
    With xOutMail
        .To = "ToLine"
        .Subject = "SubjectLine"
        .Attachments.Add xChartPath
        .HTMLBody = xPath
        .Display
    End With
    
    Kill xChartPath

End Sub

Adding a chart as an attachment or part of the body in an email via Excel VBA

2 Answers2

Linked