Email only sections of a report to related email addresses in a table in bulk

[Bkper087]

I’ve asked this question before, but I never really got an answer. This is something that would make a huge impact on my daily work and an application for its use has come up again. Here is the premise:



Below there is a table and a report. Table, tblEmailAddresses shows the team name in the first column and the email in the second column. Next you see a Report, rptProductsSold. This report shows the products sold and their amount. It is also grouped by team name.

The goal is through VBA (I assume it would have to be) send only the section of the report that is relevant to each team to the corresponding email address in tblEmailAddresses. The result would be similar to a mail merge in word from an excel file. Each email recipient would only receive the information corresponding to their row of data. Any example databases, YouTube videos, websites, or explanations in the comments below would be greatly appreciated.


tblEmailAddresses

TeamName	EmailAddress
Green	Green@email.com
Blue	Blue@email.com
Red	Red@email.com

rptProductsSold

Team Product Price Green #1 $1.00 Green #2 $2.00 Green #3 $5.00 Blue #1 $1.00 Blue #4 $6.00 Red #3 $3.50 Red #1 $1.00

Expected Result

Blue@email.com would get a report showing only the information below

Blue	#1	$1.00
Blue	#4	$6.00

Red@email.com would get a report showing only the information below

Red	#3	$3.50
Red	#1	$1.00

Green@email.com would get a report showing only the information below

Green	#1	$1.00
Green	#2	$2.00
Green	#3	$5.00

[pbaldy]

See if this gets you going:

Emailing a different report to each recipient

[RuralGuy]

Assuming your report shows all of the "teams" it is easy to use the Where condition argument of the open report command to limit the report to just one team. Then run it for each team.

[Bkper087]

@RuralGuy: My goal is to press a button and have it auto send to each email. The practical application for this will be utilizing a few hundred different emails. I'm trying to avoid doing that.

[RuralGuy]

It can all be executed in code under one button.

[Bkper087]

I'm looking into the link @pbaldy posted. @RuralGuy, what I am envisioning you explaining is selecting the team name from a combo box and then pressing a button that filters the report to that team and opens it as an attachment to an email. What I would like to do is to do that without the combo box. Where I press "send" and it automatically sends to each team.

Under the where condition I have tired to do something to the effect of "[TeamID]=" & [TeamIDfk] but again, that only opens the email as a filtered report to that one team. How do I take it one step further to have it sent to all teams?

[RuralGuy]

If you know all of the teams already or they are listed in a table then the button could easily run the report as many times as there are teams, sending each team their own report as an attachment.

[Micron]

I never really got an answer

This is the thread you refer to?
https://www.accessforums.net/showthread.php?t=64822
If it is, it's hardly fair since you got 3 replies but I don't see where you replied to anyone who posed questions. If it's not, ignore this.