Email charts using Excel VBA

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
    
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' CREATE EMAIL OBJECT.
    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     ' DISPLAY MESSAGE.
    End With
    
    ' CLEAR.
    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