Solved: Send both html signature and body of email together [Archive]

View Full Version : Solved: Send both html signature and body of email together

IrishCharm

04-23-2008, 08:02 AM

Hi,

I have a string variable Body which is defined "eg Please see the attached" and a html signature which is saved down to my C Drive. Both need to be sent in the email but the text body is being either left out or blocked by the html signature. any ideas how to show both?

Cheers

Sarah

.Subject = Subject
.HTMLBody = Body & " " & Signature
.Importance = olImportanceHigh
.ReadReceiptRequested = False

IrishCharm

04-24-2008, 12:51 AM

Anyone any idea how to go about doing the above?

Cheers

Sarah

Bob Phillips

04-24-2008, 01:04 AM

Can you post a workbook, I for one cannot envisage the problem.

IrishCharm

04-24-2008, 02:55 AM

Hi,

I have attached the code which is run. Basically the file is opened, declared as the attachment and then closed. The email body is then declared and the signature (html) which is saved to C Drive is attached also to the email.

But the signature seems to be overwriting the body of text and i am unsure how to resolve this.

kind regards,

sarah

Function GetBoiler(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)
GetBoiler = ts.readall
ts.Close
End Function
Sub SendMessage()
Dim SigString As String
Dim Signature As String
Dim str_MsgBody As String

''Signature to send in Email
''==========================
SigString = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\MySig.htm"
Signature = GetBoiler(SigString)

If Dir(SigString) = "" Then
MsgBox "No Signature Saved to C Drive to send Emails"
End If

Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

str_MsgBody = "Please find the attached mail"
With objOutlookMsg

activecell = "Email Addresss"
Set objOutlookRecip = .Recipients.Add(ActiveCell)
'' Attach the spreadsheet to the Email
Workbooks.Open FileName, UpdateLinks:=False
Set WB = ActiveWorkbook
.Attachments.Add WB.FullName
Windows(str_reportname).Close , savechanges:=False

'' send the message and attachment!!!
.Subject = Subject
.HTMLBody = str_MsgBody & " " & Signature
.Importance = olImportanceHigh
.ReadReceiptRequested = False
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
If DisplayMsg Then
.Display
Else
.Send
End If
End If
End With
Set objOutlook = Nothing

End Sub

IrishCharm

04-25-2008, 03:56 AM

Hi,

Anyone have any suggestions re the above? any ideas would be helpful

Thanks

Sarah

rory

04-25-2008, 04:18 AM

Your code works for me (after removing the spare End If line).

IrishCharm

04-25-2008, 04:48 AM

Hi,

Sorry - i forgot to remove the last endif. The code works ok but if you see that it defines a message str_MsgBody and then a html signature (i.e. your own normal signature at bottom of emails sent which is saved in html format on your C Drive - gerboiler function finds this)

So when you're sending the email the content should be "please find attached... etc" and then underneath this is your signature.

But what is happening is taht the signature is being returned but it is not returning the str_MsgBody bit as well.

if you remove the signature and replace .htmlbody with .body you can see that the str_MsgBody returns as normal.

Any ideas on how to correct for this?

rory

04-25-2008, 04:54 AM

It worked for me exactly as intended, but I have just tried it with Outlook set to use Word as the email editor, and it behaved as you describe. If you have that set, then I guess the obvious answer is not to use Word?

IrishCharm

04-25-2008, 05:17 AM

What do you have Outlook set to use and how can i change it?

IrishCharm

04-25-2008, 05:21 AM

Is there a way do you know to send the mail without having to change that setting as microsoft word allows you to spell check etc emails

rory

04-25-2008, 05:42 AM

I think it's to do with the way Word handles the HTML signature. Try changing the .HTMLBody line to this:
.HTMLBody = Replace$(Signature, "<BODY>", "<BODY>" & str_MsgBody & " ")

IrishCharm

04-25-2008, 06:00 AM

Hi,

i tested that with the microsoft word switched on and it does not work for me its only works when i have word off.

rory

04-25-2008, 06:09 AM

I'm stumped then because that works for me both with and without Word as the email editor. What default format are your emails?

IrishCharm

04-25-2008, 06:33 AM

Hi,

I have attached the settings for my mail format in the attachment. I must have something ticked that is blocking the file from being correctly loaded. I have checked my outlook settings and re-run the macro but am still in limbo im afraid.

rory

04-25-2008, 06:51 AM

That's the same settings I was testing with, but I guess from that you are using Office 2003? I'm testing on Office XP so there may be a difference between the two there.

IrishCharm

04-25-2008, 06:55 AM

