Add Signature to HTML email using VBA

Topic

New Reply

Can someone help me figure out how to add my standard signature to an email I create and send using VBA. I can get everything I want but that piece. Thanks.

Code:

Sub testemail()

If MsgBox(“Send Email?”, vbYesNo + vbQuestion, “Email”) = vbYes Then
‘send email
Dim myOutlok As Object
Dim myMailItm As Object
Dim signature As String
Shell (“OUTLOOK”)


Set otlApp = CreateObject(“Outlook.Application”)
Set OtlNewMail = otlApp.CreateItem(olMailItem)

signature = OtlNewMail.HTMLBody
OtlNewMail.HTMLBody = signature
With OtlNewMail
.To = Range(“H9”).Value
.CC = Range(“H10”).Value
.Subject = Range(“H11”).Value
.HTMLBody = “Hello,” & “
” & Range(“H12”).Value & “
” & “
” & “
” & “Thank you,” & “
” & “
” & signature
‘.Attachments.Add “U:CRAIGActual” & Range(“H6”).Value & “” & Range(“H8”).Value & “” & Range(“H13”).Value & “.xls”
.Send

End With



Set OtlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
Else
Exit Sub
End If
End Sub

Reply | Quote

Replies

Balla,

I have amended you code slightly and added the additional lines needed to grab your default signature.

HTH,
Maud

Code:

