Log in  \/ 
x
or
x
x
Register  \/ 
x

or

How to paste a range of cells into message body as image in Excel?

If you need to copy a range of cells and paste it as an image into message body when you send an email from Excel. How could you deal with this task?

Paste a range of cells into email body as image with VBA code in Excel


Paste a range of cells into email body as image with VBA code in Excel


May be there is no other good method for you to solve this job, a VBA code in this article can help you. Please do as this:

1. Enable the sheet you want to copy and paste the cells as image, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: paste a range of cells into email body as image:

Sub sendMail()
    Dim TempFilePath As String
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xHTMLBody As String
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox("Please select the data range:", "KuTools for Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set xOutApp = CreateObject("outlook.application")
    Set xOutMail = xOutApp.CreateItem(olMailItem)
    Call createJpg(ActiveSheet.Name, xRg.Address, "DashboardFile")
    TempFilePath = Environ$("temp") & "\"
    xHTMLBody = "<span LANG=EN>" _
            & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
            & "Hello, this is the data range that you want:<br> " _
            & "<br>" _
            & "<img src='cid:DashboardFile.jpg'>" _
            & "<br>Best Regards!</font></span>"
    With xOutMail
        .Subject = ""
        .HTMLBody = xHTMLBody
      .Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue
        .To = " "
        .Cc = " "
        .Display
    End With
End Sub
Sub createJpg(SheetName As String, xRgAddrss As String, nameFile As String)
    Dim xRgPic As Range
    Dim xShape As Shape
    ThisWorkbook.Activate
    Worksheets(SheetName).Activate
    Set xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss)
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        For Each xShape In ActiveSheet.Shapes
            xShape.Line.Visible = msoFalse
        Next
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
   Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
Set xRgPic = Nothing
End Sub

Note: In the above code, you can change the body content and email address to your need.

3. After inserting the code, press F5 key to run this code, a dialog box is popped out to remind you selecting the data range that you want to insert into the email body as picture, see screenshot:

4. Then click OK button, and a Message window is displayed, the selected data range has been inserted into the body as image, see screenshot:

Note: In the Message window, you can also change the body content and Email addresses in To and Cc fields as you need.

5. At last, click Send button to send this email.


Note: If you need to paste multiple ranges from different worksheets, the below VBA code can do you a favor:

First, you should select the multiple ranges that you want to insert into the email body as pictures, and then apply the following code:

VBA code: paste multiple ranges of cells into email body as image:

Sub sendMail()
    Dim TempFilePath As String
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xHTMLBody As String
    Dim xRg As Range
    Dim xSheet As Worksheet
    Dim xAcSheet As Worksheet
    Dim xFileName As String
    Dim xSrc As String
    On Error Resume Next
    TempFilePath = Environ$("temp") & "\RangePic\"
    If Len(VBA.Dir(TempFilePath, vbDirectory)) = False Then
      VBA.MkDir TempFilePath
    End If
    Set xAcSheet = Application.ActiveSheet
    For Each xSheet In Application.Worksheets
        xSheet.Activate
        Set xRg = xSheet.Application.Selection
        If xRg.Cells.Count > 1 Then
            Call createJpg(xSheet.Name, xRg.Address, "DashboardFile" & VBA.Trim(VBA.Str(xSheet.Index)))
        End If
    Next
    xAcSheet.Activate
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set xOutApp = CreateObject("outlook.application")
    Set xOutMail = xOutApp.CreateItem(olMailItem)
    xSrc = ""
    xFileName = Dir(TempFilePath & "*.*")
    Do While xFileName <> ""
        xSrc = xSrc + VBA.vbCrLf + "<img src='cid:" + xFileName + "'><br>"
        xFileName = Dir
        If xFileName = "" Then Exit Do
    Loop
    xHTMLBody = "<span LANG=EN>" _
                & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
                & "Hello, this is the data range that you want:<br> " _
                & "<br>" _
                & xSrc _
                & "<br>Best Regards!</font></span>"
    With xOutMail
        .Subject = ""
        .HTMLBody = xHTMLBody
        xFileName = Dir(TempFilePath & "*.*")
        Do While xFileName <> ""
            .Attachments.Add TempFilePath & xFileName, olByValue
            xFileName = Dir
        If xFileName = "" Then Exit Do
        Loop
        .To = " "
        .Cc = " "
       .Display
    End With
    If VBA.Dir(TempFilePath & "*.*") <> "" Then
        VBA.Kill TempFilePath & "*.*"
    End If
