• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1467
  • Last Modified:

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?

  • 4
  • 3
1 Solution
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 http://www.w3schools.com/tags/. 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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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, www.ladygrover.org.uk." & 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.
jeffkwells2003Author Commented:
Great stuff, thanks Scott. That did the trick!


Jeff Wells
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now