Paste range into email body...

[Rudi]

I'm trying to paste a range from a worksheet into the body of an email?
I was wondering if I could use the clipboard to do this, copy from the range, use Application (or Office) clipboard to paste into new email body.

I did find a long (complex) process fro Ron here, but is there no easier way to just paste the data into an email body?

TX

My code as current...

Code: Select all

Sub MailToBroker()
Dim OutApp As Object
Dim OutMail As Object
Dim rC As Range
Dim rF As Range
Dim rMsg As Range
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    For Each rC In Sheet0.UsedRange.Columns(1).Cells
        Set rF = Sheet2.Range("A:A").Find(What:=rC.Value)
        If Not rF Is Nothing Then
            Set rMsg = rF.CurrentRegion
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = rC.Offset(0, 1).Value
                .Subject = "New Trades: " & rC.Value & " - Please acknowledge trades"
                '.HTMLBody = Dump clipboard contents
                .Display '.Send
            End With
            On Error GoTo 0
        End If
        rF.Offset(-1).Font.Color = vbGreen
    Next rC
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

[HansV]

You could change the two lines

Code: Select all

                '.HTMLBody = Dump clipboard contents
                .Display '.Send

to

Code: Select all

                .Display
                OutApp.ActiveInspector.WordEditor.Content.Paste

Note that we MUST display the e-mail message first before we can use WordEditor to paste. Ron de Bruin's method will work even if you don't display the message but send it immediately.

[Rudi]

TX

It does paste the data, but it clears out the signature too.
When the email displays there is a signature, so is there a way to place the cursor in the body (above the sig) and then paste. I hope in this way it retains the sig.

TX

[HansV]

Try

Code: Select all

        OutApp.ActiveInspector.WordEditor.Range(0, 0).Paste

[Rudi]

Perfect!
TX