Sub testemail()
[COLOR=”#008000″]’—————————————–
‘DETERMINE IF EMAIL TO BE SENT[/COLOR]
If MsgBox(“Send Email?”, vbYesNo + vbQuestion, “Email”) = vbYes Then
[COLOR=”#008000″]’—————————————–
‘DECLARE AND SET VARIABLES[/COLOR]
    Dim myOutlok As Object
    Dim myMailItm As Object
    Dim Signature As String
    Shell (“OUTLOOK”)
    Set otlApp = CreateObject(“Outlook.Application”)
    Set OtlNewMail = otlApp.CreateItem(olMailItem)
[COLOR=”#008000″]’—————————————–
‘GET DEFAULT EMAIL SIGNATURE[/COLOR]
    Signature = Environ(“appdata”) & “MicrosoftSignatures”
    If Dir(Signature, vbDirectory)  vbNullString Then
        Signature = Signature & Dir$(Signature & “*.htm”)
    Else:
        Signature = “”
    End If
    Signature = CreateObject(“Scripting.FileSystemObject”).GetFile(Signature).OpenAsTextStream(1, -2).ReadAll
[COLOR=”#008000″]’—————————————–
‘CREATE EMAIL[/COLOR]
    OtlNewMail.HTMLBody = Signature
    With OtlNewMail
    .To = Range(“H9”).Value
    .CC = Range(“H10”).Value
    .Subject = Range(“H11”).Value
    .HTMLBody = “Hello,” & “
” & Range(“H12”).Value & “
” & “
” & “
” & _
        “Thank you,” & “
” & “
” & Signature
    .display
    ‘.Send
    End With
[COLOR=”#008000″]’—————————————–
‘CLEANUP[/COLOR]
    Set OtlNewMail = Nothing
    Set otlApp = Nothing
    Set otlAttach = Nothing
    Set otlMess = Nothing
    Set otlNSpace = Nothing
End If
End Sub

Reply | Quote

You even know where signatures hide. 🙂

cheers, Paul

Reply | Quote

Paul,

They are located in the “C:UsersMaudibeAppDataRoamingMicrosoftSignatures” folder where Maudibe is replaced by the user’s name. The signature will be the .htm file(s) in that folder.

Reply | Quote

This works beautifully!!! Thanks Maudibe.

Reply | Quote

This solution works perfectly for me, except in one aspect – the image from my default signature does not display in the generated email. Instead, the outline of it shows, and it says “This image cannot currently be displayed.” I wonder if you might know how to resolve this?

Thank you for the original solution.

Reply | Quote

Bob,

I can’t tell you how many solutions I have tried to do what you are asking but never found anything that really worked. As a workaround you can do what I do.

1. Create an Outlook signature without a logo. Your default signature is the first one in the “C:UsersMaudibeAppDataRoamingMicrosoftSignat ures” folder so make this created signature appear first by naming it something that will be first in alphabetical order. Using the .HTMLBody property, compose your message with the Signature variable at the end of your HTML code (see code post#2) then apply an image tag after a break tag. If your image is sized right, it will appear as part of the signature.

Code:

.HTMLBody = “Hello,” & “
” & Range(“H12”).Value & “
” & “
” & “
” & _
        “Thank you,” & “
” & “
” & Signature & “
” & _
        “”

2. Instead of using the default signature, build your signature right in the .HTMLBody property value then apply the image tag as above.

HTH,
Maud

Reply | Quote

Bob,

I can’t tell you how many solutions I have tried to do what you are asking but never found anything that really worked. As a workaround you can do what I do.

1. Create an Outlook signature without a logo. Your default signature is the first one in the “C:UsersMaudibeAppDataRoamingMicrosoftSignat ures” folder so make this created signature appear first by naming it something that will be first in alphabetical order. Using the .HTMLBody property, compose your message with the Signature variable at the end of your HTML code (see code post#2) then apply an image tag after a break tag. If your image is sized right, it will appear as part of the signature.

Code:

.HTMLBody = “Hello,” & “
” & Range(“H12”).Value & “
” & “
” & “
” & _
        “Thank you,” & “
” & “
” & Signature & “
” & _
        “”

2. Instead of using the default signature, build your signature right in the .HTMLBody property value then apply the image tag as above.

HTH,
Maud

In the .htm file in the signatures directory you can edit the htm file. The pictures are stored as relative path and when you use the code it looses that path so if you use discrete path it will be able to find the pictures. so go into the file and look for any relative paths and make them discrete.

“/Microsoft/Signatures/picturefile.jpg”

change that to include the whole path

“/root/user/blah blah../Microsoft/Signatures/picturefile.jpg”

This solved the missing image problem for me.

Reply | Quote

will this work if your using a citrix based system?

Reply | Quote

Have you tried creating a template email with the signature already present and then using to create the mailitem rather than just creating a blank one?

Reply | Quote

Assuming you mean Windows virtual desktop, then yes, it will work. If you are using XenApps it may or may not, depending on the packaging.
More information on your set up please.

cheers, Paul

Reply | Quote

we are using using citrix to access our own workspace from another server from our main site.

the automail is a mail loop wherein we have 3 tabs, 1st tab is sending email tab which us used to create the body of your email, enter you subject, the second tab is where you input a report or summary of bills etc, the 3rd tab is for email database.

basically just put the report needed, make sure you have the email address on the database, then it will send all emails to all recipient based on the report you’ve created.

my concern is that we are unable to access the location on the signature’s because of we only accessing it from another server.

Assuming you mean Windows virtual desktop, then yes, it will work. If you are using XenApps it may or may not, depending on the packaging.
More information on your set up please.

cheers, Paul

Reply | Quote

Do you use a desktop at all or just Citrix VD? If it’s just Citrix you should have the same C: all the time, regardless of where you log in. If this is not the case you can set up a scheduled task to copy all signatures to the required location.

cheers, Paul

Reply | Quote

its having your own desktop. if you to my computer C: is not present but I can still access it if I type it manually, but I’, unable to locate appdata, I think this is hidden by default, we don’t have the any access on changing those settings

Do you use a desktop at all or just Citrix VD? If it’s just Citrix you should have the same C: all the time, regardless of where you log in. If this is not the case you can set up a scheduled task to copy all signatures to the required location.

cheers, Paul

Reply | Quote

mrpasawai,

It will work if you built your signature into the body of your email instead of trying to attach it. Use something like

Code:

.HTMLBody = “Hello,” & “
” & _
        message & “
” & “
” & _
        “Thank you,” & “
” & _
        “Maudibe” & “
” & _
        ““

The image tag should point to the server folder that contains the image file and not a local folder on the computer where Citrix is being run
HTH,
Maud

Reply | Quote

Any thoughts about why when I use to have an image attachment hidden but was inserted in the body as part of the signature that it would no longer be visible when upgrading from 2010 to 2016 office?

mrpasawai,

It will work if you built your signature into the body of your email instead of trying to attach it. Use something like

Code:

.HTMLBody = “Hello,” & “
” & _
        message & “
” & “
” & _
        “Thank you,” & “
” & _
        “Maudibe” & “
” & _
        ““

The image tag should point to the server folder that contains the image file and not a local folder on the computer where Citrix is being run
HTH,
Maud

Reply | Quote

Hi all,

Is there a way to set a keystroke combo that inserts a signature automatically?
I monitor several inboxes and am interested in setting up shortcuts like #ZZ and #YY to insert different signatures as needed.

Any input would be greatly appreciated.

Thanks

Reply | Quote

You can access appdata by opening Windows Explorer and entering this in the location box: %appdata%

cheers, Paul

Reply | Quote