Passing query result via VBA (1 Viewer)

JamesN

Registered User.
Local time
Today, 06:27
Joined
Jul 8, 2016
Messages
78
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

Registered User.
Local time
Today, 06:27
Joined
Nov 2, 2015
Messages
823
Code:
...
qrystr1 = "Select * from PCFLMList where department='" & me!department & "'"
Set rst = CurrentDb.OpenRecordset(qrystr1)
...
 

Users who are viewing this thread

Top Bottom