VBA For..Next syntax

[chancer via AccessMonster.com]

I have a table of suppliers (tblSuppliers), including a field [email status]
(Yes/No)

I have a query (qrySuppliersToMail) that includes only the suppliers marked
Yes

I have an Outlook emailing routine (SendMail), which works ok


What I am now trying to do is create a routine that sends a mail for each of
the records in the qryt

ie

for each record in qrySuppliersToMail
SendMail [email address], strSubject, strBody
next


but i don't know the correct syntax

this line is failing:
Set rs = CurrentDb.OpenRecordset("qrySuppliersToMail")

and if I fix that i am not sure of the syntax of the loop


thanks in advance

 

[Stefan Hoffmann]

hi,

this line is failing:
Set rs = CurrentDb.OpenRecordset("qrySuppliersToMail")

What kind of error (message, number)?

and if I fix that i am not sure of the syntax of the loop

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qrySuppliersToMail")
Do While Not rs.Eof
'do something
rs.Next
Loop
rs.Close
Set rs = Nothing



mfG
--> stefan <--

 

[chancer via AccessMonster.com]

i was getting:
runtime error '13' type mismatch

with DAO in it i get:
compile error. user-defined type not defined.


thanks

 

[Stefan Hoffmann]

hi,

i was getting:
runtime error '13' type mismatch
with DAO in it i get:
compile error. user-defined type not defined.

Are you having an Access project (.adp) or an Access database (.mdb)?
Check your references for DAO (VBA/Extras).


mfG
--> stefan <--

 

[chancer via AccessMonster.com]

it is an mdb

i am not sure what you mean by reference.

 

[Guest]

Open any module. If you do not have one, you can create a new blank one. Open
it.
The menu options will change. Click on the Tools menu. Select References.
You should see Microsoft DAO 3.51 or 3.6 with a checkbox next to it near the
top of the list. If it is missing, scroll down the list and check it.
The code should work now.

Good Luck!

jmonty

 

[chancer via AccessMonster.com]

that worked

many thanks