Create Reports by Customer and email

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?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Luke ChungPresidentCommented:
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!
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Your guidance helped me to work through this on my own. But you still deserve the points for your help! Thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.