![Automate Data Reporting: How to Instantly Create Outlook Emails with Excel Charts Using VBA](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEsYmed1HzTU9QArLpQLrx9S3DjlUlKpkaH2YsOxV5xeDM9axqSsvuKCO2u9aAi3T7k90bQSpV6McPNK52QnKU5vkcI-V3ae2f_usUbrimjtHei5i0bJEnrMhjzRVVWV4ZJiI21J_R6NOdSnobDgDh_QVF4fQ9gF5l-gW7yUtSVQPqy5DoxkA2Ra_SW0M/s16000/Thumbnail10.jpg)
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
2. Specify Chart and Worksheet:
ChartName = "Bar1"
Set ChartSheet = ThisWorkbook.Sheets("Sheet1") ' change Sheet1 to the name of your sheet
3. Retrieve Email Details:
ToAddress = ChartSheet.Range("B2").Value
CCAddress = ChartSheet.Range("B3").Value
Subject = ChartSheet.Range("B4").Value
Message = ChartSheet.Range("B5").Value
4. Create New Outlook Instance:
Set OutlookApp = New Outlook.Application
5. Create New Email Item:
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
6. Configure Email Details:
With OutlookMail
.To = ToAddress
.CC = CCAddress
.Subject = Subject
.BodyFormat = olFormatHTML
.Display
7. Copy Chart as Picture:
ChartSheet.ChartObjects(ChartName).Chart.CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
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
9. Email Ready for Review:
' .Send ' Uncomment this if you want to send the email directly
End With
10. Clean Up Objects:
Set OutlookMail = Nothing
Set OutlookApp = Nothing
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