By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Email a report and attach each record as seperate report

P: 161
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub E_mailExt_Click()
  6.     Dim strEMail As String
  7.     Dim oOutlook As Object
  8.     Dim oMail As Object
  9.     Dim strAddr As String
  10.     Dim MyDB As DAO.Database
  11.     Dim rstEMail As DAO.Recordset
  12.     Dim strReportName As String
  14.     Set oOutlook = CreateObject("Outlook.Application")
  15.     Set oMail = oOutlook.CreateItem(0)
  17.     strReportName = "ES Test Failure"
  19.     DoCmd.OutputTo acOutputReport, "ES Test Failure Report", _
  20.         acFormatPDF, "N:\Lab\ES_Test_Failure_Reports" & "\" & strReportName & _
  21.         "-" & Format(Date, "mm-dd-yy") & ".pdf", False, , , acExportQualityPrint
  23.     'Retrieve all E-Mail Addressess in tblEMailAddress
  24.     Set MyDB = CurrentDb
  25.     Set rstEMail = MyDB.OpenRecordset("Select * From EXTEMail", _
  26.                     dbOpenSnapshot, dbOpenForwardOnly)
  28.     With rstEMail
  29.       Do While Not .EOF
  30.         'Build the Recipients String
  31.         strEMail = strEMail & ![email] & ";"
  32.           .MoveNext
  33.       Loop
  34.     End With
  35.     '--------------------------------------------------
  37.     With oMail
  38.       .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  39.       .Body = "Please review the attached report."
  40.       .Subject = Replace(Replace("ES Test Failure # |1: P/N: |2", "|1", Nz([ID #], "")), "|2", Nz([Part Number], ""))
  41.       .Attachments.Add "N:\Lab\ES_Test_Failure_Reports" _
  42.                         & "\" & strReportName & "-" & Format(Date, "mm-dd-yy") & ".pdf"
  44.           .Display
  46.     End With
  48.     Set oMail = Nothing
  49.     Set oOutlook = Nothing
  51.     rstEMail.Close
  52.     Set rstEMail = Nothing
  54.     Dim aFile As String
  55.     aFile = "N:\Lab\ES_Test_Failure_Reports\ES Test Failure.pdf"
  56.     If Len(Dir$(aFile)) > 0 Then
  57.          Kill aFile
  58.     End If
  60. End Sub
In this case I have more than one record, I need each record exported and attached to an email using the above code.

The code above works good for a single record but not with multiple records.

So my question is: with the above code is it easy enough to add more code to do what I want to do or do I need to filter the form and report and send each one separately?
3 Weeks Ago #1
Share this Question
Share on Google+
4 Replies

Expert Mod 2.5K+
P: 3,257

I havenít looked in detail at your code, but in general, what you want to do is create a record set that includes both the e-mail address(es) that you want to send the report to, as well as the filtering data specific to that addressee. You cycle through those addressees, and send the Report that is filtered accordingly. There are multiple ways to do this. You can use a global filtering string, you can export the report, filtered appropriately, and named appropriately, then attach that file to an e-mail.

Again, lots of ways to skin this cat. We can talk options with additional details.

Hope this hepps!
3 Weeks Ago #2

P: 161
Thanks for the response Twinn.
After some thought and discussion I was looking at it wrong. They only want to email the current record that is currently shown on the form. So what I need to do is have the output section to be filtered to the current record only.
Expand|Select|Wrap|Line Numbers
  1. "[ID #]=[Forms]![ES Test Failure Enter New]![ID #]"
This is the filter that I have to preview the report, but do not see a location for it in the docmd.outputto section of my code. Do I need to open the report filtered first for it to only output the 1 record that they want? Or is there a little more to it? Do I need to filter the form first then run this operation? Do I need to change what form and what report(Based on a filter query) to run be for this code?

I hope what I'm asking for makes sense? Looking mostly for the right direction to follow or validation that I'm on the right track.
3 Weeks Ago #3

P: 161
So what I ended up doing was:
1) Made a copy of that report, gave it a new name
2) Made a on open event
3) Updated my email code to use this report instead.
This worked like a charm. Although I'm sure there was a better way to do it, but it works.

Thanks Twinny for getting me to do some thinking instead of freaking....
3 Weeks Ago #4

Expert Mod 2.5K+
P: 3,257

I do what you are trying to do all the time. I use a global variable that the Report uses as a filter (you could also use that global variable as a criterion in your report's underlying query). Then, I simply export the report as a pdf, saving as a name particular to the "customer", generate an e-mail and attach that file. I even use this for massive mail-outs--I just loop through each recipient, generate the report, create e-mail, attach the report.
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.