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
Wait
sImgPathService = ThisWorkbook.Path & "\Temp_" & Format(Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
Sheets("Service Type").ChartObjects(1).CHART.Export sImgPathService
Wait
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
.Display
End With

'Delete the saved chart
Kill sImgPathAsset
Wait
Kill sImgPathService
Wait
Kill sImgPathBroker

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

End Sub

Open in new window

eastsidemarketAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
hI

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

Regards
0
 
Rgonzo1971Commented:
Hi,

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

Regards
0
 
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..
0
 
eastsidemarketAuthor Commented:
AHH TYPO. THANKS MAN!!!
0
 
eastsidemarketAuthor Commented:
pleasure to deal with as always. very knowledgeable!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.