Variables passed into HTML from VBA for a boilerplate template

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

All,

I have a complex HTML e-mail message that I want to be able to customize
using variable in VBA. I don't want to have to go through the entire HTML
maessage and include in the the .htmlbody when creating a new message. I
would perfer to somehow pass variables that are set via an input box and then
pass them into the message in a template? Some variables are just text in
the message with HTML formatting and some variables are text+part of a
hyplerlink. Does anyone know wuick way to achieve this, I am just spinning
my wheels...
 
Also let me post some code snippetts that I have:


Sub AMS360_Online_DBExtraction()

Dim myOLApp As New Outlook.Application
Dim myOLItem As Outlook.MailItem
Dim txtToAddress As String
Dim txtOrderNumber As String
Dim txtAgency As String
Dim txtQID As String


txtToAddress = InputBox("Please insert in a To: email address", "To:")
txtOrderNumber = InputBox("Please insert in an order number.", "Order Number")
txtQID = InputBox("Please insert in the agency id.", "Agency QID")
txtAgency = InputBox("Please insert in the agency name.", "Agency Name")


Set myOLItem = myOLApp.CreateItemFromTemplate("U:\Outlook
Templates\360_DBExtraction.oft")



With myOLItem
..SentOnBehalfOfName = "CLL - AFW/AMS 360 Support"
..To = Trim(txtToAddress)
..BCC = "CLL - AFW-Online Data Center"
..Subject = "RE: AMS360 Backup Completed | " & Trim(txtOrderNumber) & " | " &
Trim(txtAgency) & " | " & Trim(txtQID) & " | AMS360 Backup"



End With

myOLItem.Display

End Sub

I considered doing everything through the .HTMLBody method, but the HTML
code is essentially word "trash" code and I didn't want to have to clean it
all up if need be. What I would really like to do is define 4 ro so fields
as variables in the oft file that can be set via an input box variable upon
launch of the code...

I am not sure if that helps narrow it down somewhat, but hopefully it should.
 
I've done this by using placeholders in the HTML where the data from the InputBox result should go. For example, put %Field1% where you want one bit of data to appear, then use something like:

htmlText = Replace(htmlText, "%Field1%", "New Text")
myMessage.HTMLBody = htmlText
 
Sue, Thanks for you quick response! Using this method how would I get VBA
to do a find through either the .oft file or through an HTM/HTML file to find
the field and replace with the inputbox variable? Also can I define a full
file and designate that as a variable that I can then set the .htmlbody
method to?


Nathan Bell
 
I am assuming like this:

Set itm = Application.CreateItemFromTemplate("C:\myfile.oft")
itm.HTMLBody = Replace(itm.HTMLBody, "%name%", "customer_name")



I found it in an old forum post of yours off of lockergnome, is that correct?
 
In both cases, you need a string variable to contain the fully tagged HTML content.

..oft file -- load it with Application.CreateItemFromTemplate,then get its HTMLBody property

..txt file -- read the text into a variable with FileSystemObject methods; see http://msdn.microsoft.com/library/en-us/script56/html/jsfsotutor.asp
I am assuming like this:

Set itm = Application.CreateItemFromTemplate("C:\myfile.oft")
itm.HTMLBody = Replace(itm.HTMLBody, "%name%", "customer_name")
Click to expand...

Exactly.

--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54
 
Sue,

Thanks again for you help, that worked as intended, only one last snag, that
I don't think I can get around.....I am dynamically trying to change a
tracking order link using a place holder in the HTML tag itself, can this be
done easily using teh same described method?
 
Back
Top