1

I am trying to paste an Excel range into the body of an Outlook e-mail as a picture using VBA. However, whenever I do so, the range gets cut off in the paste operation. The original range looks as follows:

Original Range

While the paste appears as follows:

Pasted Image

I basically used Jean-François Corbett's VBA code shown at Pasting an Excel range into an email as a picture. Namely:

Dim objOutlook As Object
Dim objMail As Object

Set objOutlook = CreateObject(Class:="Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

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

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

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

'Paste picture
wordDoc.Range.Paste

I know there are other code samples out there that do the paste as HTML (e.g., by Ron de Bruin), but that ends up pasting as a table for me (as opposed to as a picture). I've also seen examples where people paste into a chart, save the chart as a JPG, and insert that JPG into the HTMLBody of the e-mail (e.g., by Mike Marshall), but that too gets cut off for me.

Appreciate any insights people can share!

skyfx
  • 29
  • 7
  • can you just expand the range to one row below (and / or above) the data. you'll get extra white space but may get your data table – Scott Holtzman Oct 11 '17 at 13:49
  • Solid idea! When I increased the range to include an additional column on the right and three additional rows at the bottom, it pasted properly (i.e., Set r = Range("B2:F17")). This is still so weird to me though, since that extra column and those extra rows are all blank. Any idea why this is happening? – skyfx Oct 11 '17 at 13:56

0 Answers0