Email Access Report to multiple receipients

[rkalapura]

I have a Access 2010 filtered report (filtered on action owner). I also have a query which gives the list of action owners with their email id who has actions pending.

At present I email a common report of all pending actions of all owners to everybody. I feel it is not right as everybody see other's pending actions. What I would like to do is to email the filtered report only to the concerned action owners so that the pending actions of one owner should not go to anybody else. I use Macro action "Email database object" and send the report in PDF format.

I have two unbound text fields set on a form from where the email command button operates. These two text fields can store the name and email id from the query. What I don't know is how I assign the name and email id to the text boxes through a macro from the query. I mean if there are 10 people in the query, it set values of first record to these text boxes, send mail to him, then set values of second record, send email to him and so on until it finish all 10 people.

Any thoughts and ideas highly appreciated. I couldn't find anything like Do while EOF - Next Loop in Macro actions.



Thanks in advance.

[June7]

In VBA, open recordset object of the ownersID and email fields. In loop structure, open report filtered to owner of the current record, send email, close report, move to next record, repeat.

[rkalapura]

Thank you june7 for your reply. I am not very good in VB. Is there any chance to get the VB code to create this loop? I really appreciate your help.

Thank you once again.

[June7]

This is a common topic. Search forum and/or web. For a start, review https://www.accessforums.net/program...ook-21903.html

[NTC]

Sending a report as a pdf attachment is an embedded feature of Access; but triggering/sending multiple reports - each with unique data is not. It will require coding skills. In addition this requirement can be viewed as a security problem / spam machine...so you will find if you use Outlook that even with the looping code working - that there is going to be a warning triggering with each send coming from Outlook.....so that one must manually ok each send. This can be suppressed with a low cost commercial software add-on for Outlook.

[June7]

I use Outlook and don't get the popup warning. Might be because of setting in Outlook to allow program to send email. In Outlook: File > Options > Trust Center > Trust Center Settings > Programmatic Access. I will have to double check this on work pc, can't get it to work on home pc.