Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Outlook / Programming VBA / January 2008

Tip: Looking for answers? Try searching our database.

Variables passed into HTML from VBA for a boilerplate template

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Nathan - 29 Jan 2008 14:38 GMT
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...
Nathan - 29 Jan 2008 14:52 GMT
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.
Sue Mosher [MVP-Outlook] - 29 Jan 2008 15:01 GMT
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

Signature

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

> All,
>
[quoted text clipped - 6 lines]
> hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
> my wheels...
Nathan - 29 Jan 2008 15:30 GMT
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'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:
>
[quoted text clipped - 11 lines]
> > hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
> > my wheels...
Sue Mosher [MVP-Outlook] - 29 Jan 2008 15:46 GMT
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")

Exactly.

Signature

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 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
[quoted text clipped - 8 lines]
>>     htmlText = Replace(htmlText, "%Field1%", "New Text")
>>     myMessage.HTMLBody = htmlText

>> > All,
>> >
[quoted text clipped - 6 lines]
>> > hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
>> > my wheels...
Nathan - 29 Jan 2008 16:25 GMT
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?

> In both cases, you need a string variable to contain the fully tagged HTML content.
>
[quoted text clipped - 32 lines]
> >> > hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
> >> > my wheels...
Sue Mosher [MVP-Outlook] - 29 Jan 2008 16:50 GMT
I don't see why not. It's all just text.

Signature

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,
>
[quoted text clipped - 39 lines]
>> >> > hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
>> >> > my wheels...
Nathan - 29 Jan 2008 15:36 GMT
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?

> All,
>
[quoted text clipped - 6 lines]
> hyplerlink.  Does anyone know wuick way to achieve this, I am just spinning
> my wheels...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.