Solved

Filtering Access Report and Sending via Email

Posted on 2014-11-18
9
638 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show, step by step, how to integrate R code into a R Sweave document
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now