Add outlook Signature using VBA when sending email.

mond007

New Member
Joined
Oct 9, 2008
Messages
34
Hi

I am using the below code in a MS Office 2000 environment.
Does anyone know how to set the email type to be HTML and also automatically attach the signiature from outlook.

I am using the following standard code to send.

Code:
    Set myOlApp = CreateObject("Outlook.Application")
    Set myMail = myOlApp.CreateItem(olMailItem)
          
    With myMail
        .Display
        .To = ("[EMAIL="test@testcompany.com"]test@testcompany.com[/EMAIL]") 
        .Subject = "test"
        .Body = "test body"
        .Attachments.Add ThisWorkbook.FullName
    End With

    'AppActivate (myMail)   
    SendKeys ("^~")

Thanks in advance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ericaindallas

New Member
Joined
Aug 20, 2009
Messages
45

ADVERTISEMENT

That would work, but it's the name of the .txt file of the signature that is different for everyone.

Thanks,
-Erica
 

Julia Jones

New Member
Joined
Mar 1, 2013
Messages
1
I had the same problem with needing it to work for different users. Below is the code I use to capture each user's signature

Dim OApp As Object, OMail As Object, signature As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.body
With OMail
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.body = "Add body text here" & vbNewLine & signature
'.Send
End With
Set OMail = Nothing
Set OApp = Nothing

That would work, but it's the name of the .txt file of the signature that is different for everyone.

Thanks,
-Erica
 

coltrane59

Board Regular
Joined
Aug 25, 2008
Messages
71

ADVERTISEMENT

I had the same problem with needing it to work for different users. Below is the code I use to capture each user's signature

Dim OApp As Object, OMail As Object, signature As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.body
With OMail
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.body = "Add body text here" & vbNewLine & signature
'.Send
End With
Set OMail = Nothing
Set OApp = Nothing

This was helpful for me. I wanted to note one modification though that improved the look of the signature. Since my signature has formatting, and includes a "Thanks, " on the line before the main signature, the above code didn't work as cleanly until I used OMail.HTMLBody instead of Omail.Body.

The HTMLBody keeps all formatting and enter spacing if you have an HTML signature in Outlook.
 

Scrappy666

Board Regular
Joined
Jul 6, 2012
Messages
215
I had the same problem with needing it to work for different users. Below is the code I use to capture each user's signature

Dim OApp As Object, OMail As Object, signature As String
Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
signature = OMail.body
With OMail
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.body = "Add body text here" & vbNewLine & signature
'.Send
End With
Set OMail = Nothing
Set OApp = Nothing

I know this is an old post, but I've been searching for hours for a simple solution to my email signature problem.
This worked like a charm, thank you!!
 

DemDigidiz

New Member
Joined
Sep 21, 2016
Messages
3
I know this is an old post, but I've been searching for hours for a simple solution to my email signature problem.
This worked like a charm, thank you!!

So, I was Upset I had to put these useless lines where you display the mail just to copy the signature.
Thus I found that there is a way which is simply more "slick" and uses a little less lines :)
just change:

This:

With OMail
.Display
End With
signature = OMail.body
With OMail
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.body = "Add body text here" & vbNewLine & signature
'.Send

To this:

With OMail
.Display
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.body = "Add body text here" & vbNewLine & .body
'.Send

of course, you can use .HTMLBody instead of .body if you are using HTML format.

If you know HTML, You can go as far as this:

With OMail
.Display
'.To = "someone@somedomain.com"
'.Subject = "Type your email subject here"
'.Attachments.Add
.HTMLbody = "******>" & "some html code containing your message" & .HTMLbody & "</body>"
'.Send

Also if you have a Variable holding your text, you could use:

.HTMLbody = "******>" & "<p>" & "%!a!%" & "</p>" & .HTMLbody & "</body>"
.HTMLbody = Replace(.HTMLBody, "%!a!%", YourVariableHoldingTheText)

Hope this helps further searchers (Such as I was)

Cheers!





 

DemDigidiz

New Member
Joined
Sep 21, 2016
Messages
3
Sorry for the BOLD, dunno what I did wrong :/

Seems I need to learn how to post things better...
The **** should be the html tag for body. at the end there should be a closing tag for the html body.

Sorry for any inconvenience...
 

Forum statistics

Threads
1,136,765
Messages
5,677,618
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top