Hi All,
Please help me to automate sending of email from excel file. In the body of the email, I would like to include my pivot table and chart from sheet("Graph"). I'm using outlook 2010. Any help will do. Thank you
I found an example here but my chart is not showing on the body of the email.
Please help me to automate sending of email from excel file. In the body of the email, I would like to include my pivot table and chart from sheet("Graph"). I'm using outlook 2010. Any help will do. Thank you
I found an example here but my chart is not showing on the body of the email.
Rich (BB code):
Sub mailHTMLsend()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xStartMsg As String
Dim xEndMsg As String
Dim xChartName As String
Dim xChartPath As String
Dim xPath As String
Dim xChart As ChartObject
On Error Resume Next
xChartName = Application.InputBox("Please enter the chart name:", "KuTools for Excel", , , , , , 2)
If xChartName = "" Then Exit Sub
Set xChart = Sheets("Pivot").ChartObjects(xChartName)
If xChart Is Nothing Then Exit Sub
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xStartMsg = "<font size='5' color='black'> Good Day," & "<br> <br>" & "Please find the chart below: " & "<br> <br> </font>"
xEndMsg = "<font size='4' color='black'> Many Thanks," & "<br> <br> </font>"
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=700 height=500 > <br> <br>"
xChart.Chart.Export xChartPath
With xOutMail
.To = "Bryan.Carpio@uk.qbe.com"
.Subject = "Add Chart in outlook mail body"
.Attachments.Add xChartPath
.HTMLBody = xStartMsg & xPath & xEndMsg
.Display
End With
Kill xChartPath
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Last edited by a moderator: