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:
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?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
Using Outlook 2016/Excel 2016.
Many thanks,
Apoc





Reply With Quote
