Multiple Recordsets

[ryan1]

I'm automating an email and sending to employees. But i need to do this multiple times and my recordset will change about five times pulling from differnet queries. Does anybody know how to run through the current recordset until EOF is reached change the recordset to rst2 then send those emails until EOF is reached, change the recordset to rst3 etc...


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
dim mydb as database
dim rstas recordset

set mydb = currentdb()
set rst= mydb.openrecordset("Mytable&quot

Set objOutlook = CreateObject("Outlook.Application&quot
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

do until rst= EOF

With objOutlookMsg
.to = rst![Email Address]
.body = "Whatever the message is" & rst!
.send

End With

loop

 

[scking]

I'm a little curious about your design, why you would need to run to EOF and then use another recordset. You can either,

Dim strSQL
strSQL = CurrentDb.QueryDefs("qryMyQuery&quot.SQL
Set rst1 = CurrentDb.OpenRecordset(strSQL)
Run to end.
strSQL = CurrentDb.QueryDefs("qryMyQuery2&quot.SQL
Set rst1 = CurrentDb.OpenRecordset(strSQL)
Run to end.
strSQL = CurrentDb.QueryDefs("qryGetOutlookNames&quot.SQL
Set rst1 = CurrentDb.OpenRecordset(strSQL)

Of course you can always use OpenRecordset("qryName&quot

or use multiple recordsets.

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------