Email a report and attach each record as seperate report

[DJRhino1175]

Option Compare Database
Option Explicit
 
Private Sub E_mailExt_Click()
 
    Dim strEMail As String
    Dim oOutlook As Object
    Dim oMail As Object
    Dim strAddr As String
    Dim MyDB As DAO.Database
    Dim rstEMail As DAO.Recordset
    Dim strReportName As String
 
    Set oOutlook = CreateObject("Outlook.Application")
    Set oMail = oOutlook.CreateItem(0)
 
    strReportName = "ES Test Failure"
 
    DoCmd.OutputTo acOutputReport, "ES Test Failure Report", _
        acFormatPDF, "N:\Lab\ES_Test_Failure_Reports" & "\" & strReportName & _
        "-" & Format(Date, "mm-dd-yy") & ".pdf", False, , , acExportQualityPrint
 
    'Retrieve all E-Mail Addressess in tblEMailAddress
    Set MyDB = CurrentDb
    Set rstEMail = MyDB.OpenRecordset("Select * From EXTEMail", _
                    dbOpenSnapshot, dbOpenForwardOnly)
 
    With rstEMail
      Do While Not .EOF
        'Build the Recipients String
        strEMail = strEMail & ![email] & ";"
          .MoveNext
      Loop
    End With
    '--------------------------------------------------
 
    With oMail
      .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
      .Body = "Please review the attached report."
      .Subject = Replace(Replace("ES Test Failure # |1: P/N: |2", "|1", Nz([ID #], "")), "|2", Nz([Part Number], ""))
      .Attachments.Add "N:\Lab\ES_Test_Failure_Reports" _
                        & "\" & strReportName & "-" & Format(Date, "mm-dd-yy") & ".pdf"
 
          .Display
 
    End With
 
    Set oMail = Nothing
    Set oOutlook = Nothing
 
    rstEMail.Close
    Set rstEMail = Nothing
 
    Dim aFile As String
    aFile = "N:\Lab\ES_Test_Failure_Reports\ES Test Failure.pdf"
    If Len(Dir$(aFile)) > 0 Then
         Kill aFile
    End If
 
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?

[twinnyfo]

DJ,

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!

[DJRhino1175]

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.

"[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.

[DJRhino1175]

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....

[twinnyfo]

DJ,

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.