OutputTo Report Pages

[modest]

I run a report that lists the productivity of different cities. It's about 50 pages long. Some cities span several pages, some only one. I also have a table where email addresses are linked with a city. I would like to send a specific report page(s) to only the email addresses that link with that page's city. This way, people only get an email address if their city is listed and they don't have to look at information that doesn't belong to them.

To send my emails I am using the OutputTo to save my report to the harddrive. I am then adding the saved report as an attachment for an outlook email.

Are there any ideas how to get this done? Someone said something about filtering the report, can I output just the filter?


----after typing this, my brain has been booting up, I might be on to something.

Please post your suggestions.

 

[Treason]

Put this behind a command button..
It will create a different html file for each city in your table

Dim rst As Recordset
'Modify this SQL to something that shows a distinct list of all your cities
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT city from tblClients;", dbOpenDynaset)
Do Until rst.EOF = True
    'Substitute MyReport for whatever your report is called
    DoCmd.OpenReport "MyReport", acViewDesign
    Reports("MyReport").FilterOn = True
    Reports("MyReport").Filter = "[city] = '" & rst("City") & "'"
    DoCmd.OutputTo acOutputReport, "MyReport", acFormatHTML, "C:\City-" & rst("city") & ".html"
    rst.MoveNext
Loop
DoCmd.Close acReport, "MyReport", acSaveNo
rst.Close

I wrote this is Access 97 using DAO... so you may need to reference DAO if youre using above 97