Insert default signature .htm file in email

[jerry12302]

I have VBA code to send emails, but they do not include the default signature which contains my company logo.

...
EMTo = Application.WorksheetFunction.Index(Range("CompData"), Counter, 3)
EMSubject = Range("EMSubject").Value
EMBody = "Test email, to include signature" & vbLf & vbLf

With outMail
.To = EMTo
.Subject = EMSubject
.Body = EMBody 'need to modify to include signature
.Send
End With
...

I have seen other posts about this, but I know the path and file name of the default signature, it doesn't have to be searched for, and shouldn't have to be converted. It's an htm file with the text and the logo:

C:\Users\Jerry\AppData\Roaming\Microsoft\Signatures\Jerry.htm

Is there a simple way to modify the email body to add this signature file?

 

[Dan_W]

So you're saying that when you go to write a new email normally, that the signature (with logo) is already in the draft by default?
If that is the case, I suspect what is happening is that the signature is in fact there at the outset when the email is created, but then is immediately written over by your code above. The solution then is to store the signature HTML code in a variable, and then append that code to the new email body that your code above introduces:

EMTo = Application.WorksheetFunction.Index(Range("CompData"), Counter, 3)
EMSubject = Range("EMSubject").Value
EMBody = "Test email, to include signature" & vbLf & vbLf

Dim Signature As String
Signature = outMail.HTMLBody

With outMail
.To = EMTo
.Subject = EMSubject
.HTMLBody = EMBody & Signature
.Send
End With

Finally, you may want to change the line .Send to .Display while your testing the code until you're happy that it's working how you want it to.

 

[jerry12302]

So you're saying that when you go to write a new email normally, that the signature (with logo) is already in the draft by default?
If that is the case, I suspect what is happening is that the signature is in fact there at the outset when the email is created, but then is immediately written over by your code above. The solution then is to store the signature HTML code in a variable, and then append that code to the new email body that your code above introduces:

EMTo = Application.WorksheetFunction.Index(Range("CompData"), Counter, 3)
EMSubject = Range("EMSubject").Value
EMBody = "Test email, to include signature" & vbLf & vbLf

Dim Signature As String
Signature = outMail.HTMLBody

With outMail
.To = EMTo
.Subject = EMSubject
.HTMLBody = EMBody & Signature
.Send
End With

Finally, you may want to change the line .Send to .Display while your testing the code until you're happy that it's working how you want it to.

Thanks for the reply Dan, but that did not work. Neither the text portion nor the Logo of the signature appeared in the email, and it changed the font and font size of the email body.

There was a post I read that said something about not being able to concatenate HTML, but there was a way to use a separate routine, I didn't really follow it.

I am open to any other suggestions you may have.

Jerry

 

[daverunt]

Have a look here at Ron De Bruins page

Insert Outlook Signature in mail

 

[Dan_W]

ADVERTISEMENT

it changed the font and font size of the email body

This bit I actually knew would happen, but I just wanted to see how the signature insertion went before worrying about formatting.

I take the point about concatenation of HTML code, but that isn't really what's happening here - the text in EMBody is not HTML code. Which leads into @daverunt's (excellent) suggestion of seeing how RDB says to do it. But looking at Example 1, this is essentially what my approach above does. Which is unsurprisingly because Ron De Bruin's site is probably where I originally learnt it. The key difference I think is that in his example, his equivalent EMBody is in HTML code. (He doesn't store the signature code in a variable either, but that's not a material difference.)

HTML Code is extremely flexible (arguably, to a fault) and the fact that the text in EMBody is not wrapped in HTML tags shouldn't result in neither the text nor the logo appearing. One quick question - if neither appeared, how do you know that the font/font size had changed? The email body should've been empty. Did you type something into the body section to see why it wasn't working?

What happens if you comment out or delete the line that sets the content of the email body? The line starting .HTMLBody = ... what is displayed in the email? In theory, it should just be the normal default email, so you should see just the signature, no?

 

[jerry12302]

Daverunt, and Dan,

It has taken me a while, but I have been modifying my code and running tests, to be consistent with the first code set on the site from Daverunt's link.

My code is finally generating the desired email body and also has the complete signature, with the logo, so thank you for that.

There is still a minor issue with the font, maybe both of you could give me a clue. The first line of the email body is displaying as Times New Roman, 12 point. The rest of the body is Calibri, 10. When I manually type an email the default is Calibri 11, which is also true for my signature. I'm not sure why the body uses different fonts when generated by the VBA code.

Any ideas?

 

[jerry12302]

ADVERTISEMENT

Update, this is weird, after reviewing some other code snipets, here is my code for the email body:

strbody = "<font size=""11"" face=""Calibri"">" & _
Range("Greet").Value & EMName & "," & _
"<br><br>" & _
Range("EMBody").Value & _
"<br><br>" & _
Range("EMSigned").Value

When I run it I get a consistent font throughout the email body, and it is Calibri, but the size is 36, not 11.

If I use 1 instead of 11 I get a font size of 7.5; 2 gives me 10; both 2 and 3 gives me 10; 4 gives me 13.5; greater increments produce greater non-matching font sizes. I can't seem to get to 11.

Any ideas?

 

[jerry12302]

Closer....

This code (first line) using a size of 14 gave me an actual size of 10.5, and 15 gave me an actual size of 11.5. I'm going with 15 for 11.5 for now, but I am really curious what is going on here.

strbody = "<p style='font-family:calibri;font-size:15'/p>" & _
Range("Greet").Value & EMName & "," & _
"<br><br>" & _
Range("EMBody").Value & _
"<br><br>" & _
Range("EMSigned").Value

With OutMail
.Display
.To = EMTo
If EMCC > "" Then .CC = EMCC
.BCC = ""
.Subject = EMSubject
.HTMLBody = strbody & "<br>" & .HTMLBody
.Display 'Send or Display
End With

 

[daverunt]

I think Outlook assumes points as the font size when you just use a figure. You have to add 'px' ie '15px'. It then converts px to points for sending.
As a rough rule 1pt =1.33px

https://support.exclaimer.com/hc/en-gb/articles/360023360431-Pixel-to-Point-conversions

There is a more accurate calculation in one of my other responses to an email question some time ago, which I cannot find and I have forgotten the figures.

Not sure why you are seeing 36 when you use font size 11. There must be some error there. Check through the html code of your message when sent to yourself to see if you notice anything odd. Note: your pixel font size will be converted to points in the code.

 

[daverunt]

Found it.

Convert Pixels to Points

I have a need to convert Pixels to Points in C#. I've seen some complicated explanations about the topic, but can't seem to locate a simple formula. Let's assume a standard 96dpi, how do I calulate...

stackoverflow.com

Points=pixels *72 /96