Apoc
11-08-2017, 09:12 AM
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
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