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

 

[John Big Booty]

Given that your form references are enclosed in Double Quotes they are being treated as a Static strings rather than as a dynamic form reference. I suspect that if you remove those double quote surrounding the form reference things should work as expected.

 

[WayneRyan]

Frank,

I think you want something like:

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


Wayne

 

[Soule]

Hi, John & Wayne (you guys should do a cowboy movie together) -

Thanks so much for going through my post and code. Your suggestions let me catch, count them...TWO mistakes.

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 value and not a string like you guys 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).

Thanks for your time in going through my post and code.