Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filtering Access Report and Sending via Email

Posted on 2014-11-18
9
Medium Priority
?
1,042 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

721 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