Solved

Create Reports by Customer and email

Posted on 2013-12-29
6
323 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:LukeChung-FMS
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now