How do I create a macro to email specific pages of a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to email specific pages of a report based upon content within the
report to users on our network. Each page emailed will be based upon content
specific to the user. For example, Page 1 contains data for John Smith while
Page 2 contains data for Jane Doe. I want to automatically email John Smith
Page 1 and Jane Doe page 2.
 
Vitamin,

It is possible, but very awkward, to do this with a macro. As it
involves looping through the records, this is an example of where a VBA
procedure, looping through a recordset, is much more functional than a
macro.

Something like this (caution: untested air code!)...

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 UserID, Email FROM YourUsersTable")
Set qdf = dbs.QueryDefs("YourReportQuery"­)
BaseSQL = qdf.SQL
With rst
Do Until .EOF
strSQL = Left(BaseSQL, Len(BaseSQL)-3) & " WHERE UserID = " &
!UserID
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
 
Back
Top