CODEWITHSUNDEEP
excelvbaoutlook
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Adding signature to an automated outlook mail

I am trying to add signature at the end of the automated mails I am sending. I want the signature to be the default signature of the user that runs the macro. The code I have written runs without crushing but does not insert the signature. I am providing the code below.

Dim OutApp As Object
Dim OutMail As Object
Dim currentDate As Date
Dim DeliveryDate As String
Dim Recipients As String
Dim CarbonCopy As String
Dim Signature As String

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

currentDate = Format(Date, "dd/mm/yyyy")
Recipients = "[email protected]"
CarbonCopy = "[email protected]"
Signature = OutMail.body

msg = "<span style='color:black'><p>Dear Team,</p>"

msg = msg & "Thank you in advance</span>"

On Error Resume Next
With OutMail
    .To = Recipients
    .CC = CarbonCopy
    .Subject = "PSR " & currentDate
    .HTMLBody = "<span style = 'color:#1F497D'>" & msg & "</span>" & Signature
    .Attachments.Add ThisWorkbook.FullName
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Upvotes: 3

Views: 397

Answers (3)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66306

Firstly, you cannot concatenate two HTML strings and expect a valid HTML string back. They must be merged. More than that, you also need to merge the styles etc. from two HTML documents.

Secondly, to retrieve the signature in your case, MailItem must be shown first - call Display, and only then read the HTMLBody property.

If using Redemption is an option (I am its author), it exposes RDOSignature object and allows to insert any signature without displaying the message using RDOSignature.ApplyTo() method.

Upvotes: 1

pondersome
pondersome

Reputation: 183

The signature has to be declared as a variant and you have to display the empty email first to capture it.

Your "msg" isn't declared in the above code. I'm assuming you've got that covered. Otherwise your code won't work. Given that assumption...

Dim OutApp As Object
Dim OutMail As Object
Dim currentDate As Date
Dim DeliveryDate As String
Dim Recipients As String
Dim CarbonCopy As String
Dim Signature As Variant

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

currentDate = Format(Date, "dd/mm/yyyy")
Recipients = "[email protected]"
CarbonCopy = "[email protected]"
Signature = OutMail.Body

'msg hasn't been defined so it's commented out. msg in the body has been replaced with "msg".
'msg = "<span style='color:black'><p>Dear Team,</p>"

'msg = msg & "Thank you in advance</span>"

On Error Resume Next
With OutMail
    'Capture signature block.
    .Display
    Signature = .HTMLBody
    .To = Recipients
    .CC = CarbonCopy
    .Subject = "PSR " & currentDate
    .HTMLBody = "<span style = 'color:#1F497D'>" & "msg" & "</span>" & Signature
    .Attachments.Add ThisWorkbook.FullName
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

Upvotes: 3

Franksta
Franksta

Reputation: 139

I think your mailItem does not become active. Try adding OutMail.display before you try getting the body. Then it should work.

Upvotes: 0

Related Questions