vba chart to email help

Having some issues with my chart to email vba macro.

Sometimes (but not everytime) it uses the chart image from:
sImgPathService FOR sImgPathAsset.

Also sometimes it attaches the .bmp for sImgPathService in the email and not in the body like the rest of the images.

I can't seem to figure it out. I tried to add a few wait commands to pause a bit , thinking maybe it was too fast.. but that didnt help.

Appreciate any help.

Sub SendChartThroughMail()
Dim olMail As MailItem
Dim objOL As Object
Dim sImgPathAsset As String
Dim sImgPathService As String
Dim sImgPathBroker As String
Dim sBegin As String
Dim sBody As String
Dim sBody2 As String
Dim sBody3 As String
Dim sInternal As String
Dim sBottom As String
Dim Cust As String
Dim TotalAssets As String
Dim TotalServiceType As String

Cust = Worksheets("Results").Range("R2").Value
TotalAssets = Worksheets("Assets").Range("G5").Value
TotalServiceType = Worksheets("Service Type").Range("G5").Value

' Saving chart as image
sImgPathAsset = ThisWorkbook.Path & "\Temp_" & Format(Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
Sheets("Assets").ChartObjects(1).CHART.Export sImgPathAsset
sImgPathService = ThisWorkbook.Path & "\Temp_" & Format(Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
Sheets("Service Type").ChartObjects(1).CHART.Export sImgPathService
sImgPathBroker = ThisWorkbook.Path & "\Temp_" & Format(Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
Sheets("GI By Broker").ChartObjects(1).CHART.Export sImgPathBroker

'creating html body with image
sBegin = "<font size='3' color='black'><B>Client Analysis: " & Cust & "<br><br><br><br></b></font>"

sBody = "<font size='3'><B>Volume By Asset Type</b>:<BR><p align='Left'><img src=""cid:" & Mid(sImgPathAsset, InStrRev(sImgPathAsset, "\") + 1) & """ width=400 height=300 > <br>" & "<font size='2'>Total Volume: " & TotalAssets & "<BR><BR><BR>"
sBody2 = "<font size='3'><B>Volume By Service Type</b>:<BR></font><font size='2'>(Give Out vs Give In vs Full Service)<BR></font><p align='Left'><img src=""cid:" & Mid(sImgPathService, InStrRev(sImgPathService, "\") + 1) & """ width=400 height=300 > <br>" & "Total Volume: " & TotalServiceType & "<BR><BR><BR>"
sBody3 = "<font size='3'><b>Give In By Broker</b>:<BR></font><p align='Left'><img src=""cid:" & Mid(sImgPathBroker, InStrRev(sImgPathBroker, "\") + 1) & """ width=600 height=400 ><BR><BR><BR>"

sInternal = "<font size='3'><B>" & "INTERNAL ONLY<br></b></font>"

sBottom = "<font size='1'>" & "Report Creation: " & Date & " - " & Time & "<br></font>"

'sending the email

Set objOL = CreateObject("Outlook.Application")
Set olMail = objOL.CreateItem(olMailItem)

With olMail
.To = "emailhere@email.com"
.Subject = "Client Analysis: " & Cust & " (" & Date & ")"
.Attachments.Add sImgPathAsset
.Attachments.Add sImgPathService
.Attachments.Add sImgPathBroker
.HTMLBody = sBegin & sBody & sBody2 & sGBody3 & sInternal & sBottom
End With

'Delete the saved chart
Kill sImgPathAsset
Kill sImgPathService
Kill sImgPathBroker

'Free-up the objects
Set olMail = Nothing
Set olApp = Nothing

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


The only difference between your filenames is the time and date

sImgPathAsset, sImgPathService,sImgPathBroker

I think you should at least have a letter of difference like A, S or G

eastsidemarketAuthor Commented:
thanks Rgonzo1971. I tried that and seems to clear up the image mixups. However the 3rd chart image is still adding as an attachment (sImgPathBroker) and not in the body of the email,.

any ideas?

also are all the Wait commands really helping anything? that was just a guess of mine..

I think the wait was only necessary to counter the mixup

please correct

.HTMLBody = sBegin & sBody & sBody2 & sBody3 & sInternal & sBottom

Open in new window

instead of
.HTMLBody = sBegin & sBody & sBody2 & sGBody3 & sInternal & sBottom

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eastsidemarketAuthor Commented:
eastsidemarketAuthor Commented:
pleasure to deal with as always. very knowledgeable!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.