SendObject **Reserved Error** (Fails in Loop)

[nspader]

OK, So i have used the help of a few people on here to get the code below running properly. It has run great for a little while. Now when I send emails using code below, I sometimes get a Reserved Error and it does not send any more emails. Please help and let me know what I need to do to fix this.

[Code=vb]
Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim rst As DAO.Recordset
Dim bkMark As String
Dim stDocName As String
Dim strSendTo As String
Dim strSubject As String
Dim strMessageText As String

Set rst = Me.RecordsetClo ne

If rst.EOF Then

MsgBox "There is no Email Address for Supplier's in this Branch Report File", vbExclamation, "No e-mail addresses"

rst.Close

Set rst = Nothing

DoCmd.Close acForm, "Email", acSaveYes

Exit Sub

End If

Do While rst.EOF = False
bkMark = rst.Bookmark
Me.Bookmark = bkMark

stDocName = "Request for Updated PO Info EMAIL"
strSendTo = rst![EmailAddress]
strSubject = "Wesco Distribution Status Update Report"
strMessageText = "To: " & rst![SupplierName] & vbCrLf _
& "" & vbCrLf _
& "C/O: " & rst![ContactName] & vbCrLf _
& "" & vbCrLf _
& "Attached is a status update report for specific PO line items." & vbCrLf _
& "" & vbCrLf _
& "Please review the attached report and reply back to this email with the requested information." & vbCrLf _
& "" & vbCrLf _
& "If you have any questions or concerns, contact information is located on the attached report." & vbCrLf _
& "" & vbCrLf _
& "Thank you," & vbCrLf _
& "" & vbCrLf _
& "Wesco Distribution Purchasing Department "

DoCmd.SendObjec t acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText, 0

rst.MoveNext

Loop

MsgBox "All emails have been sent to Suppliers", 0, "Email Complete"

rst.Close

Set rst = Nothing

DoCmd.Close acForm, "Email", acSaveYes

Exit_Form_Click :
Exit Sub

Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Click

End Sub
[/Code]

Thank you In Advance.

Nick

[Stewart Ross]

Hi again Nick. Having reviewed your code before I can't see anything obvious. I am wondering if there is any chance of having a zero-length e-mail sendto address causing a SENDOBJECT failure? You can guard against this if it is at all likely using an IF:

[code=vb]IF strSendto = "" then
strSubject = "Wesco Distribution Status Update Report"
strMessageText = "To: " & rst![SupplierName] & vbCrLf _
& "" & vbCrLf _
& "C/O: " & rst![ContactName] & vbCrLf _
& "" & vbCrLf _
& "Attached is a status update report for specific PO line items." & vbCrLf _
& "" & vbCrLf _
& "Please review the attached report and reply back to this email with the requested information." & vbCrLf _
& "" & vbCrLf _
& "If you have any questions or concerns, contact information is located on the attached report." & vbCrLf _
& "" & vbCrLf _
& "Thank you," & vbCrLf _
& "" & vbCrLf _
& "Wesco Distribution Purchasing Department "
DoCmd.SendObjec t acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText, 0
END IF[/code]

If this is not likely, one other possibility is that the report you are sending is empty - nothing to report. If this is also unlikely then you are going to have to systematically debug by setting a break point at the start of the loop then stepping line by line through the code until you come across the failure point. You can examine the values of variables or print them to the immediate window (using Debug.Print), and check in particular that all expected values are present.

-Stewart

[nspader]

Thank you for the reply. Both are unlikely because I have set up queries that filter out the possibility of empty fields prior to the loop running.

I did discover something...A little background first.

When I run my reports, I run them by branches. In a given report run I run 2-4 branches. This is selected by a drop down menu on the form prior to the email run. This then generates the data for the email form.

When I send emails for the first branch it sends succesfully everytime. If I then change branches and resend it fails after the first few emails.

However, If I exit out of the DB program and go back in I can send another branch successfully (as if it was the first branch ran). If I try to send another branch it fails after the first few.

Is it possible that something doesnt properly close out after the first run? Is there a code to do a fresh start each time, or something like it?

