While I was doing my project, I got a request from the client, to include charts in the mails along with other content which is being shared earlier.
As we all are so much into google, so I did some research on how we can do this.
Following are some nice examples that really helped me.
For
simple mail, we all do this
Option Explicit
Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.to = "abc@xyz.com"
.Subject = "This is a test message"
.Body = "Hi there"
.Display
End With
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
End Sub
Now for including a
chart following are the examples:
Example 1
This example sends a chart with the name "Chart 1" from "Sheet1" of the ActiveWorkbook.
It will save My_Sales1.gif in the temp folder, send the mail and delete My_Sales1.gif after that.
Sub SaveSend_Embedded_Chart()
Dim OutApp As Object
Dim OutMail As Object
Dim Fname As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'File path/name of the gif file
Fname = Environ$("temp") & "\My_Sales1.gif"
'Save Chart named "Chart 1" as gif file
'If you hold down the CTRL key when you select the chart
'in 2000-2013 you see the name in the Name box(formula bar)
ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Export _
Filename:=Fname, FilterName:="GIF"
On Error Resume Next
With OutMail
.To = "abc@xyz.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add Fname
.Send 'or use .Display
End With
On Error GoTo 0
'Delete the gif file
Kill Fname
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Example 2
This example sends a chart sheet with the name "Chart1" from the ActiveWorkbook.
It will save My_Sales2.gif the temp folder, send the mail and delete My_Sales2.gif after that.
Sub SaveSend_Chart_Sheet()
Dim OutApp As Object
Dim OutMail As Object
Dim Fname As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'File path/name of the gif file
Fname = Environ$("temp") & "\My_Sales2.gif"
'Save Chart sheet named "Chart1" as gif file
ActiveWorkbook.Sheets("Chart1").Export _
Filename:=Fname, FilterName:="GIF"
On Error Resume Next
With OutMail
.To = "abc@xyz.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add Fname
.Send 'or use .Display
End With
On Error GoTo 0
'Delete the gif file
Kill Fname
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
to temporarily hide the dialog box, select cell D1 on the worksheet, and then press Expand Button 
.
