CODEWITHSUNDEEP
vbaemailexceloutlook
HattrickNZ
HattrickNZ

Reputation: 4643

vba + Selection.Paste into outlook + control poition

What I am trying to do is copy a chart from excel into an outlook email, but after numerous searching I am struggling.

i am having trouble positioning where the chart is pasted. I want it to paste after the last line "this is another line again " in the body of the email. It currently pastes at the start of the email before the line "test ... body"

 Sub CopyAndPasteToMailBody3() ' this works but how do i control where it puts the chart?
        Set mailApp = CreateObject("Outlook.Application")
        Set mail = mailApp.CreateItem(olMailItem)
        mail.Display
        mail.To = "[email protected]"
        mail.subject = "subject" & Now
        mail.body = "test ... body" & vbNewLine & vbNewLine _
                    & "this is another line " & vbCrLf _
                    & "this is another line again "
        Set wEditor = mailApp.ActiveInspector.wordEditor
        ActiveChart.ChartArea.Copy ' chart needs to be active
        wEditor.Application.Selection.Paste

        ' mail.send
    End Sub

Note: using excel 10 on windows 7

Upvotes: 0

Views: 7266

Answers (3)

Patrick Thompson
Patrick Thompson

Reputation: 41

I have found that 

Set wEditor = mailapp.ActiveInspector.WordEditor

needs to be followed by

wEditor.Range(0, 0).Select

to avoid an error sometimes when you go to paste it.

Upvotes: 4

user3514930
user3514930

Reputation: 1717

You can try also with another code (in this case the image are temporary saved on disk):

Sub CopyAndPasteToMailBody4() ' this works but how do i control where it puts the chart?
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(0)
    mail.Display
    mail.To = "[email protected]"
    mail.Subject = "subject" & Now

    Dim Stri As String
    Stri = "test ... body" & vbNewLine & vbNewLine _
                & "this is another line " & vbCrLf _
                & "this is another line again " & vbNewLine & " "
    ActiveChart.Export "e:\0\C1.png"
    Stri = Stri & "<img src='e:\0\C1.png'>"
    mail.HTMLBody = Stri
    ' mail.send
End Sub

On my PC the first code ask me some permission, with the second code no...

Upvotes: 0

user3514930
user3514930

Reputation: 1717

You can modify the code put the Body on the Clipboard and Paste it:

    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    mail.To = "[email protected]"
    mail.Subject = "subject" & Now

    Dim Clip As MSForms.DataObject
    Set Clip = New MSForms.DataObject
    Clip.SetText ("test ... body" & vbNewLine & vbNewLine _
                & "this is another line " & vbCrLf _
                & "this is another line again " & vbNewLine & " ")
    Clip.PutInClipboard
    Set wEditor = mailApp.ActiveInspector.wordEditor
    wEditor.Application.Selection.Paste

    ActiveChart.ChartArea.Copy ' chart needs to be active
    wEditor.Application.Selection.Paste
    ' mail.send

In this case you can assembly the mail as you want.
MSForms.DataObject need to have the Reference: Microsoft Form 2.0 Object Library (FM20.DLL)

Upvotes: 0

Related Questions