Errors using Access/SendObject to send Exchange mail

[GordonLeeds]

Dear VB gurus,

I’m hoping someone can help solve my problem with SendObject, or recommend an alternative method for sending emails. I have built an Access DataMart application to automate custom reporting and run scheduled updates against our enterprise software (Ecometry, running an Image db on an HP3000 under MPE/ix, though I believe all of that is irrelevant to my current issue.) The Access db is on a newish Dell with Windows 2000 & Office 2000. I have a table of report/update subscribers with Exchange 2000 email addresses. The system generates and saves the day’s reports, and then queues up messages to each report subscriber. In the morning, the operator reviews the log files, checks the Unsent message queue, and pushes the “Send Messages” button. VB code compiles the unsent messages for each subscriber one by one, and then says (text message assembly greatly abbreviated):

stEmail = rsUnsentDataMartMessages.Email (e.g. ‘Gordon Leeds’)
stTitle = “DataMart Report/Update Notification”
stReportMsg = "You have a Report. "
stUpdateMsg = "An Update was done."
DoCmd.SendObject , , acFormatHTML, stEmail, , , stTitle, stReportMsg & stUpdateMsg

Thing is, SendObject is failing unpredictably, and for any of several different reasons, depending on the weather. I can detect NO pattern to the errors I’m getting. Some days it works perfectly. Makes no difference whether Outlook is openly running at the time. Rebooting first doesn't help. Restarting the Access program is necessary after most failures, but doing it in advance does not reduce the likelihood of error. There’s no evident correlation with WHO gets and doesn’t get sent on any given day, nor with the contents of the messages. Eventually, if I keep re-trying, they all send without making any changes to the variables.

There are three different ways in which SendObject is failing, at apparently random intervals. That is, it will successfully send some subset of the unsent messages, and then fail in one or the other of these ways. When I retry, sometimes it gets the rest of the messages out, and sometimes it fails again, perhaps in a different way.

1) Most commonly, it will send one or more emails, and then error out with this helpful dialogue box: “Reserved Error”. Code execution stops, and the remaining unsent messages are not marked as being sent, so I just have to close and restart the program and try again.

2) Worse, at other times the SendObject method will fail for SOME of the intended message recipients WITHOUT generating any error message at all. For example, this morning it sent to the first person in the Queue, and then to the last two people, skipping over the 4 people in between. The code continued to execute, marking ALL of the queued message as having been sent as of Now(). As you can imagine, I find it extraordinarily annoying to have to check Outlook's sent messages, compare to who HAD a queued message, delete the DateSent entries for the right subscribers, and then try to send again, knowing full well that it may fail again part way through the remaining messages!

3) This one is rare, but sometimes I get a few good sends, and then I get a pop-up saying "There are no registered wizards of this type." Click ok, code stops.

All of this was only occasionally a problem at first. Now, several months later, I realize the problem has gradually gotten worse and worse, until now it's a daily frustration.

What on earth is causing Microsoft Access 2000/VB 9.0 to generate such egregious errors in conjunction with Microsoft Outlook 2000/Exchange 2000? Why is it so randomly flaky? Above all, is there a better way to do what I want?!?!

Is there a Reference I should have checked that isn’t, or vice versa? I’ve got the following set as References:
• VB For Applications
• MS Access 9.0 Object Library
• MS Office 9.0 Object Library
• MS DAO 2.5/3.5 Compatibility Library (for compatibility with a related app)
• MS VB for Applications Extensibility 5.3
• OLE Automation
• MS Excel 9.0 Object Library
• MS Graph 9.0 Object Library
• MS Outlook 9.0 Object Library

Thanks in advance for ANY ideas on this topic!

Regards,
Gordon Leeds
IS Manager, Sun Precautions, Inc.

 

[Coxylaad]

Hi Gordon,
Did you get a resolution to this problem?
I am suffering your problem number 2 all the time. Seems to have something to do with the size of the message, but if I do an isolated test with some made up code it works fine

 

[Darrylles]

Hi,

May or may not help:

1) I'd use: rsUnsentDataMartMessages!Email not
rsUnsentDataMartMessages.Email.

2) Is this failing on the same pc, or many different ones?
I would in any case ensure that the actual email address is used and not it's associated label:

i.e. gordonbloggs@leeds.com rather than 'Gordon Leeds'

3) I also can't understand why you don't have a ,FALSE on the end of your sendobject string to stop the email app. from opening the email for editing (default is true).
You are using Exchange (used to be bane of my life), so may not apply.
If you totally understand the 'sendobject' action and have been through the above - then ignore this post.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk