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?

  • Takes the Range provided by you and copy it as a Picture
  • Creates a Temp worksheet and add a Chart and paste this image in to a Blank Chart
  • Now export this chart as an Image and save it to a temp folder
  • Create your mail in Outlook and Draft your Mail in HTML as shown in the above code and then send the email.
  • Now delete the temp sheet and temp image from temp folder and release all the objects created for outlook.

Download Sample

Here is your sample workbook to play with.

DOWNLOAD NOW