Mail Chart as Image from Outlook – VBA Code

.

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 Picture As an Attachment

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") & "\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 Embedded to message of Email

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") & "\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

Adsense

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.

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

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
How to Declare a Public Variable in VBA

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

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 (=).

You May Also Like…

17 Comments

  1. Pradip Sengupta

    Superb…. Just what I want to find… Great Job..

    Reply
    • Vishwamitra Mishra

      Thanks Pradeep !!

      Reply
  2. Georgetta

    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
  3. Dorin

    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
  4. Sandeep

    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
  5. Sandeep

    Hey Its Working I changed gif to jpeg..

    Thanks A lot…

    Reply
  6. Sandeep

    Hi After i make changes to chart..

    it is not sending the updated one.. sending the previous one only.. Any help

    Reply
    • Vishwamitra Mishra

      Hi Sandeep,

      Please make sure that you refresh the chart before sending and it will take the latest one.

      Hope this helps.

      Reply
      • nithin

        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
  7. balaji

    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
  8. Vinay

    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
  9. china

    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

      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
  10. nithin

    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
  11. Inderjeet

    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
  12. Ramandeep

    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

      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

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest