Adding multiple charts in email body using vba

Question

Hi there so I am working on project and I want to add charts from excel into the body of the email using VBA. Now the code is working and I am able to copy over one chart, however when I add a for loop around it, the output is just the same three charts and the other charts end up being added as attachments.

If someone could help me output multiple charts in the body that'd be great. I've added the code below:

UserChartOpt = Application.InputBox("Would you like to add charts to the email?", "User Chart Option.", , , , , , 2)
         If UserChartOpt = "Yes" Or UserChartOpt = "yes" Then
            
           'This line will be used later to help loop when attaching the charts into the body of the email (as of now doesn't change anything)
          NumbOfChart = Application.InputBox("How many charts do you want to insert?", "Number of Charts:", , , , , , 2)
          
          
          
          xChartName = ChartName
                                                             
                                                             
            'errorCode = 29
            'If xChartName = "" Or ChartSheet = "" Then GoTo ErrorMsgs
            

            Set xChartSheet = ThisWorkbook.Sheets(ChartSheet)
            Set xChartObj = xChartSheet.ChartObjects(xChartName)
            
            
            For Each xChartObj In xChartSheet.ChartObjects
    
           
            ChartPath = ThisWorkbook.path & "\" & Environ("Charts") & VBA.Format(VBA.Now(), "DD_MM_YYYY") & ".bmp"
            Pth = "<p align='Left'><img src= ""cid:" & Mid(ChartPath, InStrRev(ChartPath, "\") + 1) & """ width = 350 height = 200> <br> <br>"
            xChartObj.Chart.Export ChartPath
            
            .attachments.Add ChartPath
            tempBody = Pth & tempBody
            Next xChartObj
            '.Display
            'Kill ChartPath
            
            'errorCode = 11
            If HTMLBodyRange = "" Then
                .HTMLBody = RangetoHTML(Sheets(HTMLBodySheet).UsedRange)
            Else
                .HTMLBody = RangetoHTML(Sheets(HTMLBodySheet).Range(HTMLBodyRange))
            End If
            'Just to help email look more professional, however the third line is what allows the charts to display as an image rather than an attachment
            OpeningMsg = "<font size='2.5' color='black'> Hi There," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
            ClosingMsg = "<br><br>" & "<font size =2.5' color='black'> Cheers, </font>"
            .HTMLBody = OpeningMsg & tempBody & .HTMLBody & ClosingMsg

[Edit](https://stackoverflow.com/posts/70866858/edit) the question. [mcve]. Remove all the `errorCode =` lines. The code should be in a condition to allow potential responders to attempt to run it, similar to https://stackoverflow.com/a/70804126/1571407. — niton, Jan 26 '22 at 16:42
Do not use `HTMLBody` property as a variable that you can concatenate with in a loop. Use a dedicated variable, and set the `HTMLBody` property only once when you are out of the loop. — Dmitry Streblechenko, Jan 26 '22 at 16:44
Also, look at the resulting HTML body too see what is wrong with it. — Dmitry Streblechenko, Jan 26 '22 at 16:45
surely `VBA.Format(VBA.Now(), "DD_MM_YYYY")` will be the same for all charts try `VBA.Format(VBA.Now(), "DD_MM_YYYY_") & xChartObj.Index & ".bmp"` — CDP1802, Jan 26 '22 at 17:07

score 0 · Answer 1 · answered Jan 26 '22 at 16:35

Most probably you also need to set the PR_ATTACH_CONTENT_ID property in the code on the attachments:

Const PR_ATTACH_CONTENT_ID As String = "http://schemas.microsoft.com/mapi/proptag/0x3712001F"

And then in the code you may refer to the property value (cid:).

attachment = MailItem.Attachments.Add("c:\temp\chart.jpg")
attachment.PropertyAccessor.SetProperty("http://schemas.microsoft.com/mapi/proptag/0x3712001F", "YourChart1")
MailItem.HTMLBody = "<html><body>Test image <img src=""cid:YourChart1""></body></html>"

Read more about that in the Distinguish visible and invisible attachments with Outlook VBA thread.

Okay thanks everyone, ill give it ago and let everyone know how it goes. — Breezy1, Jan 26 '22 at 16:57

Adding multiple charts in email body using vba

1 Answers1