Solved

Create Reports by Customer and email

Posted on 2013-12-29
6
339 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 48

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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