Excel VBA- Include signature in Outlook email message

"Abys" Wallace
"Abys" Wallace used Ask the Experts™
on
Hi Experts,

Does anyone know how to include an end user's signature in the body of an email message?
Sample-Workbook.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
shorvath

Commented:
Different versions of Outlook store Signature files in different places.  What version?
"Abys" Wallaceself employed

Author

Commented:
@shorvath... My apologies .. MS Outlook 2010  (Office 2010)
Commented:
Hi abys757, attached is an update to your sample file.
To run it first copy the abys757.htm file attached to %userprofile%\AppData\Roaming\Microsoft\Signatures folder. It uses TextStream to bring in the signature HTML, just adding that as a string to the HTMLBody of the email
Let us know how it goes
...Terry
Sample-Workbook-2.xlsm
abys757.htm
"Abys" Wallaceself employed

Author

Commented:
Hi Terry :)

Your solution worked but I had a quick question.  do I have to know each end user's signature file name (ex:  abys757.htm, on my home pc its:  main.htm) so could it change?  if so is there a way to easily find the signature file name. a way to go to the signatures folder and choose the saved signature...  Just a clarifying question.. Thank you for the solution!
terencino

Commented:
Hi abys757 yes that is not so easy! I build & distribute all our corporate signatures, so they have a simple naming convention, being username_New.htm and username_Reply.htm. So to retrieve these into the code, I pickup the username with Environ("username") and add it into the code as follows:
SigString = Environ("userprofile") & _
     "\AppData\Roaming\Microsoft\Signatures\" & Environ("username") & "_New.htm"

Open in new window

Another option is to extract the hex from the registry, just look for New Signature or Reply-Forward Signature which are the defaults for new and reply/forward emails set in each users Outlook options. For example mine is buried deep at

HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\terencino\9375CFF0413111d3B88A00104B2A676\00000001

We use Office365, so it may be in a different location for you.

Yet another option might be to use FileSystemObject to cycle through the HTM files in the Signature folder, and present the user with a picklist or FileDialog so the can select the one they want. This might be more flexible as my users have up to 5 different signatures depending on the role and entity they are using at the time.

Let me know if your users have a pattern we can work with
...Terry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial