Copy Excel chart to Outlook mail message

Question

I have email addresses in column A, and a chart object in the same sheet.

For each email address, I want to create a new mail in Outlook and paste the Excel chart into the email body.

The problem with my attempt (below) is that the chart does not get pasted into the mail body. How do I fix this?

This my code:

Sub smail()        
    Dim r As Integer
    Dim o As Outlook.Application
    Dim m As Outlook.MailItem
    Set o = New Outlook.Application
    r = 1
    Do While Cells(r, 1) <> ""
        Set m = o.CreateItem(olMailItem)
        m.To = Cells(r, 1)
        m.CC = "[email protected]"
        m.BCC = "[email protected]"
        m.Subject = "Test"
        ActiveChart.ChartArea.Copy
        Set wEditor = o.ActiveInspector.WordEditor
        'm.Body = Paste
        wEditor.Application.Selection.Paste

        m.Send
        r = r + 1
        Set m = Nothing
    Loop
End Sub

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2014-09-01T13:33:57

I think the problem with this line

wEditor.Application.Selection.Paste

is that nothing is selected, i.e. .Selection returns Nothing, as long as the message is not visible. To solve this, make it visible before pasting:

m.Display

That worked for me.

Also, you should always declare all your variables using Dim, including wEditor:

Dim wEditor As Word.Document

Copy Excel chart to Outlook mail message

1 Answers