Solved

Create Reports by Customer and email

Posted on 2013-12-29
6
335 Views
Last Modified: 2014-01-06
I need to find a way to create a daily summary report for each customer and dump it into a PDF file and then email it directly to my customer. I only want these reports to be created for customers who had entries in my table for that given day. I want to be able to just select a date and then just click a button and run this code and have it look in my table and find all the customers who had entries that day and then look up their email address in my "tblClinics" and then loop through each of these accounts and email the generated PDF to them. So the way I see this happening is a PDF file of the report will be created for each customer and then stored on my C drive. Then it will loop through each customers account in the "tblClinics" and email their PDF file report to them. Then once the emails are sent it will delete the reports from the C drive. Any ideas?
0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39745401
The way I usually do this is:

1.  Open the report, you can hide it if you want

2.  Create a recordset of the customers who have "entries" that day (whatever that means).  You would need to provide some table structure for me to provide any more details than that.  Make sure you include their email address in the recordset.

3.  Loop through the recordset.  Inside each loop, set the Filter property of the report.  Then, once the filter is set, use the docmd.SendObject method to send the email.
While not rs.eof

    reports(0).Filter = "[CustomerID] = " & rs!CustomerID
    reports(0).FilterOn = true

    docmd.SendObject acSendReport, reports(0).Name, acExportPDF, _
        rs!CustomerEmail,,,"Daily Report","Here is your Daily Report", False

    rs.movenext
Wend

Open in new window

The down side of this technique is that with current Office Security, you will be asked if you want to send each of these emails.  I use vbMapi to get around this, but it involves just a little bit of additional coding, and requires saving the PDF to disk (I generally use Docmd.OutputTo to save the filtered report to a file).
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 39747749
We offer a commercial product, Total Access Emailer, that lets you send emails to everyone in your list (table or query) with Access reports filtered for each recipient as an attached PDF file. Data from your data source can also be used to personalize the message and subject.

Total Access Emailer runs as an Access add-in, so you can do this without any programming by using a simple Wizard. A programmatic VBA library is available in the Professional Version if you want to automate the emails in your code.

For more information, visit http://www.fmsinc.com/MicrosoftAccess/Email.asp

A free trial is also available. Good luck!
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39757428
Hi Dale,
Sorry for the delayed reply. I was off doing other tasks. I'm back on this one now. I have 2 tables, one for the clincs, and one for the records that print on the report. They are linked using the ClinicCodeID.

tblClinics:
ClinicCodeID
ClinicEmailAddress

How do I create the recordset to loop through all the email addresses for my report? The report is controlled by a starting date and ending date from my frmReports. The frmReports is where I have all the selection criteria to open the reports. I want to be able to select a date range and then click a command button and have it loop through all the email addresses for the clinics and email each person their report. I have custom email code too that I use. I don't use Outlook. I use some SMTP code to send emails. So also, how would the code you provided be changed to use my SMTP code instead of the SendObject? I can provide the SMTP code if you want to see that too.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39760899
Ok I think I'm halfway there. I was able to find this code that creates PDF files for each clinicID and put them in a file location on my server. Each file is created with the cliniccode and the date as the name. Now I just need to figure out how to loop through each file and email it to the clinic via the email address for that clinic that is also in my qryClinicDailySummary. Here is the code I'm using to create the individual PDF's

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim sDate As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryClinicDailySummary") 'My parameter query
qdf.Parameters(0) = [Forms]![frmReports]![txtStartDate] 'Form control
Set rst = qdf.OpenRecordset
sDate = Me.txtStartDate

Do While Not rst.EOF
    strRptFilter = "[RecordID] = " & rst.Fields("RecordID")
 
    DoCmd.OutputTo acOutputReport, "Clinic Daily Summary", acFormatPDF, "D:" & "\" & rst.Fields("ClinicCode") & "-" & Format(sDate, "mmddyyyy") & ".pdf"
    DoEvents
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39760980
I was able to get it working on my own with a little brain power. :) I created some declarations to store the email address, file name and the company name and then inserted them into the loop function and then added the SMTP email code that I currently use and called those declarations. And it works like a charm. Here's the code:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim sDate As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryClinicDailySummary") 'My parameter query
qdf.Parameters(0) = [Forms]![frmReports]![txtStartDate] 'Form control
Set rst = qdf.OpenRecordset
sDate = Me.txtStartDate

Do While Not rst.EOF
    strRptFilter = "[RecordID] = " & rst.Fields("RecordID")
    strRptEmail = rst.Fields("ClinicEmail")
    strRptFileName = rst.Fields("ClinicCode") & "-" & Format(sDate, "mmddyyyy") & ".pdf"
    strRptClinicName = rst.Fields("ClinicName")
 
    DoCmd.OutputTo acOutputReport, "Clinic Daily Summary", acFormatPDF, "D:" & "\" & rst.Fields("ClinicCode") & "-" & Format(sDate, "mmddyyyy") & ".pdf"
    DoEvents
    SendReport
    DeletePDF
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing

Open in new window

0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39760982
Your guidance helped me to work through this on my own. But you still deserve the points for your help! Thank you very much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question