Emailing individual reports to individual users (1 Viewer)

rmcafee · May 23, 2019

I'm trying to come up with a way to email pages of a report to the person the page of the report has on it.
Each page of the report has data about an individual person, some people will have 2-6 pages of data, others will have only 1. Is there a way to automatically email only the pages that apply to that person?
I'm not an expert, but I'm a moderate level user of Access. So you may have to be specific in your replies.

theDBguy · May 23, 2019

Hi. How about opening the report with only the individual's info on it. So, if the most pages a person could have on the report is 6, then the whole report is no more than 6 pages, because it will only contain a single person's data. You can then email this report to that particular person. You can keep doing this until you have sent all the data to everyone.

rmcafee · May 23, 2019

I don't think that will work for me. The report will have data for about 160 people and I don't want to email them one at a time. I'm trying to automate this, so that I can mass email the whole report to the individual person and the email will only have the pages that apply to that person. It's very complicated, I know, but I think there must be a way to do it.

theDBguy · May 23, 2019

rmcafee said:
I don't think that will work for me. The report will have data for about 160 people and I don't want to email them one at a time. I'm trying to automate this, so that I can mass email the whole report to the individual person and the email will only have the pages that apply to that person. It's very complicated, I know, but I think there must be a way to do it.

Hi. I think we're actually saying the same thing here. I wasn't suggesting you manually open each report and click send email 160 times. I was suggesting using code to automate those very same steps. For example, it might go something like this (in pseudocode):

Code:

Loop From 1 To 160
    Open report filtered to currrent user (1 then 2 then 3, and so on...)
    Send filtered report to current user's email address
Repeat loop

So, you see, you only click one button and 160 emails are sent automatically.

rmcafee · May 23, 2019

AHH, I see what you're saying.
Where exactly do I enter the code, View Code?...(design view)
If the identifying field is an employee # for each person, what would the code look like?

theDBguy · May 23, 2019

rmcafee said:
AHH, I see what you're saying.
Where exactly do I enter the code, View Code?...(design view)
If the identifying field is an employee # for each person, what would the code look like?

Hi. Here's a sample code on how to "open" a report filtered to a specific record.

rmcafee · May 23, 2019

Maybe I should change my status to Novice...haha
Looks good. I can try this tomorrow and see if I can get it to work.
So I will need to create a form and use this code on a button on the form?
Do you know if this will work with all email clients? or is it specific to Outlook?
Also, what is the code for actually sending the email? "Send filtered report to current user's email address"
Repeat loop"

Micron · May 23, 2019

IIRC you will have to close the last report then reopen with new filter and send subsequent email. Link code doesn't indicate that.

Mark_ · May 23, 2019

The EMailing of the individuals is NOT done in the report. Rather you have a FORM that goes through each Individual's record in what ever table you store this in, then for EACH individual, it call a report ON THAT INDIVIDUAL ONLY. The report is saved to disk with a unique name.

The same loop would then Email the individual their unique report.

Does this make more sense for what theDBguy is suggesting? Also it gives you several questions that you'd need answers to;
1) HOW to make a loop through a query.
2) HOW to call a report for each record
3) HOW to get each report saved to disk with a name passed to the report
4) HOW to send an Email from ACCESS
5) HOW to format the Email for each user, including the report.

Each of the above should give you a very good idea where to look for answers if you don't already know how to do each.

Micron · May 23, 2019

rmcafee said:
Do you know if this will work with all email clients?

I think not. AFAIK, Access doesn't play well with other email clients and there's not a lot of documentation for using them with vba. There's TONS of sample code, depending on your situation. Maybe SendObject? There's also CDO for sending mail which I think is not app specific 'cause you connect with email server.

rmcafee · May 23, 2019

Would it help if I attached the database with dummy data. I have to change the data because it involves students at my school.
In a nutshell, I'm trying to create this distribution for my school. It's to help teachers and in turn, students. I'm trying to automate the distribution of this report, so that it creates a process which will save teachers a lot of work and time.
It will take me a day or so to get this done.
I appreciate any help you guys/gals can give.

Mark_ · May 23, 2019

Does the school have one email client that is used by all faculty? Or more to the point, is there one Email client (like outlook) that anyone running this process will always have?

rmcafee · May 23, 2019

Our official email client is Lotus Notes, but I could probably use/install Outlook and make it work for this purpose.
I have access to all the email addresses I would need and could easily add them to Outlook's address book.
BTW- I have used Lotus Notes to attach output files from Access and it's worked.

Mark_ · May 24, 2019

And older thread here may help you out...
https://www.access-programmers.co.uk/forums/showthread.php?t=131103

Micron · May 24, 2019

suggest you just try sending one report to yourself and see before going whole hog, so to speak. try SendObject method and you don't have to save report as pdf if it works for you - unless you want network copy. If you get any Windows security issue, look into CDO as I mentioned.

or research
ms access send report via email as pdf

Uncle Gizmo · May 24, 2019

I answered a very similar question on Access World Forums (AWF) a while ago.

I used the question as a basis for a Blog on my website which is here:- Generate Multiple Reports

Please bear in mind the answer I gave was aimed at answering a specific question, however I think much of it could be adapted to answer your question. If you have any problems, then post in this thread as I can probably help you out. The sample files are free, all you need do is sign up to my newsletter.

If you go down to the bottom of the web page there are some useful links, one in particular to Gina Whipps website where she shows you how to send multiple emails.

Emailing individual reports to individual users (1 Viewer)

rmcafee

Registered User.

theDBguy

I’m here to help

rmcafee

Registered User.

theDBguy

I’m here to help

rmcafee

Registered User.

theDBguy

I’m here to help

rmcafee

Registered User.

Micron

AWF VIP

Mark_

Longboard on the internet

Micron

AWF VIP

rmcafee

Registered User.

Mark_

Longboard on the internet

rmcafee

Registered User.

Mark_

Longboard on the internet

Micron

AWF VIP

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread