Solved

Excel VBA- Include signature in Outlook email message

Posted on 2012-12-28
5
656 Views
Last Modified: 2012-12-29
Hi Experts,

Does anyone know how to include an end user's signature in the body of an email message?
Sample-Workbook.xlsm
0
Comment
Question by:"Abys" Wallace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:shorvath
ID: 38728498
Different versions of Outlook store Signature files in different places.  What version?
0
 

Author Comment

by:"Abys" Wallace
ID: 38728644
@shorvath... My apologies .. MS Outlook 2010  (Office 2010)
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38728812
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
0
 

Author Closing Comment

by:"Abys" Wallace
ID: 38729286
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!
0
 
LVL 16

Expert Comment

by:terencino
ID: 38730129
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
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question