E-mailing one report with different data

[AnnieV via AccessMonster.com]

I have a question that may be kind of basic but I’m stuck on the best way to
do this. I run a monthly open items aging report that varies in length (it’s
determined by the amount of vendors that have outstanding items each month so
it can be anywhere from 10-40 pages long). It’s one report but the pages are
broken up by vendor so each page contains separate vendor specific detail.
What I’d like to do is set up a macro that allows me to send each page via e-
mail to the vendor contact (the e-mail address is listed on each page). I’ve
only set up macro’s to e-mail before where everyone gets the same report
(usually internal company based mailings). Is there a way to set this up so I
can run the report and have it automatically e-mail each vendor their
specific detail page? I hope this question makes sense. Being able to do this
would save quite a bit of time.

 

[ErezM via AccessMonster.com]

hi
set a global variable (let's call it vVendorID)
now use the command's button that should send the emails click event:

Dim rs As New ADODB.Recordset
rs.Open "Select This Month's Vendor ID's", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
vVendorID = rs!VendorID
DoCmd.SendObject acSendReport, "repVendors", acFormatPDF, rs!VendorEmail
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

now you need to set a filter on the report's Open event to set it to the
right vendor (each time the report is conveted to pdf and sent it is opened)

Me.Filter="VendorID=" & vVendorID
Me.FilterOn=True

good luck
Erez