[AccessD] Outlook automation - use a template for a message

Doug Murphy dw-murphy at cox.net
Wed Jan 25 12:43:40 CST 2006


Erwin,

That is great.  Thank you!  I'll try it out.

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Erwin Craps - IT
Helps
Sent: Wednesday, January 25, 2006 3:18 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Outlook automation - use a template for a message


The problem lies in the CreateItemFromTemplate.
I see after creating the item from template it also adds my regular footer
above the template. This is probably why your replacefields get at the
bottom. In some occasions I simulated the problem, it also messes up the
format of my template.

I use a different approach using a HTML template, may I suggest you to use
this one? The advantage is that you can use regular HTML editors, like
Frontpage, to create your template and you have access to both the design
and HTML source. For what concerns stiff resistance against HTML, I find
that a discussion like between using DOS and Windows. RTF is from a security
point of view not that more secure and is outdated by HTML. Plain text
e-mails is for what concerns me should dead a long time ago pointless these
days. If you want to present something is a decent fashion, you gonna need
formatting and hyperlinks. I am however against adding pictures (files) in
mass E-mails to minimize size. I always use hyperlinks to my website where
the pictures reside. As in the example I give you. Further more its better
for statistics because I see in my weblog how many users have opened my mail
or even forwarded it to someone else (depending on the situation).


To make this code work you need to download the HTML template and save in
into your C:\temp folder. http://www.ithelps.be/temp/SampleVideo2.htm

To view the result after running the code see
http://www.ithelps.be/temp/NewVideo.msg


Beneath is the code I trimmed from my e-mail marketing function. You can
also download it from http://www.ithelps.be/temp/TestHTMLBodyEmail.txt

Small note:
I use [[fieldtoreplace]] brackets instead of <fieldtoreplace>. <> get's
replaced by &something in HTML code. I also find that [[]] is more unique
than <> to avoid replacement problems.


Public Function TestHTMLBody()
    Dim strPromoTemplatePathAndFile  As String
    Dim strHTMLPromoTemplate As String
    Dim strHTMLPromoTemplateTEMP As String
    Dim lngLOF As Long
    Dim strFind As String
    Dim strReplace As String
    Dim strMailSubject As String
    Dim oOut As Outlook.Application
    Dim mItem As Outlook.MailItem
    
    'Path and filename to HTML template
    'Locate at your server so you only have one master file for all users.
    strPromoTemplatePathAndFile = "C:\temp\SampleVideo2.htm"
    strMailSubject = "Some mail subject"
    
    'Open templatefile and load HTML code in a variable
    Open strPromoTemplatePathAndFile For Binary As #1
    lngLOF = LOF(1)
    strHTMLPromoTemplate = Space(lngLOF)
    Get #1, 1, strHTMLPromoTemplate
    Close #1
    
    'Copy HTML code in temporary variable
    strHTMLPromoTemplateTEMP = strHTMLPromoTemplate
    
    'Replace fields in HTML with real data
    strFind = "[[NAME]]"
    strReplace = "Craps"
    strHTMLPromoTemplateTEMP = Replace(strHTMLPromoTemplateTEMP, strFind,
strReplace, 1, , vbDatabaseCompare)
                
    strFind = "[[DATE]]"
    strReplace = Date
    strHTMLPromoTemplateTEMP = Replace(strHTMLPromoTemplateTEMP, strFind,
strReplace, 1, , vbDatabaseCompare)
    
    strFind = "[[FIRSTNAME]]"
    strReplace = "Erwin"
    strHTMLPromoTemplateTEMP = Replace(strHTMLPromoTemplateTEMP, strFind,
strReplace, 1, , vbDatabaseCompare)
    
                
                
    'create new e-mailitem and display without sending
    Set oOut = New Outlook.Application
    Set mItem = oOut.CreateItem(olMailItem)
    With mItem
        .To = "dw-murphy at cox.net"
        .Subject = "New Video"
        .HTMLBody = strHTMLPromoTemplateTEMP
        .Display
    End With
    
    Set mItem = Nothing
    Set oOut = Nothing
    
    
End Function
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list