Mail Chart as Image from Outlook – VBA Code



Email

In this article you will learn how to send a chart as picture using outlook from Excel Macro. As you know that in Outlook email, you can send an Image like a normal file attachment. In this case, receiver has to open the attachment to see the content. Since outlook supports HTML formatting of the email body, it is possible to paste the image in the body of the email itself. In this case, your image will be part of the body of the email. To view this receiver does not have to open any file.

Based on these two options available in Outlook, it is possible to send the Chart Image via Outlook using following two methods:

Sending Chart as an attachment to the email

Sending chart – embedded to the Outlook email body.

We will discuss about both the methods in detail here. Before we jump over to the VBA codes for those methods, let me explain you the trick which involved in achieving this task of sending your graph as a picture in your outlook email.

Steps to send Chart as Image in Outlook email

Let see some common steps which we are going to follow to send Excel graph as an Image in email.

Step 1. Save Chart as Image in your System

For sending the chart as an Image, it is important that we first save the Excel Chart as an Image somewhere in your local computer. Here in this example, I am storing the Image in temporary folder of your windows. You can read this article to know, how to find the special directories path of Windows using Excel VBA. Special directories like Desktop, My Documents, Temp Folders etc..

Step 2. Send the Image via email

Now send the Chart Image by either way from outlook. Read more about how to send emails from Outlook

Step 3. Now Delete the Image which was saved

At last delete the Chart Image from the temp folder where it was saved it. This logic, I have applied for both the methods mentioned below

Click here to read more about Sending emails using Excel VBA >>

Method 1: Sending Chart Image as an attachment in the mail

As explained earlier, in this methods, Chart image will be attached like any file attached to the email. You can refer the below image.

Chart Image As an Attachment

VBA code to send Chart as a Picture in Your Outlook Email


Sub SendChart_As_Attachment_UsingOutlook()

    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    
    Set olApp = CreateObject("Outlook.Application")


    'fill in the file path/name of the gif file
    ChartName = Environ$("temp") &amp; "\Chart1.gif"
           
    ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Export _
    Filename:=ChartName, FilterName:="GIF"
    '**************************************************************
    'In the above line
    '   Sheet1 : Sheet Name where Chart is placed
    '   Chart 1: Chart Name which you want to sent in email.
    
    '   Note: To get the name of the Chart, select that particular
    '         chart and see in the left side of the formula bar.
    '***************************************************************
   
   ' Create a new mail message item.
    Set NewMail = olApp.CreateItem(0)
    With NewMail
        .Subject = "Please the attached Chart"
        .To = "abc@email.com"
        .Body = "Report is displayed below and chart is attached"
        .Attachments.Add ChartName
        .Send
    End With
    
    'Now delete the chart image from the temp folder
    'Kill ChartName

    'Release memory.
    Set olApp = Nothing
    Set NewMail = Nothing

End Sub

Method 2: Send the chart picture embedded in the Outlook email Body

As explained in the beginning of the article, in this method, Image is going to be part of the Email body. Email body format should be in HTML. Refer the below image to see how your image will look in your outlook email body.

Chart Displayed as body of the Email

VBA Code to send Chart image embedded in the Outlook email Body


Sub SendChart_As_Body_UsingOutlook()

    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    
    Set olApp = CreateObject("Outlook.Application")


    'fill in the file path/name of the gif file
    ChartName = Environ$("temp") &amp; "\Chart1.gif"
           
    ActiveWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart.Export _
    Filename:=ChartName, FilterName:="GIF"
    '**************************************************************
    'In the above line
    '   Sheet1 : Sheet Name where Chart is placed
    '   Chart 1: Chart Name which you want to sent in email.
    
    '   Note: To get the name of the Chart, select that particular
    '         chart and see in the left side of the formula bar.
    '***************************************************************
   
   ' Create a new mail message item.
    Set NewMail = olApp.CreateItem(0)
    With NewMail
        .Subject = "Please the attached Chart"
        .To = "abc@email.com"
        
