Replace Function Statements Subverting Automated E-mail

[Soule]

Hi, Everyone,



I'm trying to send an automated e-mail from Access through Outlook with a form button click.

My environment is:
- Access 2007
- .accdb file
- Button code in standard module (for debugging; will move to form class mod. when works)
- Am in datasheet view when compiling/stepping through/running
- Early binding w/Intellisense
- Libraries (in this order):
VB for Apps
MS Access 12.0 Obj. Lib.
MS Outlook 12.0 Obj. Lib.
MS OFC 12.0 Access db engine Obj. Lib.
MS VB for Apps Extensibility 5.3
MS VBScript Regular Expressions 5.5
MS ActiveX Data Objects 2.8 Lib.
OLE Automation
- No compile, step-through or run-time errors

The code below seems to be messing up the button automated HTML mail I send using an HTML template. The mail passes through the MS Exchange server and populates my "Subject" line okay, but the body of the message has none of the body of the template. The only text on the message body is inexplicably a form reference in my code!

' This section populates the <<KnownAs>> text greeting in the e-mail template with the "KnownAs" data in the
' record. If the data in the record is null (blank), "FirstName" data in record populates instead.
OlMsg.BodyFormat = olFormatHTML
OlMsg.HTMLBody = Replace("KnownAs", "KnownAs", "Forms![A1S1 Onboarding Tracking Form].[KnownAs]")
If IsNull("Forms![A1S1 Onboarding Tracking Form].[KnownAs]") Then
OlMsg.HTMLBody = Replace("KnownAs", "KnownAs", "Forms![A1S1 Onboarding Tracking Form].[FirstName]")
End If
' Debug.Print "Choose/populate e-mail greeting name", Err.Number, Err.Description
            
' Body section is almost all boilerplate (populates (3) HR EOD contact infos and movie code).
OlMsg.HTMLBody = Replace("HREODContactName", "HREODContactName", "Forms![A1S1 Onboarding Tracking Form].[HREODContactName]")
OlMsg.HTMLBody = Replace("HREODContactInternalPhone", "HREODContactInternalPhone", "Forms![A1S1 Onboarding Tracking Form].[HREODContactInternalPhone]")
OlMsg.HTMLBody = Replace("HREODContactInternalEMail", "HREODContactInternalEMail", "Forms![A1S1 Onboarding Tracking Form].[HREODContactInternalEMail]")
OlMsg.HTMLBody = Replace("MovieCode", "MovieCode", "Forms![A1S1 Onboarding Tracking Form].[MovieCode]")
' Debug.Print "Populate e-mail (3) contact infos & movie code", Err.Number, Err.Description

I noticed that form reference was the only control on my form that wasn't a text box (was a combo), so I changed it to a text box and still got the same mail result.

I added semi-colons to the end of every Replace function statements and got "end of statement" compile errors.

** When I take out all the Replace function statements from my code, the message is received looking fine, just like the template.

Can anyone notice something in this code that would cause it to circumvent a template's body and not populate the texts as it should?

Thanks for taking the time to read this. The shortest, smallest advices are appreciated as if they were monoliths.

Frank

This post is also here http://www.utteraccess.com/forum/Rep...l#entry2215542

[June7]

You are using the intrinsic Replace function? Variables should not be within quotes. Reference to control on form is a variable.

OlMsg.HTMLBody = Replace("HREODContactName", "HREODContactName", Forms![A1S1 Onboarding Tracking Form].[HREODContactName])

or if code is behind the referenced form

OlMsg.HTMLBody = Replace("HREODContactName", "HREODContactName", Me.HREODContactName)

[Soule]

Hi, Miss June! Thanks for looking at my stuff...AGAIN. You'll never know how much your help has been useful.

I changed the following and the population (partially) worked:

- Cleaned up my Replace function statements...namely changed first parameter to message object and took out quotes of replacement text so it would be treated as a variable
and not a string like you recommended. Thanks for that catch.

- Changed the send mail-type in my Outlook Tools>Options from HTML to Rich Text because the STMP mail server system is a non-web MS Exchange intranet at this
company.

- Re-saved my template from HTML to Rich Text.

The send now:

- Includes the graphic (though moves it from the top of mail to the bottom...working on fixing that).

- Includes the template text & bullets (but it loses the bolding of bolded text & repeats hyperlink text next to
the link so it appears twice).

Frank