Solved Using the DoCmd.SendObject loop through all records on a form (1 Viewer)

[kengooch]

I am trying to send an email to everyone on a form. When I open the form it asks for the Appointment date. The form then shows all people with that appointment date. I use this code and it sends an email to the first person in the list. The query (qApptPickTm) that uses a "Between [Start Date] And [End Date]" to isolate the records. I show this on a form (fSndMailByDate) to the computer user to verify that there are records. There is a Send Mail button at the bottom of the form. When clicked it initiates the following VBA code.

DoCmd.SendObject , , acFormatHTML, tStfPEmail, , "kenneth.gooch@va.gov", "!! TEST EMAIL !!! Reminder", "Dear " & tStfFstNm & "," & vbCrLf + vbCrLf & "My Message here. " & vbCrLf & "Occupational Health", False

I know I need to create a loop that looks at the current record set and then loops through until the end of the file, and I've tried numerous things to no avail.
I'm sure I have to assign the current form and it's content to a variable. Maybe something like "RecCnt = me.currentform, then create a loop, maybe a do while or do until the last record is found, then my code, then next loop.

Would appreciate any help.
Thanks so much
Ken

 

[Gasman]

Use the form's RecordsetClone ?

 

[MajP]

  Dim rs As DAO.Recordset
  Set rs = me.recordsetClone
  Do While Not rs.EOF
    'get a value from field is Rs!FieldName
   ' put send object code here pull fields values as need
    rs.MoveNext
  Loop

 

[kengooch]

Great thanks!!