Access 2007 VBA loop code HELP !

[Stacey1306]

Hello,

I have created a membership dbase in Access 2007, in which I need to send out individual membership fees annually to about 326 members. I have set up a form based on a query that runs the report with information of an individual member.

I am having difficulty in my code to loop through the reports and attach to the individual member's email. I can get it to email each member to Outlook, however it attaches all the reports not the individual members report.

Here is my code,

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryEmailInvoices", dbOpenDynaset)
rs.MoveFirst
Do
vMemberID = rs("MemberID")
DoCmd.SendObject acSendReport, "Email Invoices", "PDFFormat(*.pdf)", rs("Email Address"), "", "", "Northern Beaches Liquor Accord Membership", "Please find attached your invoice to renew your annual Membership", False
rs.MoveNext
Loop Until rs.EOF
rs.Close
db.Close

 

[SkipVought]

hi,

You might get better responses for MS Access in one of the many MS Access forums, like forum705.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[genomon]

The problem looks like your EmailInvoices report. Lacking the ability to filter the sending report with something like a WHERE clause, the solution we use involves running a procedure to produce a .pdf report for each recipient and creating a control record with the member name and network address for each recipient. Then loop through the control records table and attach the .pdf to the email.

Beir bua agus beannacht!

 

[dhookom]

genomon,
Stacey1306 didn't quite get to the VBA coding forum but did repost this question thread702-1721243.


Duane
Hook'D on Access
MS Access MVP