News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Copy range from Excel and send in Outlook

    Home Forums AskWoody support Microsoft Office by version Questions: Microsoft Office Copy range from Excel and send in Outlook

    Tagged: 

    This topic contains 2 replies, has 3 voices, and was last updated by  Lugh 1 month ago.

    • Author
      Posts
    • #1874994 Reply

      Simmo7
      AskWoody Lounger

      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

    • #1875068 Reply

      Kirsty
      Da Boss

      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

    • #1876781 Reply

      Lugh
      AskWoody_MVP

      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

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Copy range from Excel and send in Outlook

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.