Email Individualized Reports to List --Filtering SendObject? (1 Viewer)

[Jonathan Kok]

Hello again, it's been a while. I'm back in Access Programming Mode, and here's my latest quandry.

I have a list of registrants that I'd like to e-mail receipts to. The obvious solution would be to send the mail to each registrant as their information is entered in, but alas, this is not a possibility, as the users entering the registrants will not have an appropriate mail client set up.

So here's where I am. I have a form showing a list of registrants, some with and some without e-mail addreses. I've created a button that will either Print or E-mail the receipt, depending on wether they have an email address. I'm using the db.openrecordset method to create the appropriate list via an SQL statement in the VBA code. I was planning on looping through that list with a For..Next loop, sending the receipt for each person on the list, until I realized that there is no filter function in the sendobject method.

I thought of perhaps putting the filter directly in the report's Record Source, except as my list is created via a coded SQL statement, I'm not sure how I can possibly refer to it?

For reference, here's the code (though I haven't tested it yet, and have noted other problems already )

Dim EmailList
Dim numrecs
Set EmailList = db.openrecordset( _
        "SELECT [Conventioneer Info].ciFirstName, [Conventioneer Info].ciLastName, [Conventioneer Info].ciEmail, BatchesDet.bdbaID " & _
        "FROM [Conventioneer Info] INNER JOIN BatchesDet ON [Conventioneer Info].ciID = BatchesDet.bdRefID " & _
        "WHERE ((([Conventioneer Info].ciEmail) Is Not Null) AND ((BatchesDet.bdbaID)=[forms]![frmreviewbatches]![baID]));")
With EmailList
    numrecs = .RecordCount
    If numrecs <> 0 Then
        .MoveFirst
        For i = 1 To numrecs
            DoCmd.SendObject acSendReport, "rptReceiptforEmail", acFormatHTML, !ciEmail, , , _
                    "Receipt for Convention 2005", "Please find attached your receipt for the 2005 Convention!"
        .MoveNext
        Next i
    End If
End With

TIA,
Jonathan