Pasting an Excel range into an email as a picture

by

Here’s a worked example, tested in Office 2010:

enter image description here

'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy

'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)

'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'To paste as picture
wordDoc.Range.PasteAndFormat wdChartPicture

'To paste as a table
'wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

Result:

enter image description here

In the code above I used early binding to have access to autocomplete; to use this code you need to set references to the Microsoft Outlook and Microsoft Word object libraries: Tools > References... > set checkmarks like this:

enter image description here

Alternatively, you can forget about the references and use late binding, declaring all the Outlook and Word objects As Object instead of As Outlook.Application and As Word.Document etc.

Apparently you’re having trouble implementing the above; the range pastes as a table rather than a picture in your email message. I have no explanation for why that would happen.

An alternative is then to paste as an image in Excel, and then cut and paste that image into your e-mail:

'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy

'Paste as picture in sheet and cut immediately
Dim p As Picture
Set p = ActiveSheet.Pictures.Paste
p.Cut

'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)

'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'Paste picture
wordDoc.Range.Paste

As pointed out by WizzleWuzzle, there is also the option of using PasteSpecial instead of PasteAndFormat or Paste…

wordDoc.Range.PasteSpecial , , , , wdPasteBitmap

… but for some reason, the resulting image doesn’t render as well. See how the lower table is kind of blurry:

enter image description here

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. How to avoid using Select in Excel VBA
  3. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  4. Convert Text to Date?
  5. Run Excel Macro from Outside Excel Using VBScript From Command Line
  6. Importing text file into excel sheet
  7. Loop through each cell in a range of cells when given a Range object
  8. Checking for #N/A in Excel cell from VBA code
  9. Non-web SQL Injection
  10. Error: “Could Not Find Installable ISAM”
  11. Filling any empty cells with the value above
  12. How to copy sheets to another workbook using vba?
  13. Check if value exists in column in VBA
  14. Excel vba refresh wait
  15. hiding formulas in formula bar
  16. How to delete multiple rows without a loop in Excel VBA
  17. Excel vba to create every possible combination of a Range
  18. Run a Macro from Another Workbook
  19. Sorting a dictionary by key in VBA
  20. Handle “No cells were found” error when filtered range is empty
  21. VBA paste as values – how to
  22. What are the benefits of using Classes in VBA? [closed]
  23. Writing a VLOOKUP function in vba
  24. Generate Word Documents (in Excel VBA) from a series of Document Templates
  25. Handle cancellation of InputBox to select range
  26. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  27. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  28. VBA: How to combine two ranges on different sheets into one, to loop through
  29. Access files with long paths (over 260)
  30. Concatenate multiple ranges using vba

Leave a Comment