Solved

CDO Replacement for .CreateMHTMLBody

Posted on 2011-03-17
19
1,756 Views
Last Modified: 2012-05-11
Using MS Access and CDO, I have started to use the .CreateMHTMLBody(filename) method to generate emails, which has fixed a number if issues.  

However I need to modify the emails before sending.  To do this I create a temporary, html file, which I then run through this command.  However, for some reason, I cannot delete the temporary html file until I exit Access.  I haven't got any public variables and am wondering if it could be CDO itself.  

In order to work around this issue, I was wondering if there was a similar command that could use a variable (containing the html code), rather than a file?
0
Comment
Question by:Andy Brown
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 57
ID: 35157576
<< I cannot delete the temporary html file until I exit Access>>

  Are you closing the file?  How are you creating it?

JimD
0
 
LVL 57
ID: 35157603

 and yes, you can certainly send HTML through CDO without that.  All you need is the HTML in a string:

http://support.microsoft.com/kb/286431

JimD.
0
 

Author Comment

by:Andy Brown
ID: 35157705
Thanks as always.

I am creating the file using the following syntax:

Dim strHTMLFilename As String
strHTMLFilename = "D:\Test.html"
Open strHTMLFilename For Output As #1
Print #1, varHTMLBody       'This is the variable containing the full html script
Close #1

At this point I can still delete the Test.html file.  However, once I have run the following method:

.CreateMHTMLBody (strHTMLFilename)

The file appears to stay open.  All of the variables are cleaned down at the end (and are only local variables).

**************************

I used to use the .HTMLbody method, but it caused formatting issues when sending graphical emails to certain platforms (mac, iPhone, Web Office) etc.  As soon as I moved to the  .CreateMHTMLBody("D:\Test.html") syntax - everything looks correct on those platforms.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35157777
<<.CreateMHTMLBody (strHTMLFilename)>>

 Definitly CDO that's holding it open.  Have you tried deleting after your through sending the mail and the CDO object has been closed and set to nothing?  Or are you in a loop and sending multiple e-mails?   One work a round might be to create multiple files - one for each send, and then delete all after your done sending eveything.

JimD.
0
 

Author Comment

by:Andy Brown
ID: 35157834
That's pretty much the route I've gone down (multiple files).

Just in case it's me...After the .send command has been issued, I simply have the following command to clean up:

set objMessage = nothing

(objMessage is the objct variable used).
0
 
LVL 57
ID: 35158252

  Do you also .Logoff the session and set it to nothing?

JimD.
0
 

Author Comment

by:Andy Brown
ID: 35158437
.Logoff - uhmm, never used that.  Is it something that should be put after the .send command?
0
 
LVL 57
ID: 35158456
<<.Logoff - uhmm, never used that.  Is it something that should be put after the .send command? >>

  Yes, but it applies to the session object, not the message object.

JimD.
0
 

Author Comment

by:Andy Brown
ID: 35158474
Thank JimD,

I'll assign the points to you in a mo, but how/where is it used?
0
 
LVL 57
ID: 35158526
Here's a link to MSDN on sending with CDO:

Creating and Sending a Message
http://msdn.microsoft.com/en-us/library/ms528126(v=EXCHG.10).aspx

 Which has sample code showing it's use.

  I've only used CDO a couple of times in the past and don't remember using the .Logoff either, so I don't know that it will solve the problem.

  Not sure what your code looks like, but something is not getting released (and it may be some type of bug).  I know the .Send method is supposed to invalidate the message object, so the only thing left is the session itself.

  But with that said, I would have thought that .Send would have completed anything to do with the Message object, so it doesn't make sense.  But I can't think of anything else to try.

JimD.
0
 

Author Closing Comment

by:Andy Brown
ID: 35158572
JimD - Thank you as ever for your help.  In the end I create individual html files, which are cleaned down when the app loads up.

That said, I'm going to take a deeper look into it (when I have a spare hour or two), so will come back if I find anything different.

Thanks again.
0
 
LVL 21
ID: 35158620
What I do is load the contents of the HTML file into a variable. The I assign the contents of the variable to the HTMLBody. This avoids CDO from attaching to the file.
0
 
LVL 21
ID: 35158663
Oops ... Question closed.
0
 

Author Comment

by:Andy Brown
ID: 35158692
No worries and thanks for your input.

In case you are interested, the HTMLBody method seems to cause formatting issues (especially on mac and web-based platforms) when using graphics.  Whereas the .CreateMHTMLBody(D:\test.html) - sorts it all out.  I will eventually find out why this is - but at the moment it just seems like Witchcraft.

Thanks again.
0
 
LVL 21
ID: 35158810
Interesting.   Thanks for sharing that information.  If I figure out anything helpful I will post it here.
0
 

Author Comment

by:Andy Brown
ID: 35158877
Best of luck.
0
 

Expert Comment

by:LouisPollock
ID: 35492573
I found that the Accepted Solution didn't work. The CDO object simply didn't let go of the input file until exiting the entire running application. Releasing the form, setting the html source file to null, releasing the CDO object, running the bodyparts.deleteall() or clearstream functions etc didnt' help. Creating mutliple files doesn't solve the situation because then you'd have to persistently store those file names and then after you exit and restart the application retrieve the list and delete the files!

However, I found a *real* solution:
Instead of using syntax: CreateMHTMLBody ("D:\FILE1.HTM") use
CreateMHTMLBody ("file://D:\FILE1.HTM")    or CreateMHTMLBody ("file://D:/FILE1.HTM")    

With this approach releases the lock on the input file immediately.

Phew!
0
 
LVL 57
ID: 35492605

 Many thanks for posting that back to the thread.

JimD.

0
 

Author Comment

by:Andy Brown
ID: 35492607
That's really useful to know - thanks for the post - much appreciated.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I didn’t use eM Client for long when I decided to swap to Outlook 2016. The reason for the switch is that it started asking for payment to continue some of its services after one month.   The problems I faced when I didn’t pay were:   I was not …
We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question