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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

ericaindallas

New Member
Joined
Aug 20, 2009
Messages
45
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
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,099,075
Messages
5,466,468
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top