how to code for ms access vba to create the report one at a time for each record

[jon huff]

Here is what I am trying to do - i have a report in MS access called "DocProcurement". it is based off of a query. the query can have many records in it. the report shows all records from the query. i want to open the report one record at a time from the query (based on email address), save it, and send the report in email. The report should only have the info from one record in the query, then move on to the next record, create it and send it out, etc... Here is what i have:

Sub CreateEachEmail()
 
Dim Rst As ADODB.Recordset
Dim FaxNumber As String
Dim PhoneNumber As String
Dim SendFaxTo As String
Dim x As Integer
Dim Msg As String
Dim numToEmail As Integer
Dim UserName As String
Dim blnClosed As Boolean
Dim address As String
Dim Atest As ClassEmailFunctions
Dim strEmail As String
 
 
x = 0
 
Set Rst = New ADODB.Recordset
With Rst
    .Open "qryFaxList", cnn, adOpenStatic
    .MoveFirst
        While Not (.EOF Or x = numToEmail)
 
 
             Do Until x = numToEmail Or .EOF
                FaxNumber = Rst![CAFax]
                PhoneNumber = Rst![CAPhone]
                SendFaxTo = Nz(Rst![CAName], "CLOSING AGENT")
                address = Rst![CAEmail]
 
 
                If Rst![NumberOfDocs] < 1000 Then
 
                Else
                    GoTo RS_MoveNext
                End If
 
 
            strEmail = address
 
            Set Atest = New ClassEmailFunctions
 
         Atest.ToAdd (strEmail)
         Atest.Subject = "IMPORTANT DOCUMENTS REQUIRED"
         Atest.Body = "PLEASE SEE ATTACHED REPORT FOR DOCUMENTS REQUIRED"
 
         DoCmd.OpenReport "DocProcurement", acViewPreview, "", , acNormal
         DoCmd.Close acReport, "DocProcurement"
         DoCmd.OutputTo acReport, "DocProcurement", "RichTextFormat(*.rtf)", "C:\Temp\DocProcurement.rtf", False, "", 0
 
         Atest.Attachment = ("C:\Temp\DocProcurement.rtf")
 
         Atest.Send
 
       RS_MoveNext:
 
                .MoveNext
               ' x = x + 1
            Loop
 
 
        .Close
    End With