eastsidemarket
asked on
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.
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
ASKER
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..
any ideas?
also are all the Wait commands really helping anything? that was just a guess of mine..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AHH TYPO. THANKS MAN!!!
ASKER
pleasure to deal with as always. very knowledgeable!
The only difference between your filenames is the time and date
sImgPathAsset, sImgPathService,sImgPathBr
I think you should at least have a letter of difference like A, S or G
Regards