Hi,

I have a database with 2 tables.

Table1 (names of people and their email addresses)
 Person
 Email

Table2 (orders per resp.person)
 Order#
 Person
 field3....field10

Table2 is the source of a report.
In this report, the orders are grouped per person.
I am trying to work out a method of sending each person an e-mail
with the report which is only destined for him.
So Person#1 will only see his/her set of orders in the report
Person#2 will see his/her set of orders
Etc.

Can anyone help me ? I'm an advanced user, but still green in the VB part of
Access, so be kind with me.

Thanks for your help.

Frans

Hi Frans,
Conceptually, you will need to open a Recordset (rs) in code for all the
people (Table 1), loop through this one at a time, and send the report using
the username (or ID or something) as the criteria for the reports
recordsource, from Table 2.
I think I would have a hidden text box on the form, and poke the name (or
criteria) into it and then have the query that the report is based on use
that control as criteria in the query. Poke the next name, send the report
again until .EOF
DoCmd.SendObject bla,bla... may be all you need to actually send the report.
Example code below.
--
Hope This Helps,
Jeff Davies

Sub RSExample()
On Error GoTo errRSExample
    Dim DBs As Database, RSt As Recordset
    Dim strSQL As String, lCounter As Long

    Set DBs = CurrentDb
    strSQL = "SELECT * FROM Table1;"    'Use Real Table name
    Set RSt = DBs.OpenRecordset(strSQL)
    With RSt
        If .BOF Then    'No records
            GoTo Exit_RSExample
        End If
        .MoveLast
        .MoveFirst
        SysCmd acSysCmdInitMeter, "Processing... " & .RecordCount,
.RecordCount
        Do While Not .EOF
            SysCmd acSysCmdUpdateMeter, .AbsolutePosition   'Show progress
            'Need to use real names next 2 lines
            Forms![FormName]![txtCriteria] = ![NameorCriteriaField] 'Set the
value for the reports query
            'The report needs to be using this control as criteria to only
send the appropriate data
            'Do the send
            DoCmd.SendObject acSendReport, "ReportName", acFormatHTML,
![fieldEmailAddress], , , "Report for " & Format(Date, "long date"), , False
            lCounter = lCounter + 1 'Count how many
            .MoveNext
        Loop    'do the next one
    End With
    SysCmd acSysCmdClearStatus
    MsgBox "Reports Successfully Sent to " & lCounter & " People",
vbInformation, "Done"
Exit_RSExample:
    On Error Resume Next
    RSt.Close
    Set DBs = Nothing
    SysCmd acSysCmdClearStatus
    Exit Sub
errRSExample:
    MsgBox Err & " - " & Err.Number, vbCritical, "Report Export Error"
    Resume Exit_RSExample

End Sub