That would be it - my computer is windows xp but the version of microsoft is 2003. Anyone have any idea how to downgrade the above Replace$ formula? I think its going to be a very long evening for me!

Thanks for all your help Rory

Sarah

rory

04-25-2008, 07:40 AM

If no-one responds beforehand, I will try and test later on when I get home as I have Office 2003 there (hopefully it's not an Exchange Server issue, as I don't have one of those at home these days!)

rory

04-25-2008, 07:45 AM

In the meantime, this may help: http://www.outlookcode.com/codedetail.aspx?id=615

IrishCharm

04-25-2008, 07:51 AM

Thanks Rory i appreciate your help. Will check this link out and see if i can fix it up

sarah

IrishCharm

04-28-2008, 03:25 AM

Hi Rory,

Still having difficulty with this, if you were able to spot anything else

Sarah

IrishCharm

04-28-2008, 05:26 AM

Hi,

I have condensed the code to the below. Basically i want to return the body of the message and the html signature underneath this (signature saved as MySig to the C Drive) but the signature overwrites the message being returned with it. Any help would be great

cheers

sarah

Sub SendMessage()
Dim SigString As String
Dim Signature As String
Dim str_body As String
Dim fso As Object
Dim ts As Object
''This is where the html version of your personal signature needs to be saved
SigString = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\MySig.htm"
str_body = "HELLO WORLD"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
Signature = ts.ReadAll
ts.Close
Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("MY EMAIL ADDRESS")
.Subject = "TEST MAIL"
.HTMLBody = Replace(Signature, "", str_body & " " & "<body>" & Signature)

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
.Display

End With
End Sub

IrishCharm

04-28-2008, 07:18 AM

Hi,
I have amended the code to the below - this will bring back either the str_body or signature but not both. It is run on XlSversion03. Any ideas on how to return both would be great. The replace$ function works only on windows xp and not 03.

Sub SendMessage()

SigString = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\MySig.htm"
str_subject = "HELLO WORLD"
str_Body = "TEST MAIL"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
Signature = ts.readall
Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("MY EMAIL ADDRESS (SARAHANN.DUFF@BOIGM.COM)")

.subject = str_subject
.HTMLBody = str_Body & " " & Signature
' .HTMLBody = str_Body

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
.Display

End With

End Sub

rory

04-28-2008, 03:29 PM

This works for me in 2003:
Sub SendMessage()

SigString = "C:\Users\Kath\AppData\Roaming\Microsoft\Signatures\Test.htm"
str_subject = "HELLO WORLD"
str_Body = "TEST MAIL"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
Signature = ts.readall
Set objOutlook = CreateObject("outlook.application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add("MY EMAIL ADDRESS")

.Subject = str_subject
.HTMLBody = Signature
Debug.Print .HTMLBody
.HTMLBody = Replace$(.HTMLBody, "<BODY>", "<BODY>" & _
"<DIV align=left>" & str_Body & "</DIV> ")
' .HTMLBody = str_Body

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
.Display

End With

End Sub

Does it work for you?

IrishCharm

04-29-2008, 01:00 AM

Hi Rory,

This works if i untick the Microsoft Word settings in my Outlook but I cant get it to attach the str_body at the same time. Bizarre - there must be some little snag to get it to attach when outlook microsoft is ticked but i just cant see it!

rory

04-29-2008, 03:51 AM

That's odd, as I was using the same version of Office and with the same settings as you posted! It must be something to do with the signature, I guess. I left a Debug.Print line in the code - can you tell me what gets output to the Immediate Window?

IrishCharm

04-29-2008, 03:58 AM

Hi rory,

The below returns in my immediate window.

Sarah

<link rel=File-List href="Sarah_1_files/filelist.xml">
<title>Mairead Magill</title>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="country-region"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>

<style>

</style>

</head>
<body lang=EN-GB link=blue vlink="#606420" style='tab-interval:36.0pt'>
<div class=Section1>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
SD
<o:p> </o:p>
BO<st1:country-region w:st="on"><st1:place
w:st="on">IRL</st1:place></st1:country-region> Global Markets
CK House, <st1:address
w:st="on"><st1:Street w:st="on">T Street</st1:Street>, <st1:City w:st="on">Dublin</st1:City></st1:address>
1, <st1:country-region w:st="on"><st1:place w:st="on">Ireland</st1:place></st1:country-region>
T: +353<o:p></o:p>
E: SD@bORI.om (SD@bORI.com)
WWW.BORI
 
 
the t s?
 
P Please
consider the environment - do you really need to print this
email?
 
</div>
</body>
</html>

</BODY>
</HTML>

IrishCharm

04-29-2008, 05:57 AM

Hi Rory,

This does not seem to want to work at all!!! I was thinking if i saved the signature in microsoft word format do you know of a way to attach this as a signature? It may be a lot easier seeing as the issue lies behind the ticked word box in outlook

sarah

rory

04-29-2008, 06:13 AM

Sarah,
If your email is in HTML format then you need an HTML sig. The Signatures are not exposed in the Outlook object model unfortunately, though the link I posted before shows a way to programmatically activate the Insert-Signature menu command.
Can you post the actual htm signature file so I can test with that later?

IrishCharm

04-29-2008, 06:47 AM

Hi Rory,

Think i may have found something - the below is bringing back both the body and the signature but instead of the actuial body returning its bringing back the word str_body.

str_body = "TEST MAIL"
.htmlBody = "<html><body> str_body, " & Signature

Sarah,
If your email is in HTML format then you need an HTML sig. The Signatures are not exposed in the Outlook object model unfortunately, though the link I posted before shows a way to programmatically activate the Insert-Signature menu command.
Can you post the actual htm signature file so I can test with that later?

rory

04-29-2008, 07:24 AM

Try:
.htmlBody = "<html><body>" & str_body & " " & Signature

IrishCharm

04-29-2008, 07:26 AM

Next time you're in Dublin I owe you a keg of guinness :-)

Thanks for all your patience and help with this, i know i drove you crazy but it was SO important that i fixed this up.

Thanks again for your help

Kind regards,

Sarah

rory

04-29-2008, 07:55 AM

Hey - you figured out the solution! :)
I'll settle for a pint in the Gravity Bar next time I'm over!

