Access VBA OpenRecordset Group Level OutputTo No records error

[Mac1206]

Hi,

I have an issue when my code runs, it only grab the records that the cursor is on and not looping through to grab the specific records grouped by ID....For the 31 clients, ID 48961 which is 1 client of the 31 clients which the cursor was on in the Form was the only records pulled for the 31 different client groups...See Attachment Below...My issue is I'm only pulling records where my cursor is pointing to and not looping through all the records...Thanks

Private Sub cmdSendRpt_Click()
Dim rst As DAO.Recordset
Dim strSql As String

strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"

Set rst = CurrentDb.OpenRecordset(strSql)

Do While Not rst.EOF

Me.txtID = rst![ID]

DoCmd.OutputTo acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amccoy\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

DoEvents
rst.MoveNext

Loop

rst.Close
Set rst = Nothing

End Sub

 

[Mac1206]

Thanks, could you please show me in sample code, I'm a visual person and learn through visual teachings. If I can visually see it then I can comprehend it for the most part and everything you are writing will make since...Sorry

 

[Mac1206]

Ok, the simplest, though not the most efficient would be to just use the SendObject after creating the file.
DoCmd.SendObject acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, "C:\Users\amccoy\Desktop\TestFolder" & "\" & rst![ID] & "_" & rst![PymtsPayee] & ".xls"

Bring in ALL the email addresses in the first query and concatenate them with a semi colon ; and use that as the To address.

The email field is in the main query to include ID, Email, PymtsPayee, and the email addresses are separated by ; .... How do I get it to populate in the To: field in outlook, Add Subject, Add Message, and automatically send so the user doesn't have to press send 500 times...

 

[welshgasman]

If you are a viaual person, then you should be checking out YouTube?, lots of video on there for all aspects of Access and a lot of other VBA.
This guy is very good, and there are plenty on examples using Google.

 

[Mac1206]

Thanks, I'm on it now...

 

[Mac1206]

This is my code but I'm getting the Compile error: Variable not defined so I'm missing something but don't know quite what?

Private Sub CmdEmailRpt_Click()

Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim strSql As String
Dim txtID As TempVars

Dim emailTo As String
Dim emailSubject As String
Dim emailText As String

Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem
Dim outStarted As Boolean

On Error Resume Next
Set outApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If outApp Is Nothing Then
Set outApp = CreateObject("Outlook.Application")
outStarted = True
End If

strSql = "Select * FROM Qry_Max_Pymt_PymtEmailDetail;"
Set DB = CurrentDb
Set rst = CurrentDb.OpenRecordset(strSql)

Do While Not rst.EOF

TempVars("txtID") = rst!ID.Value

emailTo = rs.Fields("ID").Value & " " & "<" & rs.Fields("email").Value & ";" & ">"
emailSubject = "This is a test"
emailText = "This is a test to ensure this is working properly"



Set outMail = outApp.CreateItem(olMailItem)
outMail.To = emailTo
outMail.Subject = emailSubject
outMail.Body = emailText
outMail.Send



DoCmd.SendObject acOutputQuery, "Max_Pymt_EmailDetails1", acFormatXLS, , , , , False

DoEvents
rst.MoveNext


Loop


rst.Close

Set rst = Nothing

If outStarted Then
outApp.Quit
End If

Set outMail = Nothing
Set outApp = Nothing

End Sub

 

[welshgasman]

Well it will tell you what variable is not defined.?
Also please put code within code tags, and indented to make it easier to read.?