Copy Excel range as Picture to Outlook

Question

How can I use the command "Paste Special - As Picture" that you access in Excel from the right-click menu?

I have viewed various posts, but they seem to be outdated when using Excel 2016. It seems it has to be in this section,

With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select

How do I alter to allow copy and paste as picture?

When using the original code below, I lose all column and row sizing in the email body.

Dim rng As Range
Dim OutApp As Object
Dim outMail As Object

Set rng = Nothing
' Only send the visible cells in the selection.

Set rng = Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set outMail = OutApp.CreateItem(0)

With outMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .HTMLBody = RangetoHTML(rng)
    .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set outMail = Nothing
Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' By Ron de Bruin.
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

score 6 · Accepted Answer · answered Feb 21 '18 at 02:48

6

To get better picture on Outlook, work with Word object model with MailItem.GetInspector Property (Outlook)

Example

Option Explicit
Public Sub Example()
    Dim rng As Range
    Dim olApp As Object
    Dim Email As Object
    Dim Sht As Excel.Worksheet
    Dim wdDoc As Word.Document

    Set Sht = ActiveWorkbook.Sheets("Dashboard")
    Set rng = Sht.Range("B4:L17")
        rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set olApp = CreateObject("Outlook.Application")
    Set Email = olApp.CreateItem(0)
    Set wdDoc = Email.GetInspector.WordEditor

    With Email
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Attachments.Add ActiveWorkbook.FullName

         wdDoc.Range.PasteAndFormat Type:=wdChartPicture

'        if need setup inlineshapes hight & width
         With wdDoc
            .InlineShapes(1).Height = 130
         End With

        .Display
    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set Email = Nothing
    Set olApp = Nothing
End Sub

answered Feb 21 '18 at 02:48

0m3r

12,286
15
35
71

I get an error: "Compile Error: User-defined type not defined." on this part of the code: 'Dim wdDoc As Word.Document' Do I need to add any particular reference in VBA to allow this code to work? – linktheory Feb 21 '18 at 16:20
2

@linktheory Yes, you will have to add reference to `Microsoft Word xx.x Object Library`. – L42 Feb 21 '18 at 21:32
@0m3r Is there a way to keep the image and still allow content (text, etc.) to be present in the body of the e-mail? – linktheory Feb 21 '18 at 21:53
1

@linktheory Yes, you will have to set the destination on the [`Range Method`](https://msdn.microsoft.com/en-us/vba/word-vba/articles/range-object-word) – L42 Feb 21 '18 at 22:36
@L42 are you saying range.body(“body of text”)? This is still new to me so I am not entirely sure where this would be placed. – linktheory Feb 21 '18 at 22:44
1

@linktheory Try `wdDoc.Range.InsertAfter "Hello 0m3r"` after `wdDoc.Range.PasteAndFormat Type:=wdChartPicture` look at this answer https://stackoverflow.com/a/42662697/4539709 the benefit of adding reference to Outlook Library is you will have access to all Word Objects - you may wanna post new question and we will help fix it- thanks – 0m3r Feb 21 '18 at 23:19
@linktheory see here another example using Paragraphs https://stackoverflow.com/a/49104427/4539709 – 0m3r Mar 05 '18 at 06:09
1

@Om3r - if you ever get bored, here is another [email question](https://stackoverflow.com/q/65396620/6706419) that I think would be of interest. You seem to provide best solutions relating to Outlook so figured id share with you. I was able to provide a workaround solution but curious if you know the actual problem – urdearboy Dec 21 '20 at 17:35

L42 · Answer 2 · 2018-02-21T03:03:09.673

Something like this should work:

Dim ol As Object 'Outlook.Application
Dim olEmail As Object 'Outlook.MailItem
Dim olInsp As Object 'Outlook.Inspector
Dim wd As Object 'Word.Document

Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy

Set ol = GetObject(, "Outlook.Application") '/* if outlook is running, create otherwise */
Set olEmail = ol.CreateItem(0) 'olMailItem

With olEmail
    Set olInsp = .GetInspector
    If olInsp.EditorType = 4 Then 'olEditorWord
        Set wd = olInsp.WordEditor
        wd.Range.PasteAndFormat 13 'wdChartPicture
    End If
    .Display
End With

If sure that your version of Outlook uses Word Editor, you can do it like:

With olEmail
    .GetInspector.WordEditor.Range.PasteAndFormat 13
    .Display
End With

AskDNA · Answer 3 · 2019-03-28T00:21:24.387

If you want to add Text, use this code.

Dim ol As Object 'Outlook.Application
Dim olEmail As Object 'Outlook.MailItem
Dim olInsp As Object 'Outlook.Inspector
Dim wd As Object 'Word.Document

Sheets("Dashboard").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy

Set ol = GetObject(, "Outlook.Application") '/* if outlook is running, create otherwise */
Set olEmail = ol.CreateItem(0) 'olMailItem

With olEmail
    Set olInsp = .GetInspector
    If olInsp.EditorType = 4 Then 'olEditorWord
        Set wd = olInsp.WordEditor
        wd.Range.PasteAndFormat 13 'wdChartPicture
    End If

    wd.Paragraphs(1).Range.InsertAfter "Hi, There" & Chr(10)

    Sheets("chart").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy
    wd.Paragraphs(wd.Paragraphs.Count).Range.Characters.First.PasteAndFormat 13
    wd.Paragraphs.Add

    Sheets("chart").Range("B4:L17").SpecialCells(xlCellTypeVisible).Copy
    wd.Paragraphs(wd.Paragraphs.Count).Range.Characters.First.PasteAndFormat 13
    wd.Paragraphs.Add

    wd.Paragraphs(wd.Paragraphs.Count).Range.InsertAfter Chr(10) & Chr(10) & "BR"

    .Display
End With

Copy Excel range as Picture to Outlook

3 Answers3

Linked