How to insert email signature from EXCEL VBA

  • Thread starter Thread starter Eric_G
  • Start date Start date
E

Eric_G

I am attempting to generate an an email message (Outlook 2007) from my EXCEL
VBA. All is working but for the fact that the message does not include my
default signature. Any suggestions?

Dim OutApp As Object
Dim OutMail As Object

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

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "XXX"
.body = .body & "insert text here
'.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
.Attachments.Add ("filename.xls")
.Display 'or use .Display or .send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
 
Job seems to "bomb" at this line of code:

Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
 
FYI, email signatures will be added automatically under these
circumstances:

1) you have signatures set up under Tools > Options > Mail Format >
Signature for new messages / replies / forwards, or
2) you call the Display method before setting the Body property.

--JP
 
I have tried to .Display before .HTMLbody. The signature is inserted, but promptly disappears again. If I remove or disable .HTMLbody, the signature stays.

Any ideas how I can avoid losing the signature?
 
I have successfully inserted the default signature. It comes at the expense of having the e-mail message blink to the foreground, but has the advantage that it truly uses a user's default signature for a specified message format.

I have only tested this for HTML and plain text formatted messages sent using Outlook 2010 running under Windows 7 Ultimate.

After setting up the mail object, here is th change that preserves the signature:

With OutMail
...
'For an HTML formatted message"
.Display
.HTMLBody = MyHtmlBody & .HTMLBody
...
'For a plain-text message"
.Display
.Body = MyText & .Body
...
End With

It seems to be important to display immediately before setting the body, but I didn't experiment too much once I had a working solution.
 
Last edited:
Back
Top