QA, I've worked with VBA/Outlook before, and with HTML it's a bit of a pain but it absolutely can be done. In fact, maybe you'll discover some things that make it less of a pain that it is for me—that is, maybe I've been doing it the hard way.
The general principle comes in two parts: a) There's an Outlook Application object just as there is an Excel Application object, and b) you can use either one in any VBA program regardless of whether that program is running in Excel or Outlook. (Actually you don't even have to be running in any Office app; you can use those objects just as well in VBScript. That's handy, for it means you can double-click on an icon in Windows and have it do all the work without even having to be in Excel-or-whatever.)
But here's where the pain comes in: If you've learned to write Excel macros, you'll probably remember a time when you had to struggle to understand what the Excel objects do. Not all those objects were intuitively obvious to you the first time you tried work with them. The same is true of Outlook; it's not like Excel at all, so even though the language is the same, the behavior of Outlook objects isn't easy to learn—or at least it hasn't been for me, so far.
Here's a snippet of code I wrote in MS Access to set up an HTML email and then display it for me to modify and send. I've added comments in hopes of making the broad outline easier to follow:
Code:
Set ol = CreateObject("Outlook.Application") 'establishes the main Outlook object
Set eml = ol.CreateItem(0) 'starts a new email (I think 0=olMailItem, the type)
' I've already got the To and CC addresses set up, and the subject comes from a form.
eml.To = EmlsTo
eml.CC = EmlsCC
If rsSubj!Text <> "" Then eml.Subject = rsSubj!Text
eml.Display 'makes the email visible to the user (that's me)
' The email starts out blank with just my signature. But it only
' looks blank; there's really a lot of HTML code in there. Here's
' where I try to find the right point at which to insert my text:
Const StartPoint = "<div class=Section1>"
pi = InStr(eml.HTMLBody, StartPoint)
If pi = 0 Then
' some sort of error here; that HTML tag was supposed to be there
Else
pi = pi + Len(StartPoint)
End If
pi = pi + 2 'to account for the CrLf
' So pi is now the character position of the text to be inserted. Now break
' up the text that's already there into Before and After:
Dim BodyLeft, BodyMid, BodyRight
BodyLeft = Left(eml.HTMLBody, pi - 1)
BodyRight = Mid(eml.HTMLBody, pi)
' Assemble the text to be inserted. Include the FAQ if the checkbox says so.
BodyMid = Sals & rsBody!Text
If rsBody!NclFaq Then BodyMid = BodyMid & _
vbCrLf & vbCrLf & DLookup("Text", "Text", "Key='faq'")
BodyMid = HTMLParagraph(BodyMid) 'turn plain text into HTML
eml.HTMLBody = BodyLeft & BodyMid & BodyRight 'reassemble the message body
' Attach resume to a cold email
If rsBody!NclResume Then eml.Attachments.Add _
CurrentProject.Path & "\RHBridges.doc", olByValue
eml.GetInspector.Activate ' bring the email window to the fore so the user can inspect and modify
Right in the middle there's a call to a routine I named HTMLParagraph; it takes plain text and puts HTML code around it. Maybe the details don't matter to you, but just in case, it looks like this:
Code:
' Wrap the necessary HTML code around a block of text.
Function HTMLParagraph(txt) As String
Const ParA = vbCrLf & "<p class=Bob>"
Const ParZ = " </p>" & vbCrLf
' First replace any CrLfs of the incoming plain text with blank HTML paragraphs
pb = InStrRev(txt, vbCrLf)
Do While pb > 0
txt = Left(txt, pb - 1) & ParZ & ParA & Mid(txt, pb + 2)
pb = InStrRev(txt, vbCrLf, pb)
Loop
HTMLParagraph = ParA & txt & ParZ
End Function
Now, all this looks like a lot of work to me. Maybe to someone who automates VBA a lot, it doesn't seem so—or maybe such a person would sneer at my code, telling me there are much easier ways to do it. In any case, all this is supposed to demonstrate that yes, you absolutely can do it, and you don't even have to leave Excel to make it work. But you'll probably have to spend a lot of time experimenting and swearing at it before it actually does everything you want to. Either that or maybe you'll find a VBA/Outlook jock at your workplace who'll make it all go a lot easier for you.