Macro for Outlook Email Signatures

[LaurieLundstrom]

Hello Experts!

I am using the following to successfully create an email and attach a pdf to it. All works great, except the email signature does not show.

I believe the signature is being replaced with the body text.
outlookmailitem.body="Hello,".....

I am very new to macros (less than 24 hours). Please be real specific and don't assume I know anything. Thanks,

Sub CreateEmail()
Dim edress As String
Dim cc As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim attachment As String


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments
path = "C:\Users\llundstrom\Desktop\Quotes\"


edress = ActiveSheet.Range("c46")
cc = Sheets("Formulas & Macros").Range("a27")
subj = ActiveSheet.Range("c4")
filename = ActiveSheet.Range("c48")
attachment = path + filename


outlookmailitem.To = edress
outlookmailitem.cc = cc
outlookmailitem.bcc = ""
outlookmailitem.Subject = subj
outlookmailitem.body = "Hello," & vbCrLf & "Please find your quote attached." & vbCrLf & "Thanks,"


myAttachments.Add (attachment)
outlookmailitem.display


End Sub

 

[shknbk2]

Hello! Welcome to the forum and to programming with VBA. This is an amazing effort in such a short amount of time.

Prior to displaying the email, there is no signature. So we can display the email and then change the body. However, that also leads to the 2 types of Body that can be changed: Body (as you have it) and HTMLBody.

Body is useful to add simple text, but formatting the text is not possible. If you use Body, you can put your text in front of the signature, but any formatting you had in the signature will disappear, and it will show up in plain text. If that is okay, then Body will work fine.

HTMLBody is where you want to go to include text formatting, though it can be tricky to get used to the way that HTML codes work. In the code below, I used HTMLBody so that any formatting in the signature will be preserved. Note the change in your body text where, instead of including vbCrLf, the text includes < br>, which does the same/similar thing in HTML-speak.

NOTE: in order to get the < br> to show up as text in this reply, a space was needed between the "<" and "b". Funny thing, not having the space works as it should in this reply which is HTML-based and creates the new paragraph instead of showing the text. In the code below, after you paste it into Excel, remove the space.

Sub CreateEmail()
Dim edress As String
Dim cc As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim attachment As String


Set outlookapp = CreateObject("Outlook.Application")
Set outlookmailitem = outlookapp.createitem(0)
Set myAttachments = outlookmailitem.Attachments
path = "C:\Users\llundstrom\Desktop\Quotes\"


edress = ActiveSheet.Range("c46")
cc = Sheets("Formulas & Macros").Range("a27")
subj = ActiveSheet.Range("c4")
filename = ActiveSheet.Range("c48")
attachment = path + filename

outlookmailitem.To = edress
outlookmailitem.cc = cc
outlookmailitem.bcc = ""
outlookmailitem.Subject = subj

myAttachments.Add (attachment)
outlookmailitem.display
outlookmailitem.HTMLBody = "Hello,< br>Please find your quote attached.< br>Thanks," & outlookmailitem.HTMLBody

End Sub