Send Image of a Range from Excel embedded in Mail [Inline Image in Mail]

.

Dear Friends,

Couple of my friends asked me about this like how can I send screenshot of a particular range area of my excel sheet embedded in my HTML mail with other texts in my email. I also got this question on Microsoft Excel forum where I answered that question by providing a piece of code. Though there were some other ideas and codes which was shared by Deepak Panchal (MSFT CSG).

send range as inline image

send range as inline image

Code which will do the magic


Sub SendHTML_And_RangeImage_As_Body_UsingOutlook()
    Dim olApp As Object
    Dim NewMail As Object
    Dim ChartName As String
    Dim imgPath As String
    
    On Error GoTo err
    
    Set olApp = CreateObject("Outlook.Application")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'define a temp path for your image
    tmpImageName = VBA.Environ$("temp") & "\tempo.jpg"
    
    'Range to save as an image
    Set RangeToSend = Worksheets("Sheet1").Range("A3:M27")
    ' Now copy that range as a picture
    RangeToSend.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    ' To save this as an Image we need to do a workaround
    ' First add a temporary sheet and add a Chart there
    ' Resize the chart same as the size of the range
    ' Make the Chart border as Zero
    ' Later once we export that chart as an image
    ' and save it in the above temporary path
    ' will delete this temp sheet
    
    Set sht = Sheets.Add
    sht.Shapes.AddChart
    sht.Shapes.Item(1).Select
    Set objChart = ActiveChart

    With objChart
        .ChartArea.Height = RangeToSend.Height
        .ChartArea.Width = RangeToSend.Width
        .ChartArea.Fill.Visible = msoFalse
        .ChartArea.Border.LineStyle = xlLineStyleNone
        .Paste
        .Export Filename:=tmpImageName, FilterName:="JPG"
    End With
    
    'Now delete that temporary sheet
    sht.Delete
    
   ' Create a new mail message item.
    Set NewMail = olApp.CreateItem(0)
    
    With NewMail
        .Subject = "Your Subject here" ' Replace this with your Subject
        .To = "abc@email.com" ' Replace it with your actual email
        
'       **************************************************
'       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 = "<body>Dear Sir/Madam, 

Kindly find the report below:" & _
        "
<img src=" & "'" & tmpImageName & "'/>
Regards,
LearnExcelMacro.com </body>"
        .Send
        
    End With

err:

    'Release memory.
    ' Kill tmpImageName
    Set olApp = Nothing
    Set NewMail = Nothing
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

An Explanation : What this code does?

[checklist icon=”fa-check” iconcolor=”” circle=”” circlecolor=”” size=”13px” class=”” id=””][li_item icon=”fa-check”]Takes the Range provided by you and copy it as a Picture[/li_item][li_item icon=”fa-check”]Creates a Temp worksheet and add a Chart and paste this image in to a Blank Chart[/li_item][li_item icon=”fa-check”]Now export this chart as an Image and save it to a temp folder[/li_item][li_item icon=”fa-check”]Create your mail in Outlook and Draft your Mail in HTML as shown in the above code and then send the email.[/li_item][li_item icon=”fa-check”]Now delete the temp sheet and temp image from temp folder and release all the objects created for outlook.[/li_item][/checklist]

[content_boxes layout=”icon-on-side” columns=”1″ icon_align=”left” title_size=”” backgroundcolor=”#fff6d1″ icon_circle=”” icon_circle_radius=”” iconcolor=”” circlecolor=”” circlebordercolor=”” circlebordercolorsize=”” outercirclebordercolor=”” outercirclebordercolorsize=”” icon_size=”20″ 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=”Important” 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″]Make sure that your image is not deleted before the email is actually sent. .Send command creates the mail and trigger a send.. that is it. It does not wait for actually email to be sent from the outbox. In such case if your image is deleted before your email is actually sent, then You may see that your image is not part of the email because it was deleted before email was sent actually. This is the reason I have commented the delete statement (‘ Kill tmpImageName) of the temp image.[/content_box][/content_boxes]

Download Sample

Here is your sample workbook to play with.

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…

12 Comments

  1. yousef Qaroos

    is there any way to send the same range as a normal table in the outlook message?

    Reply
    • Vishwamitra Mishra

      Yes, you can do so.. here is a very famous function written by Ron De Bruin (MVP) to achieve this: https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

      [code language=”vb”]
      Sub Mail_Selection_Range_Outlook_Body()
      ‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
      ‘Don’t forget to copy the function RangetoHTML in the module.
      ‘Working in Excel 2000-2016
      Dim rng As Range
      Dim OutApp As Object
      Dim OutMail As Object

      Set rng = Nothing
      On Error Resume Next
      ‘Only the visible cells in the selection
      Set rng = Selection.SpecialCells(xlCellTypeVisible)
      ‘You can also use a fixed range if you want
      ‘Set rng = Sheets("YourSheet").Range("D4:D12").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

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

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

      On Error Resume Next
      With OutMail
      .To = "ron@debruin.nl"
      .CC = ""
      .BCC = ""
      .Subject = "This is the Subject line"
      .HTMLBody = RangetoHTML(rng)
      .Send ‘or use .Display
      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)
      ‘ Changed by Ron de Bruin 28-Oct-2006
      ‘ Working in Office 2000-2016
      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
      [/code]

      Reply
      • Yousef Qaroos

        thank you, it works perfect, but how should I assign a cell for “to”, “cc”, “subject” and “body”.

        and is there any way to assign a cell for the new .xls file name and another one for the new .pdf file name?

        thank you for your usual cooperation

        Reply
  2. serdar

    Does this code work for CDO?

    Reply
  3. Sachin

    The temp file is not deleted
    Excel sends the same file over and over

    Reply
  4. J

    Hi there,

    Thank you for the code and I tried this and most part of the code works fine. it also ends the email but when the recepients receives the email. they are not seeing the image. I can however see the image in my sent box. I didnt make any modification to the codes and have used it just like you mentioned here. I can see the image still there in my Temp folder. but all the recepient sees the image as a box with red X. please advise in how to correct this issue.

    Thanks
    J

    Reply
  5. Mick

    Hi,
    I have the same issue, the destination recipients, cannot see the image. I can see it only if I send to myself.
    Can you please help me to fix it?

    Reply
  6. Zy

    Have you figure this out yet?

    Reply
  7. Ana

    Hi,

    The code works but how do I add another screenshot of another range in the code and mail body?

    Reply
  8. web host

    Genuinely when someone doesn’t know after that its up to other users that they will help, so here it takes place.

    Reply
  9. mm

    working in my case

    really thank you for your post

    Reply
  10. angela

    Hi Thank you for the code! really does work! I added to a few of the sheets I work on– however, recently when I tried to copy and paste the code to other /new sheets — the active X control button just loads and does nothing– no email appears. Is that error of the code or excel?

    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