Passing query result via VBA (1 Viewer)

[JamesN]

Hi,

I currently use a form which needs to create and send an email to multiple email addresses dependant on the department selected in the form. I have a tabled called 'PCFLMList' which stores a Department column and an email address column. My code at the moment creates and sends the email to all email addresses in the table rather than just the specific department...How can I amend the code so it only returns email addresses for the selected department? I tried using the query result instead of the table but couldn't get this working.

Dim rst As Recordset
Dim strEmailAddress
Dim oApp As Outlook.Application
Dim oMail As MailItem
qrystr1 = "Select "
Set rst = CurrentDb.OpenRecordset("PCFLMList")
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("EmailAddress") & ";"
rst.MoveNext
Loop
oMail.Body = "New Issue"
oMail.Subject = "New Issue raised against ID " & " " & Me.reference
oMail.To = strEmailAddress
oMail.Display
oMail.Send

Thanks in advance

 

[static]

...
qrystr1 = "Select * from PCFLMList where department='" & me!department & "'"
Set rst = CurrentDb.OpenRecordset(qrystr1)
...