Copy Excel Range & Paste as picture in Outlook using VBA

Question

Here is the VBA code which I used to copy Excel range & paste it as picture in Outlook Email body. It used to work for me, but recently when I tried it after long gap of 1 year, I can see that macro is terminating on this line "doc.Range.PasteAndFormat wdChartPicture" without any error message.

Please help in identifying the issue.

Sub CopyExcelRangeToOutlookMail()

        
        Dim outlookApplication As Outlook.Application
        Dim mail As Outlook.MailItem
        Dim insp As Outlook.Inspector
        Dim doc As Word.Document
        Dim msgText As String
        Dim myCell1 As Range
        Dim To_List As String
        Dim myCell2 As Range
        Dim CC_List As String
        Dim myCell3 As Range
        Dim BCC_List As String
        
        Set outlookApplication = New Outlook.Application
        Set mail = outlookApplication.CreateItem(olMailItem)
        
            With mail
                     .BodyFormat = olFormatRichText
                     .Display
                     .To = To_List
                     .CC = CC_List
                     .BCC = BCC_List
                     .Subject = "Team wishes you Happy Birthday!"
                     Set insp = .GetInspector
                     Set doc = insp.WordEditor
                     
                    doc.Range.InsertBefore msgText
                    Sheet2.Range("A7:S51").Copy
                     
                    DoEvents
                    doc.Range.PasteAndFormat wdChartPicture    'macro terminating while trying to execute this line without error or result

                    Application.CutCopyMode = False
            End With

End Sub

The range I am trying to copy from excel is having picture in it — Puntal, Jun 27 '23 at 06:10
Also try to remove the `.BodyFormat = olFormatRichText` line from the code and see whether it helps. — Eugene Astafiev, Jun 27 '23 at 08:05
Have you recently changed to Office365? [This answer](https://stackoverflow.com/a/62163275/19353309) speaks of an issue from Office365 with a different solution. — Notus_Panda, Jun 27 '23 at 08:09
@Notus_Panda ... No. I have been using Office 365 for quite some time — Puntal, Jun 27 '23 at 10:13
probably the picture was never copied. If there is only one picture on that sheet, try this: Application.ActiveSheet.Shapes.Item(1).Copy — hennep, Jun 27 '23 at 11:39
Try `Sheet2.Range("A7:S51").CopyPicture` followed by a regular paste. — Tim Williams, Jun 27 '23 at 17:45

Copy Excel Range & Paste as picture in Outlook using VBA

0 Answers0