Solved

Create Reports by Customer and email

Posted on 2013-12-29
6
330 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 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