473,320 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Replace Function Statements Subverting Automated E-mail

34
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!

Expand|Select|Wrap|Line Numbers
  1. ' This section populates the <<KnownAs>> text greeting in the e-mail template with the "KnownAs" data in the
  2. ' record. If the data in the record is null (blank), "FirstName" data in record populates instead.
  3.  
  4. OlMsg.BodyFormat = olFormatHTML
  5. OlMsg.HTMLBody = Replace("KnownAs", "KnownAs", "Forms![A1 Onboarding Tracking Form].[KnownAs]")
  6. If IsNull("Forms![A1 Onboarding Tracking Form].[KnownAs]") Then
  7. OlMsg.HTMLBody = Replace("KnownAs", "KnownAs", "Forms![A1 Onboarding Tracking Form].[FirstName]")
  8. End If
  9.  
  10. ' Debug.Print "Choose/populate e-mail greeting name", Err.Number, Err.Description
  11.  
  12. ' Body section is almost all boilerplate (populates (3) HR EOD contact infos and movie code).
  13.  
  14. OlMsg.HTMLBody = Replace("HREODContactName", "HREODContactName", "Forms![A1 Onboarding Tracking Form].[HREODContactName]")
  15. OlMsg.HTMLBody = Replace("HREODContactInternalPhone", "HREODContactInternalPhone", "Forms![A1 Onboarding Tracking Form].[HREODContactInternalPhone]")
  16. OlMsg.HTMLBody = Replace("HREODContactInternalEMail", "HREODContactInternalEMail", "Forms![A1 Onboarding Tracking Form].[HREODContactInternalEMail]")
  17. OlMsg.HTMLBody = Replace("MovieCode", "MovieCode", "Forms![A1 Onboarding Tracking Form].[MovieCode]")
  18.  
  19. ' 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
Mar 1 '12 #1
4 1694
Rabbit
12,516 Expert Mod 8TB
The proper syntax is:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 1 '12 #2
soule
34
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...

Expand|Select|Wrap|Line Numbers
  1. OlMsg.HTMLBody = Replace(OlMsg.HTMLBody, "KnownAs", Forms![A1 Onboarding Tracking Form].[KnownAs]) ' & ""
  2.       If IsNull(Forms![A1 Onboarding Tracking Form].[KnownAs]) Then
  3.           OlMsg.BodyFormat = olFormatHTML
  4.       OlMsg.HTMLBody = Replace(OlMsg.HTMLBody, "KnownAs", Forms![A1 Onboarding Tracking Form].[FirstName]) ' & ""
  5. End If
And this version causes the same error on the Replace statement line...

Expand|Select|Wrap|Line Numbers
  1. Dim strKnownAs As String
  2. Dim strTemplateText As String
  3.  
  4. strTemplateText = "Dear Known As Your Human Resources contact HREODContactName Their internal phone # HREODContactInternalPhone Their internal e-mail address HREODInternalEMail MovieCode"
  5.     strKnownAs = IIf(IsNull(Forms![A1 Onboarding Tracking Form].[KnownAs]), Forms![A1 Onboarding Tracking Form].[FirstName], Forms![A1 Onboarding Tracking Form].[KnownAs])
  6.         OlMsg.BodyFormat = olFormatHTML
  7.     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 'strTemplateText =' statement in this second version?

Any small pointers will be accepted as monoliths.

Frank
Mar 1 '12 #3
Rabbit
12,516 Expert Mod 8TB
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.
Mar 1 '12 #4
soule
34
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...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).
Mar 3 '12 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: pembed2003 | last post by:
Hi all, I asked this question in the C group but no one seems to be interested in answering it. :-( Basically, I wrote a search and replace function so I can do: char source = "abcd?1234?x";...
2
by: Sharon | last post by:
I've had an Access 2000 app running successfully for many months on both Windows XP and Windows 2000. Recently when my Windows 2000 users call a particular report, they get first a dialog...
9
by: Not Me | last post by:
Hi, I'm having bother with the replace function in access 2002, a while back I remember not being able to get it to work... then suddenly the next time I tried it did work. Now it doesn't...
3
by: Dave | last post by:
I have an Access 2K application that is distributed to about a dozen users (all with identical NT environments and identical Access versions, object libraries and service packs). I am using the VBA...
3
by: Andy Sutorius | last post by:
Hi, I read the thread (2/16/05) regarding a replace function in C# however it didn't answer my question. I have a string which is building an insert sql statement and I would like to replace...
4
by: Cor | last post by:
Hi Newsgroup, I have given an answer in this newsgroup about a "Replace". There came an answer on that I did not understand, so I have done some tests. I got the idea that someone said,...
4
by: Lauren Wilson | last post by:
Hi folks, We have a need to replace sub strings in certain message text. We use the Office Assistant to display help and often use the imbedded formatting commands. Those of you who have used...
5
by: enno | last post by:
Dear Community, We have a problem with null-Bytes in varchar-Columns, which are not handled correctly in our application. Therefor we try to filter them out using the Transact-SQL REPLACE...
8
by: Warren Moxley | last post by:
Hi there, i've been searching for a C String search and replace function. I need to find all occurrences of " " in a char* array, and replace them with another char, I know how to do this in...
1
by: seanmatthewwalsh | last post by:
Hi I have a website that uses an Access 2003 database. I have controls on my pages that are bound to SqlDataSources that pull data from this database. In a couple of them, I need to use the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.