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:
Code:
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