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![A1 Onboarding Tracking Form].[KnownAs]")
If IsNull("Forms![A1 Onboarding Tracking Form].[KnownAs]") Then
OlMsg.HTMLBody = Replace("KnownAs", "KnownAs", "Forms![A1 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![A1 Onboarding Tracking Form].[HREODContactName]")
OlMsg.HTMLBody = Replace("HREODContactInternalPhone", "HREODContactInternalPhone", "Forms![A1 Onboarding Tracking Form].[HREODContactInternalPhone]")
OlMsg.HTMLBody = Replace("HREODContactInternalEMail", "HREODContactInternalEMail", "Forms![A1 Onboarding Tracking Form].[HREODContactInternalEMail]")
OlMsg.HTMLBody = Replace("MovieCode", "MovieCode", "Forms![A1 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

[Rabbit]

The proper syntax is:

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

This of course assumes that OlMsg.HTMLBody is prepopulated with a template message. I see no indication of that in the code.

[soule]

Hi, Rabbit -

Thank you for your time in commenting on this post. Your advice (and NeoPa's) on my VBA has been very, very helpful.

I can't seem to get two version of my replace function to work:

This version causes an error 94 "Invalid use of null" on the If IsNull line...

OlMsg.HTMLBody = Replace(OlMsg.HTMLBody, "KnownAs", Forms![A1 Onboarding Tracking Form].[KnownAs]) ' & ""
      If IsNull(Forms![A1 Onboarding Tracking Form].[KnownAs]) Then
          OlMsg.BodyFormat = olFormatHTML
      OlMsg.HTMLBody = Replace(OlMsg.HTMLBody, "KnownAs", Forms![A1 Onboarding Tracking Form].[FirstName]) ' & ""
End If

And this version causes the same error on the Replace statement line...

Dim strKnownAs As String
Dim strTemplateText As String

strTemplateText = "Dear Known As Your Human Resources contact HREODContactName Their internal phone # HREODContactInternalPhone Their internal e-mail address HREODInternalEMail MovieCode"
    strKnownAs = IIf(IsNull(Forms![A1 Onboarding Tracking Form].[KnownAs]), Forms![A1 Onboarding Tracking Form].[FirstName], Forms![A1 Onboarding Tracking Form].[KnownAs])
        OlMsg.BodyFormat = olFormatHTML
    OlMsg.HTMLBody = Replace(strKnownAs, "KnownAs", Forms![A1 Onboarding Tracking Form].[KnownAs])

Do I have to include all the text from my .oft template in my 'strTemplateTex t =' statement in this second version?

Any small pointers will be accepted as monoliths.

Frank

[Rabbit]

In the first one, if you're going to check for null, you need to do it before you try to use it to replace something.

In the second one, you create a variable from the null but you never use it.

[soule]

Hi, Rabbit - thanks for looking at my post and code.

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

- Cleaned up my Replace function statements...na mely 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...workin g 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).