'       **************************************************
'       You can desing your HTML body for this email.
'       below HTML code will display the image in
'       Body of the email. It will not go in attachment.
'       **************************************************
        .HTMLBody =   "<img src=" & "'" & ChartName & "'>"
        .Send
    End With
    
    'Now delete the chart image from the temp folder
    'Kill ChartName

    'Release memory.
    Set olApp = Nothing
    Set NewMail = Nothing

End Sub

[content_boxes layout=”icon-on-side” columns=”1″ icon_align=”left” title_size=”” backgroundcolor=”#FFFFE0″ icon_circle=”” icon_circle_radius=”” iconcolor=”” circlecolor=”” circlebordercolor=”” circlebordercolorsize=”” outercirclebordercolor=”” outercirclebordercolorsize=”” icon_size=”” link_type=”” link_area=”” animation_delay=”” animation_offset=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ margin_top=”” margin_bottom=”” class=”” id=””][content_box title=”” icon=”fa-bullhorn” backgroundcolor=”” iconcolor=”” circlecolor=”” circlebordercolor=”” circlebordercolorsize=”” outercirclebordercolor=”” outercirclebordercolorsize=”” iconrotate=”” iconspin=”no” image=”” image_width=”35″ image_height=”35″ link=”” linktarget=”_self” linktext=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″]

Important

As I mentioned above, after sending the email, delete the Image which was saved from the Chart. There is a tricky situation here.
In Outlook, when VBA statement


.send

is executed then VBA just pushes the email to Outbox and VBA control moves to the next line. It DOES NOT wait for email to be actually sent.
Therefore, at the time when actually email is sent from Outlook, the local image was already deleted by the VBA statement


kill ChartFile

As a result, you will see that Image is not sent rather a “image not found X icon is sent in the outlook email.

Solution

Do not delete the Image which was saved. Comment the kill ChartFile in the VBA code.[/content_box][/content_boxes]

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Pradip Sengupta on November 28, 2012 at 7:23 pm

Superb…. Just what I want to find… Great Job..
Reply
- Vishwamitra Mishra on April 15, 2014 at 11:59 am
  
  Thanks Pradeep !!
  Reply
Georgetta on April 18, 2013 at 7:07 pm

I believe everything composed was very logical. But, think about this, what if you added a little content?

I am not suggesting your information isn't good, however suppose you added a title that grabbed folk's attention?

I mean Learn Excel Macro

Mail Chart as Image from Outlook – VBA Code is a little vanilla.

You might glance at Yahoo's home page and watch how they write news headlines to get viewers to open the links. You might try adding a video or a pic or two to get people interested about what you've written.

Just my opinion, it would bring your blog a little bit more interesting.
Reply
Dorin on May 2, 2013 at 8:58 am

Hi,

This is my first attempt at vba code (i'm not a programmer), so i'm sorry if this question might sound too easy.

I'm currently trying to send out a few charts to outlook that will be visible on mobile. If i save any of this images on my computer or network it won't appear in the mail.

Does anyone know how can i do that?

Thanks!
Reply
Sandeep on January 21, 2014 at 4:57 pm

Hi

Thank you very much your exmaples are helping me a lot.

When i am trying to use above code by changing the chart and sheet name its not working and adding chart to body of email.

Please Help…
Reply
Sandeep on January 21, 2014 at 5:07 pm

Hey Its Working I changed gif to jpeg..

Thanks A lot…
Reply
Sandeep on January 21, 2014 at 5:45 pm

Hi After i make changes to chart..

it is not sending the updated one.. sending the previous one only.. Any help
Reply
- Vishwamitra Mishra on April 15, 2014 at 12:02 pm
  
  Hi Sandeep,
  
  Please make sure that you refresh the chart before sending and it will take the latest one.
  
  Hope this helps.
  Reply
  - nithin on August 23, 2015 at 7:57 pm
    
    hi,
    i am sending mails from cdo as i need to send the mails from another desk top. i have a problem. i need to send 44 charts from 44 sheets. i am exporting the every sheet chart individually to the same file. i kept my name in bcc as i cannot use the .display option in cdo. in my mails all the mails showing the last sheet chart only. all the remaining charts showing the 44th sheet chart instead of showing individual chart with respect to the sheet
    Reply
balaji on June 9, 2014 at 11:54 am

i need a graph based dashboard to check the unread mail vs read mail and response for the incoming message graphs and etc., is that possible?? is there any software to achieve this task?
Reply
Vinay on June 25, 2014 at 10:39 am

Hi,
Am not a developer and I dont know much about Macros.
When I tried to send a mail with graph attached in a body, I got error stating ” No return or halt function found”.
It will be of great help if am able to use this macro to send mail in my work.So, kindly help me.

Thanks in advance.
Reply
china on January 27, 2015 at 10:27 pm

Hi,

i have this VBA codes and i would like to insert 2 pictures on the email.can you help me with this?

Sub Mail_Selection_Outlook_Body()
‘ Don’t forget to copy the function RangetoHTML in the module.
‘ Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

‘Selects the content of the email to be sent
Range(“A4:O63”).Select ‘change this with the body of the email’s range

Set rng = Nothing
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox “The selection is not a range or the sheet is protected” & _
vbNewLine & “please correct and try again.”, vbOKOnly
Exit Sub
End If

Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Cells(3, 2).Value
.CC = Cells(2, 2).Value
.SentOnBehalfOfName = “phmnl4opomdTeamWaldo@sykes.com”
.Subject = Cells(1, 2).Value
.HTMLBody = RangetoHTML(rng)
.Display ‘or use .Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$(“temp”) & “/” & Format(Now, “dd-mm-yy h-mm-ss”) & “.htm”

‘Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.delete
On Error GoTo 0
End With

‘Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

‘Read all data from the htm file into RangetoHTML
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, “align=center x:publishsource=”, _
“align=left x:publishsource=”)

‘Close TempWB
TempWB.Close savechanges:=False

‘Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
Reply
- Sathiyamoorthy on May 22, 2015 at 11:39 am
  
  Dear Vishwamitra Mishra,
  
  I wish to send sales MIS to many peoples in a single shot in outlook mail body(without attachement) from the excel. how do i make it?
  Kindly help me out.
  
  Thank you,
  Reply
nithin on August 23, 2015 at 7:58 pm

hi,
i am sending mails from cdo as i need to send the mails from another desk top. i have a problem. i need to send 44 charts from 44 sheets. i am exporting the every sheet chart individually to the same file. i kept my name in bcc as i cannot use the .display option in cdo. in my mails all the mails showing the last sheet chart only. all the remaining charts showing the 44th sheet chart instead of showing individual chart with respect to the sheet
Reply
Inderjeet on July 14, 2016 at 12:34 pm

I am trying to send a chart 1 along with few range range(“c3:e5”) on the top of my chart using vba. I cnat do that. please help
Reply
Ramandeep on July 26, 2017 at 12:47 pm

Hi,

Thanks for the code, but this is not visible to the person i.e. if I embed image in email body, it will visible till
file is not deleted to me and this image is not visible if sent to somebody else.

Any solution for this?

Thanks,
Ramandeep Singh
Reply
- Vishwamitra Mishra on July 26, 2017 at 5:21 pm
  
  Dear Ramandeep,
  
  In VBA code, “.Send ” statement just push the email to outbox in your Outlook and that is it. Immediately after that, control moves to next statement which “kill tempfile”
  This happens so fast most of the time that, before your email is actually sent, temp file is deleted from your local system.
  
  Outlook embed the picture when it is actually sent. If at the time of sent, file does not exist then, ofcourse that image will not be embedded in the email.
  
  Solution
  
  simply comment the statement the “kill ChartName” statement and try, it should work.
  
  The only problem in this method is that, your temp file will remain there always. Next time when you are again trying to send an email, then old one will be replaced by the new one. but one copy will always remain there.
  
  I hope, this solves your problem. Let me know, if it worked.
  Reply