jeffclanders

08-11-2016, 04:54 AM

Try:
.htmlBody = "<html><body>" & str_body & " " & Signature

Hi Rory, or anyone else. I hope all is well. I know this post is older, but most of what is in here is relevant for me. I have used your code in combination with other code to include information from an excel spreadsheet in the body of my email. Using your code, I am able to get the body to display and well as my signature, however, the image that I included form my signature does not display. This is simply the logo of my company, but it just shows a blank box with an error stating that this image cannot be displayed. Right now, I am streamlining processes and this is working great, with the exception of the image. My bigeest goal was to not have outlook give me the message regarding sending an email from an outside source, which is currently does not do. I am trying to avoid more clicks. At any rate, I am using Office 2013 and I am posting the code below. As I stated, the last remaining hurdle is being able to show the actual image of the signature line. If in the code below, I simply add a "." to the Signature so that it is "& .Signature", I get my full signature line with image without body text from spreadsheet. However, if I use the code without the "." I get the body text from the spredsheet as well as the signature line without image. Any help to add the image woul be much appreciated.

Code:

Sub SendExcelNoSavedFile()
' Save Excel File and send as attachment.

Dim strPath As String, strFName As String, strFName2 As String
Dim OutApp As Object, OutMail As Object, SigString As String, Signature As String, strbody As String, fso As Object, _
ts As Object

strPath = Environ$("temp") & "\" 'Or any other path, but include trailing "\"

strFName = "CNY " & "- INV " & ActiveSheet.Range("E24").Value & " - " & ActiveSheet.Range(" E8").Value & " - " & ActiveSheet.Range(" M8").Value
strFName2 = "CNY " & "- INV " & ActiveSheet.Range("E24").Value & " - " & ActiveSheet.Range(" E8").Value & " - " & ActiveSheet.Range(" M8").Value

ActiveWorkbook.SaveAs FileName:=strPath & strFName & ".xlsm", FileFormat:= _
52, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

SigString = Environ("appdata") & "\Microsoft\Signatures\Jeff Landers - Charter.htm"
'Set up outlook

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(SigString).openastextstream(1, -2)
Signature = ts.ReadAll
ts.Close

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With ActiveWorkbook.Sheets("Production Report")
strbody = "Approved Production Report Attached." & " " & _
.Range("D8") & " " & .Range("E8") & " " & _
.Range("I14") & " " & .Range("K14") & " " & _
.Range("I15") & " " & .Range("K15") & " " & _
.Range("D24") & " " & .Range("E24") & " " & _
.Range("K25") & " " & .Range("L25")
End With

'Create message
On Error Resume Next
With OutMail
.To = ActiveSheet.Range("U8") 'Insert required address here ########
.CC = ""
.BCC = ""
.Subject = strFName2
.HTMLBody = "<html><body>" & strbody & " " & Signature
.Attachments.Add ActiveWorkbook.FullName
.Display 'Use only during debugging ##############################
'.Send 'Uncomment to send e-mail ##############################
End With
'Delete any temp files created
Kill strPath & strFName

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub