PDA

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 & "<br><br>" & 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 & "<br><br>" & 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 & "<BR><BR>")

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 & "<BR><BR>" & "<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 & "<br><br>" & 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><br><br>")
' .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"/>
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>duffsa</o:Author>
<o:Template>Normal</o:Template>
<o:LastAuthor>duffsa</o:LastAuthor>
<o:Revision>3</o:Revision>
<o:TotalTime>0</o:TotalTime>
<o:Created>2008-04-17T10:09:00Z</o:Created>
<o:LastSaved>2008-04-17T11:46:00Z</o:LastSaved>
<o:Pages>1</o:Pages>
<o:Words>42</o:Words>
<o:Characters>241</o:Characters>
<o:Company>Bank of Ireland</o:Company>
<o:Lines>2</o:Lines>
<o:Paragraphs>1</o:Paragraphs>
<o:CharactersWithSpaces>282</o:CharactersWithSpaces>
<o:Version>11.6568</o:Version>
</o:DocumentProperties>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:SpellingState>Clean</w:SpellingState>
<w:GrammarState>Clean</w:GrammarState>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" LatentStyleCount="156">
</w:LatentStyles>
</xml><![endif]--><!--[if !mso]><object
classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object>
<style>
st1\:*{behavior:url(#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:536871559 0 0 0 415 0;}
@font-face
{font-family:Webdings;
panose-1:5 3 1 2 1 5 9 6 7 3;
mso-font-charset:2;
mso-generic-font-family:roman;
mso-font-pitch:variable;
mso-font-signature:0 268435456 0 0 -2147483648 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0cm;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;
text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
{color:#606420;
text-decoration:underline;
text-underline:single;}
span.SpellE
{mso-style-name:"";
mso-spl-e:yes;}
span.GramE
{mso-style-name:"";
mso-gram-e:yes;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;
mso-header-margin:35.4pt;
mso-footer-margin:35.4pt;
mso-paper-source:0;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style>
<![endif]-->
</head>
<body lang=EN-GB link=blue vlink="#606420" style='tab-interval:36.0pt'>
<div class=Section1>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'>SD</span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:#00436E;
mso-ansi-language:EN-US'><o:p>&nbsp;</o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'>BO<st1:country-region w:st="on"><st1:place
w:st="on">IRL</st1:place></st1:country-region> Global Markets</span></p>
<p class=MsoNormal><span class=SpellE><span lang=EN-US style='font-family:Verdana;
color:gray;mso-ansi-language:EN-US'>CK</span></span><span lang=EN-US
style='font-family:Verdana;color:gray;mso-ansi-language:EN-US'> 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></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'>T: +353<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='font-family:Verdana;color:gray;
mso-ansi-language:EN-US'>E: SD@bORI.om (SD@bORI.com)</span></p>
<p class=MsoNormal><span class=SpellE><span lang=EN-US style='font-family:Verdana;
color:gray;mso-ansi-language:EN-US'>WWW.BORI</span></span></p>
<p class=MsoNormal>&nbsp;</p>
<p class=MsoNormal>&nbsp;</p>
<p class=MsoNormal><span class=GramE><span lang=EN-IE style='font-size:13.0pt;
font-family:Verdana;color:#264677;mso-ansi-language:EN-IE'>the</span></span><span
lang=EN-IE style='font-size:13.0pt;font-family:Verdana;color:#264677;
mso-ansi-language:EN-IE'> t <strong><span style='font-family:Verdana'>s</span></strong></span><sup><span
lang=EN-US style='font-size:13.0pt;font-family:Verdana;color:#264677;
mso-ansi-language:EN-US'>?</span></sup></p>
<p class=MsoNormal>&nbsp;</p>
<p class=MsoNormal><span lang=EN-IE style='font-size:18.0pt;font-family:Webdings;
color:green;mso-ansi-language:EN-IE'>P</span><b><span lang=EN-IE
style='font-size:9.0pt;color:green;mso-ansi-language:EN-IE'> </span></b><span
class=GramE><span lang=EN-IE style='font-size:8.0pt;font-family:Arial;
color:green;mso-ansi-language:EN-IE'>Please</span></span><span lang=EN-IE
style='font-size:8.0pt;font-family:Arial;color:green;mso-ansi-language:EN-IE'>
consider the environment - do you <b><i>really</i></b> need to print this
email?</span></p>
<p class=MsoNormal>&nbsp;</p>
</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, <br><br>" & 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 & "<br><br>" & 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 & "<br><br>" & 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." & "<br><br>" & _
.Range("D8") & " " & .Range("E8") & "<br>" & _
.Range("I14") & " " & .Range("K14") & "<br>" & _
.Range("I15") & " " & .Range("K15") & "<br>" & _
.Range("D24") & " " & .Range("E24") & "<br>" & _
.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 & "<br><br>" & 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