Solved

Filtering Access Report and Sending via Email

Posted on 2014-11-18
9
923 Views
Last Modified: 2014-11-21
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
Comment
Question by:JJENSEN3
[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
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 40451078
add before the line While Not rs.EOF
If Not rs.EOF Then rs.MoveFirst
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 40451082
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
 

Author Comment

by:JJENSEN3
ID: 40455823
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 26

Expert Comment

by:Nick67
ID: 40456465
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
 

Author Comment

by:JJENSEN3
ID: 40456637
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
 

Author Comment

by:JJENSEN3
ID: 40456640
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
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 250 total points
ID: 40456746
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 40457909
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
 

Author Comment

by:JJENSEN3
ID: 40458172
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

632 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