Tip: Other languages are Google-Translated. You can visit the English version of this link.
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
    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") & "\" & 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.


Recommended Productivity Tools for Excel

kte tab 201905

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Jose Goncales · 1 months 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.
    Jose Gonçalves · 1 months 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 months 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.
      Jose Goncales · 1 months 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 · 3 months 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 · 2 months ago
      Did anyone ever reply to you?
      • To post as a guest, your comment is unpublished.
        Jay · 1 months 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.
    greg horton · 3 months 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 · 3 months 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!