Avatar of JJENSEN3
JJENSEN3
 asked on

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
Visual Basic ClassicMicrosoft AccessProgramming Languages-Other

Avatar of undefined
Last Comment
JJENSEN3

8/22/2022 - Mon
Gozreh

add before the line While Not rs.EOF
If Not rs.EOF Then rs.MoveFirst
Eric Flamm

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

ASKER
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??
Your help has saved me hundreds of hours of internet surfing.
fblack61
Nick67

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
JJENSEN3

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

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Eric Flamm

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JJENSEN3

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