Creating a group email (1 Viewer)

Novice1

Registered User.
Local time
Today, 15:07
Joined
Mar 9, 2004
Messages
385
On my main table I have organization information of different locations (location, unit name, etc.). The primary key is UnitID (autonumber).

On my secondary table, I have all the trainers assigned to the entire organization. I have a UnitID field (number) to associate the trainers to the corresponding unit on the main table (name, email address, etc.).

I have a form to show each location on my main table. I created a subform showing the trainers assigned to that unit (linked by UnitID field).

(1) I want to send an email to only the trainers that are associated with the record. The following pulls all the trainers. How do I pull only the trainers from the current record? Should there be criteria in the qryTest? Other? Any help is appreciated.


On Error Resume Next

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset

Dim vRecipientList As String

DoCmd.RunCommand acCmdSaveRecord

Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryTest")


If rs.RecordCount > 0 Then

rs.MoveFirst
Do

If Not IsNull(rs!COREmail) Then

If Not IsNull(rs!COREmail) Then
vRecipientList = vRecipientList & rs!COREmail & ";"
End If

End If

rs.MoveNext

Loop Until rs.EOF

DoCmd.SendObject acSendNoObject, , , vRecipientList, , , , "Sir/Ma'am," & vbCr & vbCr & vbCr & _
"vr", , True


End If


Error_Handler_Exit:
On Error Resume Next
Exit Sub

Error_Handler:
If Err.Number <> 0 Then Err.Clear
Resume Error_Handler_Exit
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:07
Joined
Oct 29, 2018
Messages
21,358
Hi. Yes, try adding a criteria to your recordset. For example,
Code:
Set rs = db.OpenRecordset("SELECT * FROM qryTest WHERE UnitID=" & Nz(Me.UnitID,0))
 

Novice1

Registered User.
Local time
Today, 15:07
Joined
Mar 9, 2004
Messages
385
Works like a charm. Many thanks
 

Users who are viewing this thread

Top Bottom