Any help is appreciated.

NICK

[Stewart Ross]

Hi Nick. If you could post the code that does the selection and branching bits you mention this would be very helpful. A possibility is indeed that something within the code is not closed properly or set back to a known state when you select the next set of e-mails to generate.

Cheers

Stewart

[nspader]

These selections are down in queries, each building on itself. I am very new to access programing and do not believe there is a "code" persay to post. I would be under the assumption that the problem would be in the code posted. But I am not sure.

I do not know where to find the code you are asking for, since like I say it occurs by queries built off another query.

Please advise.

NICK

Hi Nick. If you could post the code that does the selection and branching bits you mention this would be very helpful. A possibility is indeed that something within the code is not closed properly or set back to a known state when you select the next set of e-mails to generate.

Cheers

Stewart

[Stewart Ross]

Hi Nick. When queries are created in the query editor there is SQL code created in the background. You will see this if you select View, SQL View from the query editor window. You can copy and paste the query SQL code into these posts (using the [code=sql] code tag to delineate the start of your code).

At this remove I cannot think of more that can be done to assist you without seeing a version of your database.

If it was possible for you to attach a sanitised version of your database as a Zip file I and other contributors could check this out for you.

If you feel you could not post a public copy of the database you could send me a personal message (PM) to obtain my e-mail address and send the DB that way if you prefer. Otherwise I doubt there is much more that can be done for you at present.

-Stewart

[nspader]

I would be happy to attach the DB however I cannot attach a zip file. Any reccomendations on attaching or another way?

Please advise.

Nick

[nspader]

Attached is the DB that I have designed. I have tried to strip it down as much as I can. I have changed two things from my normal db. The first is in my code for sending the email I have changed the option to edit ON so that it will pop up the message and you have to manually press send. The error mentioned occurs in both manners. Also, I have changed the supplier emails to all be my email, that way if they do get sent through to check they will all come to me and not sent to the supplier. I have limited supplier information to just what is needed to run emails.

Please help if possible.

Thank you in advance

Nick

[Stewart Ross]

Hi Nick. I've run the code in the sample DB successfully for all test branches in the sample without any object failures. The e-mails were all placed in my Outlook outbox awaiting send (I did not send them to avoid flooding your e-mail address with the tests!). I could not force a failure condition during my tests.

Anyway, having seen the loop functioning well with repeated branch choices it would not appear to be your code as such that is causing the object failure. It suggests that there is an interaction difficulty between the repeated SendObject requests and the receiving e-mail server - can't think of any reason why offhand, other than potentially tiiming issues (if previous requests are not yet dealt with and the request queue is unable to handle new requests because the previous ones have not yet been processed, for example).

-Stewart

[nspader]

Stewart,

I was curious if you had them autosend, not through outlook but by turning of edit feature. I did state that it through the error either way, but I did not verify that. I apologize. I made the assumption if it occured one way it would occur the other as well. I am sending without edit options. Code set to 0. Just curious if that could have anything to do with it.

As a side note, being new to access DB's and setting this up from scratch, How does it look? From a DB function and coding standpoint.

Thank you for all your help with this.

Nick

[nspader]

One other quick note. Would you know if it could have anything to do with the fact that outlook is set up with an email exchange server?

Thanks

Nick

[Stewart Ross]

Hi Nick. Your DB is looking good and your code was well-structured. You used a separate form for the filtered e-mails for each branch, and this is not actually necessary (you could open the query directly within your recordset code in the branch selection form instead) but everything appears to work well.

I do have the feeling that the errors you are experiencing relate to the Outlook side of things, and possibly to the e-mail requests continuing to be received before Outlook is ready to act on them. I do not know this for certain, nor do I have a solution to it if it is the cause of the undefined error.

I wish I could assist more, but I'm at a loss to suggest what more you can do. It is possible to use VB to control Outlook from Access (as an Office automation application) instead of using the built-in SendObject method, but this may involve considerably more programming in the short-term.

I think if you tried the DB on a different computer with different e-mail access it would help eliminate whether the Outlook end of things is causing the difficulty or not.

-Stewart