OutputTo Report Pages

modest · Apr 20, 2005

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 · Apr 21, 2005

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

Code:

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

OutputTo Report Pages

modest

Registered User.

Treason

#@$%#!

Similar threads

Users who are viewing this thread