VBA send email from Excel using OFT template - Suppress automatic signatures

Question

I've created a macro that opens an Outlook email template ready for the user to send (with some additional info pulled from the spreadsheet). All is going well except that Outlook is automatically appending a signature based on user settings. While I can turn off my own signature pretty easily, I'm not the only person that may be running this macro.

The email template already has a company signature in it, so adding a personal signature as well is not wanted.

Google offers plenty of suggestions for getting signatures ON to VBA generated emails, but I couldn't find anything to get them OFF. Any ideas?

Existing code:

... snip ...

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItemFromTemplate("\\Path\To\MyFile.oft")

With otlNewMail
.Bcc = vEmailsFromSpreadsheet '(set earlier on)
.Display
End With

'otlApp.Quit
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing

Answer 1

@JoeMalpass,

I know this is an old post, but for the benefit of anyone else who might stumble on this...

From reading your solution, I learned that the problem is that when you use .Display on an email created from a template, Outlook appends a signature. Since that is the only problem, it occurred to me that the solution might be much simpler than the one you presented.

In the code from your original post, you can simply replace

    .Display

with

    BodyWithoutSignature = .HTMLBody
    .Display
    .HTMLBody = BodyWithoutSignature

Since .Display appends a signature you don't want, simply replace the body with what was there before the signature was added. I tested it, and (was surprised that) it actually works. It was that easy!

Answer 2

OK, so after much searching I figured out a way to achieve what I'm trying to do by combining a bunch of similar solutions to different problems.

I learned that as long as you do not .Display the email then automatic user signatures do not get added. Therefore you can open (without displaying) the email template to copy the body, subject, and other email parameters you want from the template email into VB variables. Those variable values can then be used to overwrite parts of the email that you DO want to .Display to the user.

Here my final code:

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItemFromTemplate("\\Path\to\myfile.oft")
With otlNewMail
vTemplateBody = otlNewMail.HTMLBody
vTemplateSubject = otlNewMail.Subject
.Close 1
End With

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(0)
With otlNewMail
.Display
.SentOnBehalfOfName = vFrom
.Bcc = vToList
.Subject = vTemplateSubject
.HTMLBody = vTemplateBody
End With

I'm not sure if the double use of Set otlApp etc is necessary or redundant, please feel free to edit this post if there is a more concise way to write it.