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

kengooch

Member
Local time
Yesterday, 22:40
Joined
Feb 29, 2012
Messages
101
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

Enthusiastic Amateur
Local time
Today, 06:40
Joined
Sep 21, 2011
Messages
10,531
Use the form's RecordsetClone ?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:40
Joined
May 21, 2018
Messages
6,356
Code:
  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
 

Users who are viewing this thread

Top Bottom