End Sub
Sub createJpg(SheetName As String, xRgAddrss As String, nameFile As String)
    Dim xRgPic As Range
    ThisWorkbook.Activate
    Worksheets(SheetName).Activate
    Set xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss)
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\RangePic\" & nameFile & ".jpg", "JPG"
    End With
    Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
Set xRgPic = Nothing
End Sub

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    DRitan · 27 days ago
    Hello...I have the problem that the image in @gmail is displayd as attachment and not in the body of the email... because when I send the email pasting my excel range manually to outlook from the source I can see that the image.png has even the: " src="cid:idashboard.png@01D622DC.8B4FCA60 and not just " src="https://download.extendoffice.com/cid:dashboard.jpgg@. I am afraind that must be icluded even the and trying to add the code like: PropertyAccessor.SetProperty etc ect ..can You help me pease ?
    thank Dritan
  • To post as a guest, your comment is unpublished.
    Jake · 3 months ago
    This is great. However, some of the text from a cell is being cut out of the image when pasted into the email. And some of the spacing between letters is off.


    For example: Cell says "Something is happening with the words."


    In the email it shows as: "e th ing ishapp ening with the wo"
  • To post as a guest, your comment is unpublished.
    msb.mp6@gmail.com · 5 months ago
    Hi ,
    Firstly ,Thank you

    Your code really workzz..


    I have another requirement , could you please help me in appending code to the existing and resolve my issue ?

    Here is my query :
    Can the below image table sent with hyperlink enabled(i.e., clickable) ?


    Expecting a fix from you . Kindly revert if my question if not clear. happy to make it clear.


    Not sure if the image that i have uploaded is visible at your end . As its not visible for me after i posted it .

    Please let me know your email id , so that i can send my reference query image to you.


    Thanks in advance
  • To post as a guest, your comment is unpublished.
    Daniel H · 5 months ago
    Thanks a lot for your code! Is it possible to add text between the images posted on the mail?
  • To post as a guest, your comment is unpublished.
    cabral1500 · 5 months ago
    Como fazer para inserir minha assinatura Outlook usando esse código?

    How do I insert my Outlook signature using this code?
    • To post as a guest, your comment is unpublished.
      smokanap · 4 months ago
      First save your signature in signature tab ,

      then insert this following code in the code


      .htmlbody = xHTMLBody & .htmlbody
  • To post as a guest, your comment is unpublished.
    windyying · 7 months ago
    The code above works well on PC, while the picture can't be seen from mobile APP. It only showed "cid:DashboardFile.jpg". Is there any way to solve the issue?
  • To post as a guest, your comment is unpublished.
    Carlos · 8 months ago
    When I try to send a second email with the same rage but diffent info (is a pivot) is showing the 1st image on the second email. How do I delete the image after created or pasted on email?
  • To post as a guest, your comment is unpublished.
    Breaking bad · 9 months ago
    Join the club, even I am facing the same issue ,with borders around the image .
    .
    .
    Waiting for a fix.

    Fingers crossed !!!!
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, Breaking,
      The code in this article has been updated, please try, hope it can help you!
      Thank you!
  • To post as a guest, your comment is unpublished.
    srilatha.aithal@gmail.com · 9 months ago
    Hi
    This works great. But it has a border around the image. Is there a way to take this off.


    Thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 9 months ago
      Hello, srilatha,
      The code in this article has been updated, please try, hope it can help you!
      Thank you!
      • To post as a guest, your comment is unpublished.
        srilatha.aithal@gmail.com · 9 months ago
        This is awesome. Thanks a heap


        I got one last problem, my image appears a little blur and that happens only in one column .Any way to fix that.

        Thank you!!
  • To post as a guest, your comment is unpublished.
    Saisri · 9 months ago
    Hi,
    This works great, but has a border. Is there a way to remove the border
  • To post as a guest, your comment is unpublished.
    NoMadMax59 · 9 months ago
    Buongiorno,
    l'esecuzione del codice si ferma a xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss) e torna alla sub sendMail senza creare l'immagine.

    Utilizzo Office 2010 e win7

    Come posso correggere?
  • To post as a guest, your comment is unpublished.
    jackielqj · 10 months ago
    Hi Skyyang, can you let me have the code for sending two ranges from two sheets of the same work sheet, each range in a different image?
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hello, Jackie,
      I have updated this article, you can use the code at the end of this article.
      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        jackielqj · 10 months ago
        Sorry, never mind. It's my mistake on my part. One more question - how can I add a space in between the images?
        • To post as a guest, your comment is unpublished.
          skyyang · 10 months ago
          Hi, Jackie,
          To insert a blank row between the images, you just need to press Enter key at the end of the image in the email body.
      • To post as a guest, your comment is unpublished.
        jackielqj · 10 months ago
        Hi skyyang, thank you very much. It works, for the most part. However, I have different ranges ("F1:N15") from sheet 1, and "H1:N15" from sheet 2. It seems always use the "F1:N15" range from both sheets. How can I make it change 2 different ranges?
  • To post as a guest, your comment is unpublished.
    jotamasters · 1 years ago
    This is amazing. One question: How can I send two ranges that are in two differents sheets of the same workbook, each range in a different image?
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hi, Jose,
      The code is somewhat difficult, and it can not insert here, if you want to this code, you can give your email here, and i will send the code to your email.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Jose Gonçalves · 1 years ago
    This is awesome!! Can you tell me how I can insert more than one image using this code? I need insert two interval of the same workbook, but they are in diferents sheets.
  • To post as a guest, your comment is unpublished.
    dadd · 1 years ago
    buongiorno… potreste dirmi cosa devo inserire al posto di quelle stringhe del codice in blu?
    • To post as a guest, your comment is unpublished.
      jotamasters · 1 years ago
      Queste linee blu indicano "text ", è probabilmente una formattazione dell'editor utilizzato per creare il códice.
  • To post as a guest, your comment is unpublished.
    Mohammad · 1 years ago
    Thanks dears,, Could you please tell me how to do this but without asking for range (predefined range)?
    • To post as a guest, your comment is unpublished.
      Carter · 1 years ago
      Did anyone ever reply to you?
      • To post as a guest, your comment is unpublished.
        Jay · 1 years ago
        Set xRg = Range("A1:J10")

        Just set xRg to whatever range you want/need it to be.
  • To post as a guest, your comment is unpublished.
    taxicabconfessor@gmail.com · 1 years ago
    This is awesome, I love it! Quick question. I see that it is adding a border to the image. Is there a way to generate without a border? Thanks in advance!
    • To post as a guest, your comment is unpublished.
      Ian Wildman · 1 years ago
      I'd love to know how to paste without generating a border as well. This code is awesome, super intuitive and straightforward. Thank you!
  • To post as a guest, your comment is unpublished.
    Piotrek · 1 years ago
    wyrzuca mi błąd w linijce "Set xOutMail = xOutApp.CreateItem(olMailItem)" olMailItem - nie zdefiniowana
    oraz ".Attachments.Add TempFilePath & "DashboardFile.jpg", olByValue" olByValue - nie zdefiniowana