Automate Data Reporting: How to Instantly Create Outlook Emails with Excel Charts Using VBA

First, you need to make sure that your Excel application can communicate with Outlook. This is done by adding a reference to the Microsoft Outlook Object Library in the VBA editor.

a. Open Excel VBA editor by pressing ALT + F11.

b. Go to Tools > References from the menu.

c. Scroll through the list to find Microsoft Outlook XX.0 Object Library & Microsoft Word XX.0 Object Library and check the box beside it. (XX represents the version number).

d. Click OK.

1. Declare Variables:

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Dim ChartName As String
Dim ChartSheet As Worksheet
Dim Message As String
Dim ToAddress As String
Dim CCAddress As String
Dim Subject As String

Here, we're declaring variables to hold the Outlook application, the mail item, the chart's name, the worksheet object, and strings for the email's message, recipient, CC, and subject.

2. Specify Chart and Worksheet:

ChartName = "Bar1"
Set ChartSheet = ThisWorkbook.Sheets("Sheet1") ' change Sheet1 to the name of your sheet

This code specifies the name of the chart and the Excel worksheet that contains the chart and email details. The user must replace "Sheet1" with the actual name of their worksheet.

3. Retrieve Email Details:

ToAddress = ChartSheet.Range("B2").Value
CCAddress = ChartSheet.Range("B3").Value
Subject = ChartSheet.Range("B4").Value
Message = ChartSheet.Range("B5").Value

We're getting the email details from the worksheet. B2 is for the recipient's address, B3 for the CC address, B4 for the email's subject, and B5 for the message body.

4. Create New Outlook Instance:

Set OutlookApp = New Outlook.Application

This line creates a new instance of the Outlook application.

5. Create New Email Item:

Set OutlookMail = OutlookApp.CreateItem(olMailItem)

This line creates a new email item in Outlook.

6. Configure Email Details:

With OutlookMail
    .To = ToAddress
    .CC = CCAddress
    .Subject = Subject
    .BodyFormat = olFormatHTML
    .Display

Here, we're setting the recipient, CC, and subject of the email, and we're specifying the email's body format as HTML. The `.Display` method is used to display the email draft, which allows VBA to access the user's default signature.

7. Copy Chart as Picture:

ChartSheet.ChartObjects(ChartName).Chart.CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

This line copies the specified chart as a picture from the Excel worksheet.

8. Access Word Editor and Paste Content:

Dim WordEditor As Object
Set WordEditor = .GetInspector.WordEditor

WordEditor.Application.Selection.HomeKey Unit:=wdStory
WordEditor.Application.Selection.Paste

WordEditor.Application.Selection.HomeKey Unit:=wdStory
WordEditor.Application.Selection.TypeText Text:=Message

The email's body is accessed via Word Editor, an Outlook feature that provides more control over email content. The code navigates to the start of the email body, pastes the chart, goes back to the start again, and then types the message text, ensuring the content is placed above the default signature.

9. Email Ready for Review:

' .Send  ' Uncomment this if you want to send the email directly
End With

The email is left open for user review before sending. If you want the macro to send the email automatically, you can uncomment the `.Send` line.

10. Clean Up Objects:

Set OutlookMail = Nothing
Set OutlookApp = Nothing

Finally, we're clearing the Outlook objects to free up the memory resources they were using. This is good practice to help prevent memory leaks.

Sub SendEmailWithChart()
    Dim OutlookApp As Outlook.Application
    Dim OutlookMail As Outlook.MailItem
    Dim ChartName As String
    Dim ChartSheet As Worksheet
    Dim Message As String
    Dim ToAddress As String
    Dim CCAddress As String
    Dim Subject As String
    
    ' Set the name of the chart and the worksheet containing the chart
    
    Set ChartSheet = ThisWorkbook.Sheets("Sheet1") ' change Sheet1 to the name of your sheet
    
    ' Get the mail info from the worksheet
    ToAddress = ChartSheet.Range("B2").Value
    CCAddress = ChartSheet.Range("B3").Value
    Subject = ChartSheet.Range("B4").Value
    Message = ChartSheet.Range("B5").Value
    ChartName = ChartSheet.Range("B6").Value
    ' Create a new instance of the Outlook app
    Set OutlookApp = New Outlook.Application
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(olMailItem)
    
    With OutlookMail
        .To = ToAddress
        .CC = CCAddress
        .Subject = Subject
        ' The BodyFormat needs to be set to olFormatHTML to preserve formatting and signatures
        .BodyFormat = olFormatHTML
        .Display  ' This will display the email, crucial for accessing the Signature

        ' Copy the chart as a picture
        ChartSheet.ChartObjects(ChartName).Chart.CopyPicture _
            Appearance:=xlScreen, Format:=xlPicture

        ' Use the Word editor to paste the chart and message, maintaining the user's signature
        Dim WordEditor As Object
        Set WordEditor = .GetInspector.WordEditor

        ' Paste the chart at the beginning of the email body
        WordEditor.Application.Selection.HomeKey Unit:=6 ' or Unit:=wdStory if the reference is set
        WordEditor.Application.Selection.Paste
        
        ' Type the message text after the new lines
        WordEditor.Application.Selection.HomeKey Unit:=6 ' or Unit:=wdStory if the reference is set
        WordEditor.Application.Selection.TypeText Text:=Message
        
        ' Add three new lines after the chart
        WordEditor.Application.Selection.TypeText Text:=vbCrLf & vbCrLf & vbCrLf

        
        ' This line leaves the email displayed and ready for user review before sending
        ' .Send  ' Uncomment this if you want to send the email directly
    End With
    
    ' Clean up the Outlook application
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Your feedback and suggestion is very valuable for us.
So please comment your thoughts and request code below