I have been reading quite a bit on this and other forums about email automation and looping queries to gather email addresses. However, I am unable to restrict my list of email addresses to only that are currently being viewed in a subform.
An image of my form is attached. I have 3 tables being used:
The EventAttendance Table is a join table to determine which Contacts attended which Events.
When I try to loop a query to capture email addresses, I am only able to return email addresses for the entire query (all people attending all events) not just those that are listed in the subform for the current event.
How do I adjust the code below to only use the emails that match the EventID I am viewing?
An image of my form is attached. I have 3 tables being used:
- Events
- Contacts
- EventAttendance
The EventAttendance Table is a join table to determine which Contacts attended which Events.
When I try to loop a query to capture email addresses, I am only able to return email addresses for the entire query (all people attending all events) not just those that are listed in the subform for the current event.
How do I adjust the code below to only use the emails that match the EventID I am viewing?
Code:
Private Sub Command19_Click()
Dim rs As New ADODB.Recordset
Dim strEmail As String
rs.Open "EventAttendance Query", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
strEmail = ""
Do While Not rs.EOF
strEmail = strEmail & rs!Email & ";"
rs.MoveNext
Loop
DoCmd.SendObject , , , strEmail, , , "test", "Test", True
rs.Close
Set rs = Nothing
End Sub