Sending multiple (separate) e-mails based on results in query

[Graham T]

I am trying to send separate e-mails for each record in a query, one e-mail per row of the query. The following code is what is being used and this is what is causing the problems. The code writes the results of various fields into the body of the e-mail (strEMailMsg) as text and not as an attachment.

The first problem is that if I open the database and use the SendMail command it will send one e-mail, but will not send again until I close down the database and reopen, then it will.

The second problem is that it will only send one e-mail containing the first record in the recordset (qrySendMail), which at present contains 5 test records to send.

Can anybody see or does anybody know if this is possible.

Private Sub cmdSendMail_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String

strSubject = "Job Outcomes"
strEmailAddress = "graham"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")
rst.MoveFirst
Do Until rst.EOF
    strEMailMsg = rst![strStudentName] & " " & "aged" & " " & rst![strAge] _
    & " has informed us of his new job entitled " & rst![strNewJob] & "." & Chr(10) & Chr(10) _
    & "He has given us these details:  " & rst![strJobDetails] & Chr(10) & Chr(10) _
    & "Angela"

    DoCmd.SendObject , , acFormatRTF, strEmailAddress, , , strSubject, strEMailMsg, False, False

    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

I have also attached the database, Access 2000 version to see if anyone may shed any light on this. I have trawled previous posts but can find any solution to this.

TIA

Graham

 

[Graham T]

Attachment

Here's the example !!

 

[Graham T]

Further to this problem....

I have just tested this on my machine at home and find that there does not appear to be a problem with the code involved.

It loops through each record of the recordset and places each records details in a new e-mail. It also allows me to use the command again, without having to close and reopen the database.

Both machines are running Office 2000, one on Windows XP and one running Windows 2000.

We also tried this code on a different machine at work and it would only send the one e-mail and only the once, until closing and reopening the database.

Would this point to it maybe being a reference problem rather than code.

Graham

 

[Rob.Mills]

The SendObject command in A2k has a problem with it. Don't remember where I read but Microsoft knows about it and fixed it for 2002. In the meantime, are you using Outlook for your emails or something else? I can show you how to adapt your code to use Outlook and it works great.

 

[mission2java_78]

Rob is correct on this one. SendObject is very buggy.

I as well as others have posted a ton of solutions on this forum regarding sending email via the outlook object model. Try searching on the forum and post back if you need help.

Jon

 

[Graham T]

Rob/Jon

Thanks for the replies. I thought I may have found the solution when I realised that I was using Outlook XP on my machine at work and Outlook 2000 at home (where it works correctly).

Tried this morning at work on a machine running Outlook 2K and no joy.

Now I feel that I may have come across the problem - it appears that something relating to Exchange Server may be preventing this. I have just tried it on a machine running Outlook XP but not connected to the Exchange Server and it has sent all 5 mails (to the Outbox) as expected, so looks like I'm going to have to investigate this route with the systems admin.

If you have any further thoughts on this any advice would be appreiciated as I need to find a solution.

Regards

Graham

 

[Graham T]

Further Info.....

After much testing it appears that the problem relates to a security patch that we had to apply to Outlook XP to allow it to interact with our main "SuperOffice" database.

We use this to archive all correspondance from students, however without the patch you only had so long to archive mail from Outlook into SupperOffice and you also recived the warning message warning that "another program outside of Outlook was trying to send messages and that these could be viruses"

As it happens the user that will use this database to send mail does not run Outlook XP so for the time being maybe things will be OK and we shall run with it for now.

If there are any other thoughts on other ideas for performing this action feel free to post.

Graham