Sending email from Report

  • Thread starter Thread starter Jason Gyetko
  • Start date Start date
J

Jason Gyetko

I have a report which I need to print and email from code.

I'm running the following line of code which prints the report (includes a
where clause to filter data)

DoCmd.OpenReport "rptPacklist", acPrintAll, ,
"[ANYUSER_MANHDR]![MNH_MH_CNTR] ='" & Control_Nbr & "'"


I'm not sure how I can email it when the report is run. I know I can use
the following command immediately after the above command, but that means
I'm actually running the report twice then.

DoCmd.SendObject acReport, "rptPacklist", acFormatSNP, "(e-mail address removed)",
, , "Packing List - Control Number " & Control_Nbr, "Packing List included
as attachment", False


Ideally, I want to run that first command then have a line in the actual
report telling it to email itself to someone. Does anyone know how I can do
that?

I've tried adding that second command (SendObject) to the report in several
spots and received the following error messages:

Report_Open()
----------------
The expression On Open you entered as the event property setting produced
the following error: Procedure declaration does not match description of
event or procedure having the same name.

Report_Close()
----------------
Run-time error '2585': This action can't be carried out while processing a
form or report event.

Report_Activate()
----------------
Works if I actually open the report, but when using that first acPrintAll
command (listed above), it does nothing (I'm guessing cause the report is
never actually opened and activated).

Any suggestions would be greatly appreciated. Thanks in advance.
 
Jason:

You are correct that when a report is simply sent to print, the On Activate
event never fires; Activation relates for both a report and a form to the
top of the window z-order as the active object.

On to your issue however. The SendObject command has never supported as
you've also found, specifying a parameter or filter when outputting an
object. This is also true of OutputTo and many similar functions in Access.
There are a couple of work arounds.

1.) Create a form which allows the user to select the filter like
Control_Nbr to use when running the report. Then in your report's query set
as a condition for the MNH_MH_CNTR field that it is =
Forms!MyForm!cboControl_Nbr or something like that dependent on what you
call the control. Then you can have a button on your report to preview the
report and a second to print and/or e-mail it and the report will filter
itself when SendObject is called.

2.) If you don't want to tie yourself to a form, then you can do either of a
couple of things.

a.) On our web in the code and design tips area is a tip on how to modify
your report's underlying query to have parameters like your Control_NBR
supplied by code so that you can fill the paramter before calling
SendObject.

b.) You can create a couple little functions in a general module that work
like this

--- Create a general variable call Dim RptSQLFilter as String in the
declarations
---Create a Sub procedure that simply allows you to specify the SQL filter
you want to use and which sets what ever you supply into the string variable
e.g.
Sub SetRptSQLFilter (strSQLFitler as String)
RptSQLFilter = strSQLFilter
End Sub
--- Create a function that returns a that filter as a string
Function GetRptSQLFilter () as String
GetRptSQLFilter = RptSQLFilter
End Function
--- Last in your report's On Open event add code like this:
Dim strFilter as String
strFilter = GetRptSQLFilter()
If Len(strFilter) > 0 Then
Me.RecordSource = "Select * from " & Me.RecordSource & _
" WHERE " & strFilter
End if

Of course when doing this make sure that you reset the filter to an empty
string by calling SetRptSQLFilter("") after you're done with your report.
So then your code to email and print would look like:

Call SetRptSQLFilter ("[ANYUSER_MANHDR]![MNH_MH_CNTR] ='" & Control_Nbr &
"'")
Docmd.OpenReport "rptPacklist", acPrintAll
DoCmd.SendObject acReport, "rptPacklist", acFormatSNP, _
"(e-mail address removed)", , , "Packing List - Control Number " _
& Control_Nbr, "Packing List included as attachment", False
Call SetRptSQLFilter ("")
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Jason Gyetko said:
I have a report which I need to print and email from code.

I'm running the following line of code which prints the report (includes a
where clause to filter data)

DoCmd.OpenReport "rptPacklist", acPrintAll, ,
"[ANYUSER_MANHDR]![MNH_MH_CNTR] ='" & Control_Nbr & "'"


I'm not sure how I can email it when the report is run. I know I can use
the following command immediately after the above command, but that means
I'm actually running the report twice then.

DoCmd.SendObject acReport, "rptPacklist", acFormatSNP, "(e-mail address removed)",
, , "Packing List - Control Number " & Control_Nbr, "Packing List included
as attachment", False


Ideally, I want to run that first command then have a line in the actual
report telling it to email itself to someone. Does anyone know how I can do
that?

I've tried adding that second command (SendObject) to the report in several
spots and received the following error messages:

Report_Open()
----------------
The expression On Open you entered as the event property setting produced
the following error: Procedure declaration does not match description of
event or procedure having the same name.

Report_Close()
----------------
Run-time error '2585': This action can't be carried out while processing a
form or report event.

Report_Activate()
----------------
Works if I actually open the report, but when using that first acPrintAll
command (listed above), it does nothing (I'm guessing cause the report is
never actually opened and activated).

Any suggestions would be greatly appreciated. Thanks in advance.
Click to expand...
 
Back
Top