Replace Function in Access 2007 for HTML Email in Outlook 2010

T

tblast8941

New member
Local time
Today, 04:43
Joined
Aug 4, 2014
Messages
6
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).

Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.

Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.

The only thing that is not working is the Replace() function, all other aspects of the code work fine.

I've provided a simplified version of the code below:

Code: 
Dim myOlApp As Outlook.Application
Dim objMailMessage As MailItem
Dim stBody As String
 
Set myOlApp = Outlook.Application
Set objMailMessage = myOlApp.CreateItemFromTemplate("C:\Users\Desktop\template.oft")
 
stBody = "xyz example"
stReplace = Forms!MainForm!txtReplacementText
 
With objMailMessage
 
 .To = "Example, Test"
 .Subject = "Testing"
 .BodyFormat = olFormatHTML
 .HTMLBody = Replace(objMailMessage.HTMLBody, stBody, stReplace)
 
 .Save
 
End With

I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.

What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?

Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
 
You say the Replace() function isn't working but have you actually tested it in the Immediate Window to confirm that it isn't working for sure?
 
The Replace() function works in the Immediate Window.

When I do a Debug.Print on the .HTMLBody property before and after the Replace() function line, the Immediate Window reflects that the change has occured, however, the email draft does not reflect said change.
 
Try...
Save the replaced value in a variable and assign the contents of the variable to HTMLBody
 
Do you mean to just simply insert a line of text into the HTMLBody rather than attempt to replace it?

EX:
Code: 
With objMailMessage
 
 .To = "Example, Test"
 .Subject = "Testing"
 .BodyFormat = olFormatHTML
 .HTMLBody = "Static string test"
 
 .Save
 
End With

If so, that doesn't seem to work either - it's as if I cannot modify any portion of the email body itself. In my actual code, the .To and .Subject properties are both variables that change as the code loops through a listbox, and that functionality works fine, the issue seems restricted to the .HTMLBody property.
 
This is what I was saying:
Code: 
Dim myOlApp As Outlook.Application
Dim objMailMessage As MailItem
Dim stBody As String
 
Set myOlApp = Outlook.Application
Set objMailMessage = myOlApp.CreateItemFromTemplate("C:\Users\Desktop\template.oft")

stReplace = Forms!MainForm!txtReplacementText
stBody = [COLOR="Blue"]Replace(objMailMessage.HTMLBody, stBody, stReplace)[/COLOR]
 
With objMailMessage
 
 .To = "Example, Test"
 .Subject = "Testing"
 .BodyFormat = olFormatHTML
 .HTMLBody = [COLOR="blue"]stBody[/COLOR]
 
 .Save
 
End With
Also are you sure that's the resulting string is a properly formatted HTML string? That may be an issue.
 
Saving the replaced value in a variable and then assigning that variable to the .HTMLBody property doesn't work either.

I don't know why the resulting string wouldn't be a properly formatted HTML string -the format of the dynamic string was never a problem before, and it wasn't changed after the upgrade to Outlook 2010.
 
Try this:
Code: 
.HTMLBody = "<HTML><BODY>Hope this should work</BODY></HTML>"
 
try .Display or .Send , instead of .Save
 
spikepl might be on to something too. I don't imagine you can save to a template unless you save it as a different name or send/display the new message.
 
spikepl said:
try .Display or .Send , instead of .Save
Click to expand...

.Display instead of .Save does indeed work, however, the changes made to the email message via the Replace function revert back to the template's text when the email is closed and saved as a draft.

.Send also works, in that the applied changes take effect, however, I do not want the emails I am generating to be sent automatically.

I save them as drafts because there are instances where I may have to insert additional text lines and/or attach files. Technically, this could be done with the .Display property, but that would mean there's 50+ open email drafts on my desktop that I'd have to sift through, as saving them as drafts from the display view does not retain the Replace function changes.
 
spikepl said:
So how about trying to save it first, which should create a draft, and then edit the draft in code?
Click to expand...

That's the ticket.

New code looks like this:

Code: 
Dim myOlApp As Outlook.Application
Dim objMailMessage As MailItem
Dim stBody As String
 
Set myOlApp = Outlook.Application
Set objMailMessage = myOlApp.CreateItemFromTemplate("C:\Users\Desktop\template.oft")
 
stBody = "xyz example"
stReplace = Forms!MainForm!txtReplacementText
 
With objMailMessage
 
 .To = "Example, Test"
 .Subject = "Testing"
 .BodyFormat = olFormatHTML
 
 .Save
 
End With
 
With objMailMessage
 
 .HTMLBody = Replace(objMailMessage.HTMLBody, stBody, stReplace)
 
 .Save
 
End With

Thank you both for your help. Thread solved.
 
You must log in or register to reply here.

Similar threads

A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
438
Gasman
Gasman
W
Access 2007 connecting to Outlook 2007 through gmail server
Replies
8
Views
370
Gasman
Gasman
Jason Lee Hayes
Solved Open Outlook & check for new mail where search criteria is applied to the email subject line
Replies
19
Views
715
Jason Lee Hayes
Jason Lee Hayes
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
222
Trevor G
Trevor G
T
Access When emailing brings up Outlook 2016 wizard to add email account
Replies
10
Views
489
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom