SendObject Attachment Problem in Access 97

[OracleRenegade]

Hi,

I am using Access 97 (no choice here - so please don't recommend upgrading!) and attempting to send a report via e-mail in a loop using module code (see below)

All works okay EXCEPT when I set the last parameter to FALSE to not edit the e-mail before sending - then the result is a zero KB attachment, BUT, with all other things the same TRUE (edit the mail) works as it should...

Any ideas anyone??


Private Sub mailloop_Click()
On Error GoTo Err_mailloop_Click

Dim stDocName As String

Dim vEmailAddress As String

Dim accountsToMail As Integer

DoCmd.GoToRecord , "", acFirst



stDocName = "NHS Single Debtor Statement"
Rem DoCmd.SendObject acReport, "NHS Single Debtor Statement",
Rem acFormatXLS after NHS Single Debtor Statement


Let accountsToMail = DLookup("[Total]", "AccountsToMailCount", 1 = 1)



For i = 1 To accountsToMail

Let vEmailAddress = DLookup("[EmailAddress]", "LookupEmailAddress", 1 = 1)



DoCmd.SendObject acReport, "NHS Debtor Statements", acFormatXLS, vEmailAddress, , , "NHS Debtor Statement from Leeds Teaching Hospitals", "Please see attached", True

DoCmd.GoToRecord , "", acNext

Next i

DoCmd.GoToRecord , "", acFirst


Exit_mailloop_Click:
Exit Sub

Err_mailloop_Click:
MsgBox Err.Description
Resume Exit_mailloop_Click

End Sub

 

[SeeThru]

Have not seen this one before....

Couple of things to try:

Try adding a doevents to give outlook a chance to complete the processing before mong to the next record:

DoCmd.SendObject acReport, "NHS Debtor  Statements", acFormatXLS, vEmailAddress, , , "NHS Debtor Statement from Leeds Teaching Hospitals", "Please see attached", True
DoEvents        
DoCmd.GoToRecord , "", acNext

if this does not work, look into ouutlook automation - it gives far better control over outlook, and I am sure that would work.

Out of interest, your code seems to send exacltly the same report to everyone: generally, so send filtered versions of the report to different people, open the report first with a where claise, and then send it, then close the report before looping.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[OracleRenegade]

Thanks for that..

I found the problem was with the client machine I was on - the code works fine on another desktop - but adding the final , false to the use of SendObject - helped also...

BTW - my report is coordinated to the form by means of a condition on the report which references the corresponding value in the form - so they all get a different report.