Adding text to an Outlook mail item created from a template in VBA

I use VBA to send emails from an Access 2010 database. I am trying to create a new Outlook item from a .OFT template file which contains a company logo plus Twitter and Facebook logos.  I use the follwoing code:

Public Function SendEMail(emailaddress As String, subjectline As String, bodytext As String)
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Set MyOutlook = CreateObject("Outlook.Application")
    Set MyMail = MyOutlook.CreateItemFromTemplate("C:\Users\jeffkwells\Data\Access2010\OA\LadyGrover\LadyGSigBlock.oft")

MyMail.To = emailaddress

MyMail.Subject = subjectline

MyMail.Body = bodytext & Chr(13) & Chr(10) & Chr(13) & Chr(10) & MyMail.Body


Set MyMail = Nothing

Set MyOutlook = Nothing
End Function


This VBA code removes all of the logo images and leaves plain text. If I remove the "MyMail.Body = " line, the original .OFT file with the images is displayed correctly as an Outlook email. How do I add text (at the beginning of the email) without losing the images?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try using HTMLBody instead:

MyMail.HTMLBody = bodytext & Chr(13) & Chr(10) & Chr(13) & Chr(10) & MyMail.HTMLBody

Also, the line above contains a variable named "bodytext", but I don't see that in your code snippet above. This might be set somewhere else, of course, but it's not apparent in the information you gave.
jeffkwells2003Author Commented:
Yes, this is just the final output function. All the parameters are set elsewhere.  Thanks for your suggestion, however it only works partially. My "bodytext" variable contains text which is roughly formatted using chr(13) & chr(10) as line spacing. All this is lost when using HTMLBody. How do I retain this and also retain the images?


Jeff Wells
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to format "bodytext" to use HTML protocols. For example, you'd use <br> instead of chr(13) and chr(10), or perhaps <p> to indicate a paragraph.

There are hundreds of sites that show the various tags you can use - for example I'd suggest you stick with very basic items, like <br> and <p>.

You'd also need to insure that the final value in HTMLBody is a valid "document" for use in your email. At the very least, that would mean you'd have <HTML> and <BODY> tags (and be sure to include the ending tags as well: </HTML> and </BODY>). If the existing HTMLBody from the Template has those tags, you'll need to make sure the resultant HTMLBody that you ultimately create doesn't contain duplicates, otherwise you'll end up with an oddly formed message body.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jeffkwells2003Author Commented:
Mmmm....... easier said than done! How would I format this lot so that Bodytext contains entirely valid HTML? Isn't there any other way?

SALUTATION = "Dear " & Trim(Forms!FormMainUpdate.TITLE) & " " & Trim(Forms!FormMainUpdate.SURNAME) & Chr(13) & Chr(10) & Chr(13) & Chr(10)
 bodytext = "Membership Number: " & Forms!FormMainUpdate.REF_NO & Chr(13) & Chr(10) & Chr(13) & Chr(10) & SALUTATION & "Thank you for your application to become a member of Lady Grover's Fund, and for your Direct Debit form.  I would be grateful for a cheque for £" & Trim(Str$(subs * 0.5)) & " to cover your subscription for the second half of this year. If you would prefer to pay by Credit Transfer, our account is 00480144 at sort code 301175.  Please use " & Trim(Forms!FormMainUpdate.SURNAME) & " " & Forms!FormMainUpdate.REF_NO & " as the reference." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
 bodytext = bodytext & "I have activated your Direct Debit; your account will be debited " & "£" & Trim(Str$(subs)) & " on " & Format(CDate("01/01/" & Year(Now()) + 1), "dd mmmm yyyy") & " and annually on that date thereafter.  " & Chr(13) & Chr(10) & Chr(13) & Chr(10)
 bodytext = bodytext & "Your membership dates from " & Format(Trim(Forms!FormMainUpdate.JOIN_DATE), "dd mmmm yyyy") & "; you will be entitled to claim on the Fund from  " & Format(DateAdd("m", 6, Trim(Forms!FormMainUpdate.JOIN_DATE)), "dd mmmm yyyy") & ", 6 months after your membership starts." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
 bodytext = bodytext & "You will find a list of the benefits which accrue to members' families, the Rules of the Fund and a copy of our latest newsletter on our website," & Chr(13) & Chr(10) & Chr(13) & Chr(10)
 bodytext = bodytext & "Welcome to membership of Lady Grover's Fund. If you need any further information, please don't hesitate to get in touch by email, phone or letter." & Chr(13) & Chr(10) & Chr(13) & Chr(10)



Jeff Wells
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Not really ... HTML expects HTML tags, and has no idea what Chr(13) means (that's a VBA method, not HTML).

From what I see, you only need to change those Chr(13) & Chr(10) to <br> tags. You don't really do anything else in terms of formatting. In fact, using the builtin replace function you could simply replace:

Chr(13) & Chr(10) & Chr(13) & Chr(10)



and that should just about do it. Of course, you'd need to actually include those tags in the bodytext, instead of formatting that text, so you'd have to remove the quotes around the Chr(13) & Chr(10) & Chr(13) & Chr(10) lines. For example, the Salutation line would become:

SALUTATION = "Dear " & Trim(Forms!FormMainUpdate.TITLE) & " " & Trim(Forms!FormMainUpdate.SURNAME) & "<br><br>"
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As far as making sure there is only one set of <HTML><BODY> tags, just grab the HTMLBody from the mail message and use the Replace function to remove them:

Dim htmlbody As String

htmlbody = MyMail.HTMLBody

htmlbody = Replace("<HTML>", "")
htmlbody = Replace("<BODY>", "")

Now use the htmlbody variable with your bodytext variable:

htmlbody = "<HTML><BODY>" & bodytext & "<br><br>" & htmlbody

You may need to tweak this to get it just right, but essentially that's the way to manage it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeffkwells2003Author Commented:
Great stuff, thanks Scott. That did the trick!


Jeff Wells
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.