Send email to multiple recipients based on query

[nablmm]

Hello,

I am running access 2010 and would like to have a macro that runs a query and emails individuals through outlook (exchange) based on the results of that query. Specifically, if the query yields no results, no one should be emailed. One of the columns in the query is the individuals email address, therefore for each result in the query, I would like an email to be sent to that individual. However, if there are multiple results for the same individual, I would like those results to be sent in the same email as opposed to one email per result.

Thank you in advance for your help!

[June7]

Not a macro, use VBA. Common topic in forum. Here is recent one for starters: https://www.accessforums.net/macros/...rts-46472.html

[nablmm]

Thanks for the link June7. I saw the basic structure for the code (below) as well as your comments on pulling data by looping through recordsets. I have a few questions still: First, the way the code is written the vba pulling data from two tables. My data is all in one table. How do I adapt this to search through just the one table. Secondly, my data is filtered in my query. Is it possible to have the code search through the query as opposed to the table? If not, how do I filter using vba? Lastly, keeping in mind question 2, how do I tell the code to do nothing if there are no results after being filtered?

Basic structure
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address"
''.cc = ""
''.bcc = ""
.Subject = "Active Projects Alert"
.HTMLBody = "The following projects are approaching their deadlines:"
.Send
End With


Recordset Code:
Sub SendMail()
Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
Dim ...
Set rsEmails = CurrentDb.OpenRecordset("SELECT Address FROM Staff;")
While Not rsEmails.EOF
Set rsData = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE StaffID=" & rsEmails!StaffID & ";")
While Not rsData.EOF
'code to build email body using rsData record
strBody = strBody & rsData!field1 & ", " & rsData!Field2 & ", " & rsData!field3 & vbCrLf
rsData.MoveNext
Wend
'code to send email
...
rsData.Close
rsEmails.MoveNext
Wend
End Sub

Thanks again for your help!

[June7]

So the email addresses and user IDs and the data are all in one table? This would be unusual.

The first recordset is to get a dataset of unique records for email and user ID. In your case, the first query could possibly be like:

SELECT DISTINCT email, UserID FROM tablename;

The code loops through this dataset and opens second recordset with a filter based on user ID so only data relevant to each user is retrieved and compiled into the email body.

The EOF parameter prevents the looping code from executing if the dataset is empty. EOF stands for End Of File.