Paste text and multiple Excel ranges as images in Outlook using wordeditor

Question

I am trying to paste multiple Excel ranges as images in Outlook mail using VBA. I am using the answer to this question (Pasting an Excel range into an email as a picture) to paste a range of excel as image in mail but as soon as I paste another range, it overwrites the previous image. Is there anyway to change the cursor position in Outlook mail using wordeditor. I tried using collapse before pasting the image but it did not help. Also how do I add the text to it as using Outmail.body to edit anything gets overwritten too by the image pasted afterwards. This is the code I am using:

Sub Sendmail()
Dim r as range
Set r = Range("C2:O13)
r.copy
dim outlookapp as Outlook.Application
set outlookapp = CreateObject("Outlook.Application")
dim outMail As Outlook.Mailitem
Set outMail = outlookApp.CreateItem(olMailItem)
With outMail
.Display
.CC = "xyz@abc.com"
.Subject = "Test"
.Body = "Dear" & "Macro" & vbnewline
end with
outmail.Display
'Opening wordeditor
dim worddoc as Word.Document
Set worddoc = Outmail.GetInspector.WordEditor
worddoc.range.PasteandFormat wdChartPicture
'Adding new line after pasting image
worddoc.range.Insertafter vbNewline
' Adding second image
dim s as range
set s= Range(P2:Z30)
s.copy
worddoc.range.PasteandFormat wdChartPicture

WordEditor the [add-in](https://www.microsoft.com/p/wordeditor/9wzdncrdgbpk) or WordEditor the [property](https://learn.microsoft.com/office/vba/api/outlook.inspector.wordeditor)? Can you add to specific detail about what you're trying to do and what you've tried so far? (see [ask] as well as [mcve]). — ashleedawg, Dec 05 '18 at 21:46
Sorry for not adding Code before. I have added the code in the question. — Kuljeet Keshav, Dec 06 '18 at 10:02

score 0 · Answer 1 · answered Dec 06 '18 at 02:08

You could refer to the below code:

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

For more information, please refer to these links:

Copy Excel range as Picture to Outlook

Copy range of cells from Excel as picture and add text in the email body

Paste text and multiple Excel ranges as images in Outlook using wordeditor

1 Answers1