In this Article you are going to learn how to insert Outlook Signature in outlook email while sending an email via Excel VBA. It means while sending an email from Outlook via Excel Macro, if you want already saved signature to be inserted at the end of your email, then here is the code to do so.
<< Return to Send Email Tutorial Page
Important: |
As soon as you create a signature in Outlook it saves the signature in 3 different types of files: .HTM, TXT and RTF as shown below: These files get stored at the following location in your system: Windows 7 and 8 :
|
Note:
In the below code when we are creating an email in Outlook then at the end of the email, we will insert the Signature from the .txt file or .htm file.
In the below code we are first checking if these files exists and have some values in it. If yes then we are reading the text from the signature file and inserting that signature in the email.
Thus we need a function which read and return the Signature Texts from the file wherever we need to insert it in the body of the Outlook email.
Function to read the Signature file and return the Signature Text
Function GetSignature(fPath As String) As String Dim fso As Object Dim TSet As Object Set fso = CreateObject("Scripting.FileSystemObject") Set TSet = fso.GetFile(fPath).OpenAsTextStream(1, -2) GetSignature= TSet.readall TSet.Close End Function
Below is the code to create the Outlook Email with the Signature at the end.
Here there could be two types of Email and Signatures:
- Email and Signature with Simple Text
- Email and Signature with the HTML
We will see the code of both the above methods below one by one:
Email and Signature with Simple Text
Sub With_Text_Signature() 'Do not forget to change the email ID 'before running this code Dim OlApp As Object Dim NewMail As Object Dim EmailBody As String Dim StrSignature As String Dim sPath As String Set OlApp = CreateObject("Outlook.Application") Set NewMail = OutApp.CreateItem(0) EmailBody = "Type the Body of your email" '***************************************************** ' Important '***************************************************** ' go to the appdata path as mentioned in ' the above important point. Check the name of ' the signature file. For example: here in my system ' the signature's file name is vish.txt, vish.htm ' Therefore before running this code, check the ' name fo the signature file in your system and ' replace vish.txt with your file name. '**************************************************** sPath = Environ("appdata") & "\Microsoft\Signatures\vish.txt" ' If the path and file name given by you is not ' correct then code you insert a blank Signature If Dir(sPath) <> "" Then StrSignature = GetSignature(sPath) Else StrSignature = "" End If On Error Resume Next With NewMail .To = "info@learnexcelmacro.com" .CC = "info@learnexcelmacro.com" .BCC = "info@learnexcelmacro.com" .Subject = "Type your Subject here" ' Here at the end of the Email Body ' Text Signature is inserted. .Body = EmailBody & vbNewLine & vbNewLine & StrSignature .send End With On Error GoTo 0 Set NeMail = Nothing Set OlApp = Nothing End Sub
Email and Signature with HTML Body and HTML Signature
Sub With_HTML_Signature() 'Do not forget to change the email ID 'before running this code Dim OlApp As Object Dim NewMail As Object Dim EmailBody As String Dim StrSignature As String Dim sPath As String Set OlApp = CreateObject("Outlook.Application") Set NewMail = OlApp.CreateItem(0) ' Here Since we are talking about ' the HTML email then we need to ' write the Body of the Email in ' HTML. EmailBody = "Hello Friends !!" & "
Welcome to LearnExcelMacro.com" & vbNewLine & _ "Here i will make you awesome in Excel Macro.
You can mail me at info@learnexcelmacro.com" '***************************************************** ' Important '***************************************************** ' go to the appdata path as mentioned in ' the above important point. Check the name of ' the signature file. For example: here in my system ' the signature's file name is vish.txt, vish.htm ' Therefore before running this code, check the ' name of the signature file in your system and ' replace vish.txt with your file name. '**************************************************** sPath = Environ("appdata") & "\Microsoft\Signatures\vish.htm" ' If the path and file name given by you is not ' correct then code you insert a blank Signature If Dir(sPath) <> "" Then StrSignature = GetSignature(sPath) Else StrSignature = "" End If On Error Resume Next With NewMail .To = "info@learnexcelmacro.com" .CC = "info@learnexcelmacro.com" .BCC = "info@learnexcelmacro.com" .Subject = "Type your Subject here" ' Here at the end of the Email Body ' HTML Signature is inserted. .htmlBody = EmailBody & "
" & StrSignature .send End With On Error GoTo 0 Set NeMail = Nothing Set OlApp = Nothing End Sub
Important : Outlook Signature with an Image
If your signature contains some image, then above method will not display the image in signature. You will see that all the texts are displayed in signature but not the image.
To overcome this issue, there are two solutions.. refer to my new article
PLease send the exel macro from outlook with Signature
Have tried but its not taking
you will need to create a separate function for GetSignature(SigString) eg. below
Function Getsignature(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetSignature = ts.readall
ts.Close
End Function
Awesome code..was looking for same for a while. Thanks a ton.
My default signature has two images in it as well it showing as “image cannot be displayed” and a cross within it. Do we need to tweak this code to use the signature with its images also?
Thanks a ton.
Vish, this works, but the font used in the message body does not match my default font, which is used in my signature (e.g. Thanks, Eric) so it looks odd.
Is there any way to fix this? I have tried creating an email with only the signature, and then using SendKeys to paste my message into the body, but there must be a better way.
Thanks,
Eric
Hello,
I am getting the same error, My default signature has image in it as well it showing as “image cannot be displayed” and a cross within it. Please advise
Hello … The code is great but when i open te new email ( replaced .send with .display ) the image in the signature isn’t showing 🙁
what if your using a citrix based system?
The code to add my signature does not want to display my signature, instead I get a “image cannot be displayed” message. I would be great full for some advice to solve this problem
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
What is the meaning of this line?? in the above function??
Thanks. Pity about the image.
I think there’s a typo in your code on line 58.
Set NeMail = Nothing
should be
Set NewMail = Nothing
I cannot get the html version to work. In your example you say put the emailBody in as HTML, but you don’t do that in your example. For some reason, I can get the signature OR the body to appear, but not both at the same time. 🙁