Copy range from Excel and send in Outlook

Topic

New Reply

Hi,

The following code was previously working under Win7, but has stopped working in Win10.  But now we are getting an error at the following statement

Set objMailDocument = objMail.GetInspector.WordEditor

Can anyone help please?

Here is the full code.

Sub ExportInsert_ScreenshotOfSheet_Mail()
Dim objSheet As Excel.Worksheet
Dim objUsedRange As Excel.Range
Dim objOutlookApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim objMailDocument As Word.document

'Refresh

Sheets("MacroEODEmail").Visible = True
Sheets("Failed").Visible = True
Sheets("On Queue").Visible = True

EmailTO = ThisWorkbook.Sheets("MacroEODEmail").Range("C3").Value
EmailCC = ThisWorkbook.Sheets("MacroEODEmail").Range("C4").Value
strSubject = ThisWorkbook.Sheets("MacroEODEmail").Range("C5").Value & ThisWorkbook.Sheets("MacroEODEmail").Range("D5").Value
FilePath = ThisWorkbook.Sheets("MacroEODEmail").Range("C7").Value
strbody = "<font size=""2"" face=""Helv"">" & _
"Hi All, <br><br>" & "Please see below status as of 3:00 PM. <br><br></font>"
strbody = strbody & "File Location: "
strbody = strbody & "" & FilePath & "<br><br>"

Set objOutlookApp = CreateObject("Outlook.Application")
Set objMail = objOutlookApp.CreateItem(olMailItem)

With objMail
.Display
.To = EmailTO
.CC = EmailCC
.BCC = ""
.Subject = strSubject
.HTMLBody = strbody & .HTMLBody
'.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
'.Send
End With

x = 324
Set objSheet = ActiveWorkbook.Sheets("Summary")
Set objUsedRange = objSheet.UsedRange
'Copy the screenshot of the sheet
objUsedRange.CopyPicture xlScreen, xlPicture

Set objMailDocument = objMail.GetInspector.WordEditor
'Paste the copied screenshot
objMailDocument.Range(x, x).Paste

Set objSheet = ActiveWorkbook.Sheets("Failed")
Set objUsedRange = objSheet.UsedRange
'Copy the screenshot of the sheet
objUsedRange.CopyPicture xlScreen, xlPicture

objMailDocument.Range(x + 2, x + 2).Paste

Set objSheet = ActiveWorkbook.Sheets("On Queue")
Set objUsedRange = objSheet.UsedRange
'Copy the screenshot of the sheet
objUsedRange.CopyPicture xlScreen, xlPicture

objMailDocument.Range(x + 4, x + 4).Paste

Sheets("MacroEODEmail").Visible = False
Sheets("Failed").Visible = False
Sheets("On Queue").Visible = False

End Sub

Edit: Removed HTML

Reply | Quote

Replies

Hi @simmo7 – welcome to AskWoody.

This link is on a similar issue, and may be of some assistance to you:
Copy-and-Paste Range from Excel 2016 into Outlook 2016 as Picture

Reply | Quote

Simmo, I don’t understand the code, but is it possible Word was your email editor in Win7, but isn’t in Win10? That’s the thought the error sparks for me.

Lugh.
~
Alienware Aurora R6; Win10 Home x64 1803; Office 365 x32
i7-7700; GeForce GTX 1060; 16GB DDR4 2400; 1TB SSD, 256GB SSD, 4TB HD

Reply | Quote