e-mail a report based on records in the query...

[Rick's News]

I want a report to go to specific people (records) in the report...
In the query bound to the report is the e-mail field.
How do I pass the e-mail field to the docmd.sendobject?

Thanks in Advance!

-Rick

 

[Allen Browne]

SendObject does not have a WhereCondition, so it sends the entire report to
the recipient.

If you want to send one page of a report to one person, and another to a
different person, apply a filter in the Open event of the report.

1. In the General Declarations section of a standard module, declare a
public string variable:
Public gstrReportFilter As String

2. Use the Open event of the report to apply and reset the filter:
Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

3. Set the filter string before you SendObject:
gstrReportFilter = "ClientID = 99"
DoCmd.SendObject ...

 

[Rick's News]

O.K I didn't think I could break it down that far.

Would it be easier to do this?
In the report there might be 10 records listed.
Everyone on the report can see the report.

What I don't want is to the 28 people in the main table that the report is
built off of to be sent the report only the 10 people of the report.
Is this possible? The query limits the records to the 10 records, but how
do I pass the e-mail fields to the sendobject command?

Thanks for helping me....

-Rick

 

[Allen Browne]

You will need to OpenRecordset on the table that contains the email
addresses, and loop through them.

This sort of thing:

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT ClientID, Email FROM tblClient WHERE Email Is Not Null;"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
Do While Not rs.EOF
gstrReportFilter = "ClientID = " & rs!ClientID
DoCmd.SendObject acSendReport, "MyReport", To:= rs!Email, _
Subject:="Your report", EditMessage:=True
rs.MoveNext
Loop
rs.Close
Set rs = Nothing