E-mailing access reports

[jamie13]

I have a list of customers, of which I need to send invoices. I have all the contact names and reports done in an access database. I know how to use the send object function, but that works for one account at a time. Is there a way to send out the invoices to each customer and only press one button? If i had to press it for every customer it will take days. I need to send out about 2000 invoices per week.
Thanks for the help
Jamie

 

[AWithers]

Sounds like you need to set up a loop to loop through all the customers (via a recordset) and each time run the report with the customer as a filter on the report

Hope this makes some sense

Andy

 

[jamie13]

ok sounds like a good idea. How do i do it?
Jamie

 

[AWithers]

Its something like this (Sorry I dont have time to test it but hopoefully you get the idea)

Andy
---------

First set up a global variable
Public reportfilter As String

then in your report on the open event put in the following code

'define filter
Me.Filter = reportfilter
Me.FilterOn = True


then on the blick event of your button

Dim db As Database
Dim rs As Recordset
dim reportfilter as string

Set db = CurrentDb
Set rs = db.OpenRecordset("select customername,contactname from yourlistofinvoicestobesent&quot

do until rs.eof = true
reportfilter = "Customername = " + rs!cutomername
docmd.sendobject acsendreport, "youreportname",acFormat...,rs!contactname

loop

rs.close
set rs=nothing