Email pieces of a report that pertain to individual users

[Guest]

I have a report based on a query which I email out daily to over 40 different
managers using the SendObject command. The report has a different header for
each manager's department.

Currently, the manager has to look through the report to find the
information which pertains to his/herself, and being that it is often over
100 pages this is cumbersome to the end user.

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate the report so that each header gets exported
to a different snapshot allowing each manager to only get the information
that pertains to his/her area? (referencing a table which links the header
with an email address)

Any assistance would be very much appreciated.

 

[Steve Schapel]

Matt,

Yes, this is possible. One approach is to loop through the managers
list, and set the criteria of the query that the report is based on.
Code will be something along these lines...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT ManagerID, Email FROM
ManagersTable")
Set qdf = dbs.QueryDefs("YourReportQuery"­)
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE ManagerID
=" & !ManagerID
qdf.SQL = strSQL
DoCmd.SendObject acSendReport, "YourReport", "Snapshot
Format", !Email
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

 

[Guest]

I'm brand new with VBA. This might be a really stupid question, but where do
I put this code. Before I was running a macro but could not dynamicly select
email addresses. To use the code you gave me, I tried to create a command
button on a form and the set up a private sub, but when I executed the
command I get this message:

Compile error

User-Defined type not Defined

With the compiler calling out "dbs As DAO.Database"

 

[Steve Schapel]

Matt,

It sounds like you are using Access 2000, which does not set a Reference
to DAO by default. You have probably done the right thing so far. But
in the VB Editor window, go to the Tools|References menu, and put a tick
next to Microsoft DAO 3.6 Object Library.

 

[Guest]

Thanks a million Steve. That worked.

Matt,

It sounds like you are using Access 2000, which does not set a Reference
to DAO by default. You have probably done the right thing so far. But
in the VB Editor window, go to the Tools|References menu, and put a tick
next to Microsoft DAO 3.6 Object Library.