Sending emails to multiple people based on same query and report

[choaspes]

Hi All

At present I have a tblData, a tblPeople, a long union query and a report that displays its results. On a form I select a person from tblPeople (another field on that table contains their email address), run the query and report in that person's respect and email the results to them.

What I want to do is add a button which runs some VBA code which cycles through tblPeople, runs the query for each person, works out if the query has returned any records from tblData for that person, and if so - sends them the report.

I would be very grateful for some views on the easiest way to accomplish this, before I embark on my usual run of trial - error - error - error - ask for help. It's the cycling through tblPeople and running the query for each record, then checking if the query has returned a result which I really need the most help with. I think I can probably handle the DoCmd.SendObject stuff.

Thanks in advance!

Edit: I am using Access 2010

 

[pbaldy]

This should get you started:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[choaspes]

Thanks very much, hopefully that'll get me going. Lots of functions that aren't familiar to me there.

 

[pbaldy]

No problem; post back if you get stuck.

 

[choaspes]

I thought I'd post the code I created to solve this problem, in case another beginner has similar requirement in future. I hope it's not too sloppy, it seems to work.

The first thing I did was to change the query behind the report. It still returns records from tblData filtered by an ID from tblPeople, but instead of getting the ID from a combo box on a form (where the user selects the person) it gets the ID number from a temporary variable called tvPerson.

The code below runs the query for each record in tblPeople (by starting with a value of "1" for tvPerson and incrementing it each time), and if the query contains any data it sends that person the report. I haven't included the code that supplies the name, email address or body text for the email, but that's all done through using DLookup to find those values in tblPeople.

Dim varPeopleCount As Integer
Dim varPeopleMidcount As Integer
Dim varPeopleEnd As Integer

On Error Resume Next
varPeopleEnd = DCount("[ID]", "tblPeople") + 1
varPeopleCount = "1"

Do Until varPeopleCount = varPeopleEnd

varPeopleMidcount = varPeopleCount
[TempVars]![tvPerson] = varPeopleMidcount
DoCmd.OpenQuery "MyQuery"

If DCount("[ID]", "MyQuery") <> "0" Then
DoCmd.SendObject acSendReport, "MyReport", etc etc etc
Else: End If

varPeopleCount = varPeopleMidcount + 1

Loop

Hope this isn't too full of holes and somebody finds it helpful one day, I take a lot of advice from this site and I wanted to try and give a little back.