Pasting a range into an email, while also adding text to body and keeping signature

[Apoc]

Hi,
So I am trying to create a script that will copy data from a range of cells on my spread sheet, open up a new email, paste in the range, add some text to the email body and contain my default email signature.

The email should ideally look like:

"Hi
Blablabla example text"

*Pasted range from excel*

Signature

I have worked out how to do most of this with bits of my own knowledge and some helpful tutorials however I cannot seem to get both the pasted range and the text body in the email without it removing my signature. Code so far:


Private Sub CommandButton2_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim wdDoc As Object
Dim oRng As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Range("B55:L58").Select
Selection.Copy

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.Display
.To = "TestEmailAddress"
.CC = "TestEmailAddress"
.BCC = ""
.Subject = "test"
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.collapse 1
oRng.Paste


End With

End Sub


This works fine for the range + signature, but if I try to add text to the body of the email with .Body or .HTMLBody, it either gets rid of my signature or the excel range. I am too new to VBA to understand why or how to fix this, can anyone assist?
Using Outlook 2016/Excel 2016.
Many thanks,

Apoc

[Kenneth Hobs]

Welcome to the forum!

If you are going to use the WordEditor method, use it. It can be a bit tricky but provides a nice result. Plus, you are working in 3 Applications. While not all Word things are possible, many are. Record macros in Word and then adapt to WordEditor. Look at the code at the end for tips.

WordEditor really gets tricky when you add a copy/paste OLEobject but it can be done.

Otherwise, go with htmlBody method. https://www.rondebruin.nl/win/s1/outlook/signature.htm

Normally, I just copy/paste in the content from a rtf file for the signature. PasteSpecial with InLine option is usually the best but Paste "may" work out ok.

You can find one path to signature.rtf file as Ron shows at his site. That may or may not be your "default" signature.

I can show a more complete example if needed. For your range Copy, you might find that CopyPicture method is the better choice.
e.g.

'Tools > References > Microsoft Word xx.0 Object Library > OK
Dim Word As Document, wr As Word.Range, rTo As Recipient

.GetInspector.Display
Set Word = .GetInspector.WordEditor
Word.Content = "Dear Sir, " & vbCrLf & vbCrLf & S & _
vbCrLf & vbCrLf & "Confirmation Attached" _
& vbCrLf & vbCrLf & _
"Regards," & vbCrLf & "My name," & vbCrLf & _
"Firm name, & Contact detail.."


'Paste sig.rtf
Set wr = Word.Range
wr.Collapse Direction:=wdCollapseEnd
'Copy content from sig.rtf as signature for body of email to clipboard.
GetObject(sig).Range.Copy
wr.Paste