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?
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
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoThis 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.· 1 months agoThis 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.
To post as a guest, your comment is unpublished.· 3 months agoThanks 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.· 3 months agoThis 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!