Unanswered: Loop collecting emails from all contacts displayed thru filtered form
I need a button in my CONTACT form that sends an email to all contacts displayed by the form - In filtered mode or none filtered mode. Itīs a marketing database containing a zillion of sortables. Therefore I need the user to make the filtering themselves in a form. For instance it has checkboxes of 40+ products when they want to email customers with a certain product interest. So writing a query for every option is out of the question. Especially since they might want to select all contacts, working at companies w/ turnover over $ 1 million, interested in FORD, having birthday in June and what not...
I can get it to send to the currently displayed record, no problem. Simple SendObject (EmailField is called [PersonalEmail]) like this:
But I need the loop to go thru a form. So I have tried:
Do Until .EndOfForm
Didnīt work of course, a mere guess. But that is what I need, I figure!? How to do until end of form and also the syntax for equivalent to ".MoveNext record in Form"
If I use a loop in a recordset based on a table or query, something I do know how, it will email ALL my contacts, ignoring the filtering made manually in the Form.
But can I get my recordset to identify the filtering made in the form I opened the function from?
Or maybe I could use the recordset method just adding some Where statement to the recordset. Something like this:
rstTblCONTACTS.Open "SELECT * FROM TblCONTACTS WHERE [contactID] = !Forms![CONTACTS]![contactID]"
But I donīt get that to work.
The easiest would of course be to use the Query access makes when a user filters a form. Simply set that query as recordset. But how do I find it? What is this Query called?
Or maybe as suggested below cloning the records the form is showing into a new recordset then using that. But I donīt know how to do that either... Probably also need the same Formloop that I donīt know the syntax for.
I know the logics but am a complete rookie at the VB syntax. Very frustrating. Knowing what to say but not how.
Here is the Function I call to mail ALL contacts(But wish to alter so it just sends to contacts in a Form):
Option Compare Database
Public SendString As String
Dim rstTblCONTACTS As New ADODB.Recordset
'Open the Recordset to Customers
rstTblCONTACTS.ActiveConnection = Application.CurrentProject.Connection
rstTblCONTACTS.CursorType = adOpenKeyset
SendString = ""
If MsgBox("Send Email" & Chr(13) & _
"to all Contacts using Microsoft Outlook?", 4) = 6 Then
Do Until .EOF
' Create the Invoice report Filter used by the Report_Open