Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Filtering Access Report and Sending via Email

I have a report that I created that is customer specific. I have a table with each record having the customer id and fields with additional information (order #, purchase order, order date, etc...)
I want to select the table and then filter, record by record, and email the report to the customer with the report being in the body of the email. (we use Outlook)
I have created the following code, however I am getting a run-time error of 3021 - No Current Record
I am not sure why. There are 52 records in the recordset:

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Order Confirmation", dbOpenTable)

While Not rs.EOF

    Reports(0).Filter = "[customer_id] = " & rs!customer_id
    Reports(0).FilterOn = True

    DoCmd.SendObject acSendReport, "Order Confirmation Message", acFormatPDF, _
        "username@domain.com", , , "Daily Report", "Here is your Daily Report", False

    rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
0
JJENSEN3
Asked:
JJENSEN3
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
GozrehCommented:
add before the line While Not rs.EOF
If Not rs.EOF Then rs.MoveFirst
0
 
Eric FlammSenior ConsultantCommented:
Well, you dim your recordset variable as rst, then you use rs in your code...
That shouldn't even compile if you have Option Explicit on.

Take a look at this link: FMS Documentation for SendObject - when you use acFormatPDF - the report output will be attached as a PDF, not placed in the message body.

That said, the conventional VBA code for sending an e-mail looks like:
Public Function SendEmailWithOutlook( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String)

    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item
 
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Send       ' Send the message immediately
    End With

    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing

End Function

Open in new window

from http://msdn.microsoft.com/en-us/library/office/ff197046(v=office.15).aspx

With this code, you can loop through your recordset and execute the following pseudocode:
Open report in design mode
Change filter conditions or data source query
Open report in print preview mode
Output to pdf
Create an mailitem object
Attach pdf
Set parameters of mailitem
Send it

Open in new window


Alternatively, if you really just want the data from the current record in the email body, just use the data to create an HTML string and set the HTMLBody property of the mailitem to the HTML string. No need for an Access Report object at all.
0
 
JJENSEN3Author Commented:
I corrected the recordset dim statement, However I know that there is a current pointer on a record, so I am confused by the "No Current Record" Error. The sendmail looks to have the proper syntax, so it should send the report output as a PDF with the current record??
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Nick67Commented:
Your code just looks odd to me all the way around.
Why is the loop in the Report_Open event?
It should be in a command button off of a form, passing in an OpenArgs value that then causes the report to be filtered

Private Sub SendReports_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Order Confirmation", dbOpenTable)

Do Until rs.EOF
    DoCmd.OpenReport "Order Confirmation Message", , acPreview, , , acHidden, "[customer_id] = " & rs!customer_id
    DoCmd.SendObject acSendReport, "Order Confirmation Message", acFormatPDF, _
         "username@domain.com", , , "Daily Report", "Here is your Daily Report", False
    DoCmd.Close acReport, "Order Confirmation Message",  acSaveNo
    rs.movenext
Loop


your Report_Open should have code like
Private Sub Report_Open(Cancel As Integer)
If  Nz(me.OpenArgs,"",<>"" then
    Me.RecordSource = "select WhatEver from WherEver where " & Me.OpenArgs
End If


I can't really see the looping in the report's open event working out well.
YMMV
1
 
JJENSEN3Author Commented:
The reason for the loop is that I need the report to output as an independent email  for each record in the table.
Think of it this way:
I have a table of customer records. Each record has an order $ total in the record. I want to send an email receipt of the order, as a PDF, to each customer independently.
0
 
JJENSEN3Author Commented:
I put it in the open portion because I want it to fire without user input. I would run this as a daily automated task.
0
 
Eric FlammSenior ConsultantCommented:
The way to run the report for each record in the table is to put the table in a recordset and loop through the recordset, running the report each time through the loop. You can pass arguments to the report from the recordset through the OpenArgs collection, or you can open the report in design mode and modify the data source, e.g.
set myRst=dbs.openrecordset(mySQL)
while not myRst.eof
strWhere="KeyField='" & myRst.Fields("myField") & "'"
docmd.openreport reportname:=rpt,view:=acViewPreview, wherecondition:=strWhere, windowmode:=Hidden
'Make any other changes to the report based on the recordset
docmd.outputto acOutputReport, rpt, acFormatPDF, filepathAndname
'Send the e-mail, attaching the file
...e-mail code goes here, or put it in a separate method
docmd.close acreport, rpt, acSaveNo
rst.movenext
wend

Open in new window

You might be able to use SendObject to generate the report and e-mail in the same command - I've never had much luck with it, and I usually need a copy of the PDF in the archives, anyway
0
 
Nick67Commented:
The reason for the loop is that I need the report to output as an independent email  for each record in the table.
 Think of it this way:
 I have a table of customer records. Each record has an order $ total in the record. I want to send an email receipt of the order, as a PDF, to each customer independently.

I get all that -- but doing it in the REPORT'S open event makes no sense.
My understanding of report events is that you have open, load, page header, report header, group headers, details, group footers, report footer and page footer events, in that order.  To put the loop filtering the report recursively in the Open event makes little sense -- that event and loop would complete in its entirety before any of the other events even occur.  So the only report that would actually render is the last filtered one.

I am thinking that perhaps DoCmd.SendObject is able to open another instance of the report -- normally that CANNOT be done -- and that's why it doesn't blow up altogether.

I put it in the open portion because I want it to fire without user input. I would run this as a daily automated task.
There are a myriad of ways to do that.  You could put the code I posted into a code module and have some form that remains open call it on a Timer event.  Or as I suggested, on a form, and behind a button, or even in a FORM'S open event -- but I can't see this code working well in the REPORT'S open event.  That just doesn't make sense.
0
 
JJENSEN3Author Commented:
Nick67, after stepping back a minute and looking at this, yes, you are absolutely correct - The report was not the place to put the event logic. What ultimately worked was creating a form and firing the logic through a command button event. I'm sure there is a better way to automate the start event, but for now, a command button works fine. Your input along with the code/logic from Eric Flamm has allowed me to reach an acceptable solution. Thanks to all for the input.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now