SendObject report with a filter

[Jeff Hunt]

I have a report that I can open using DoCmd.OpenReport and pass it filter
criteria in the WHERE clause. Now I need to send that same report as an RTF
in an email, but I’m not sure how I can get the filter to work. I was
applying the filter in code because it can change based on what is selected
in the form. I thought of putting each filter in a separate report, but I
would prefer to avoid that since it would end up adding multiple reports to
my database that all do the same thing.

 

[Maverick]

I have a report that I only want a specific record emailed in HTML format.
The code I used to do this is as follows:

------------------------------------------------------------------------------------------------

Dim stDocName As String

stDocName = "rptCaseDetails"

DoCmd.OpenReport stDocName, acViewPreview, , "[ID]=" & [ID]

DoCmd.SendObject acReport, stDocName, acFormatHTML,
DLookup("[EmailAddress]", "tblEmployees", "[ID]=" & Nz([AssignedTo], 0)), , ,
Replace(Replace("Request |1: |2", "|1", Nz([ID], "")), "|2", Nz([Title], ""))

DoCmd.Close acReport, stDocName

 

[Jeff Hunt]

Thanks, that works really well. I added acHidden to the open report so the
users don't see the report before it goes right into an email. One thing I'm
finding is that my filter only seems to apply every other time I press the
button. The other times it opens with the same filter setting as the last
button press. When the filter fails, it also does not open in Hidden mode.
Have you ever experienced this and/or know a way to keep it from happening?

I have a report that I only want a specific record emailed in HTML format.
The code I used to do this is as follows:

------------------------------------------------------------------------------------------------

Dim stDocName As String

stDocName = "rptCaseDetails"

DoCmd.OpenReport stDocName, acViewPreview, , "[ID]=" & [ID]

DoCmd.SendObject acReport, stDocName, acFormatHTML,
DLookup("[EmailAddress]", "tblEmployees", "[ID]=" & Nz([AssignedTo], 0)), , ,
Replace(Replace("Request |1: |2", "|1", Nz([ID], "")), "|2", Nz([Title], ""))

DoCmd.Close acReport, stDocName

------------------------------------------------------------------------------------------------

I have a report that I can open using DoCmd.OpenReport and pass it filter
criteria in the WHERE clause. Now I need to send that same report as an RTF
in an email, but I’m not sure how I can get the filter to work. I was
applying the filter in code because it can change based on what is selected
in the form. I thought of putting each filter in a separate report, but I
would prefer to avoid that since it would end up adding multiple reports to
my database that all do the same thing.

 

[Jeff Hunt]

Nevermind, figured out the every other time thing. It only did it when I
canceled the email, so I just added the docmd.close acreport to my error
handler as well. Thanks again for the tip!

Thanks, that works really well. I added acHidden to the open report so the
users don't see the report before it goes right into an email. One thing I'm
finding is that my filter only seems to apply every other time I press the
button. The other times it opens with the same filter setting as the last
button press. When the filter fails, it also does not open in Hidden mode.
Have you ever experienced this and/or know a way to keep it from happening?

I have a report that I only want a specific record emailed in HTML format.
The code I used to do this is as follows:

------------------------------------------------------------------------------------------------

Dim stDocName As String

stDocName = "rptCaseDetails"

DoCmd.OpenReport stDocName, acViewPreview, , "[ID]=" & [ID]

DoCmd.SendObject acReport, stDocName, acFormatHTML,
DLookup("[EmailAddress]", "tblEmployees", "[ID]=" & Nz([AssignedTo], 0)), , ,
Replace(Replace("Request |1: |2", "|1", Nz([ID], "")), "|2", Nz([Title], ""))

DoCmd.Close acReport, stDocName

------------------------------------------------------------------------------------------------

I have a report that I can open using DoCmd.OpenReport and pass it filter
criteria in the WHERE clause. Now I need to send that same report as an RTF
in an email, but I’m not sure how I can get the filter to work. I was
applying the filter in code because it can change based on what is selected
in the form. I thought of putting each filter in a separate report, but I
would prefer to avoid that since it would end up adding multiple reports to
my database that all do the same thing.

 

[Maverick]

Happy to help. Please mark this question as answered by clicking on yes next
to "Was this post helpful to you?" This helps others identify questions that
have solutions.

Nevermind, figured out the every other time thing. It only did it when I
canceled the email, so I just added the docmd.close acreport to my error
handler as well. Thanks again for the tip!

Thanks, that works really well. I added acHidden to the open report so the
users don't see the report before it goes right into an email. One thing I'm
finding is that my filter only seems to apply every other time I press the
button. The other times it opens with the same filter setting as the last
button press. When the filter fails, it also does not open in Hidden mode.
Have you ever experienced this and/or know a way to keep it from happening?

I have a report that I only want a specific record emailed in HTML format.
The code I used to do this is as follows:

------------------------------------------------------------------------------------------------

Dim stDocName As String

stDocName = "rptCaseDetails"

DoCmd.OpenReport stDocName, acViewPreview, , "[ID]=" & [ID]

DoCmd.SendObject acReport, stDocName, acFormatHTML,
DLookup("[EmailAddress]", "tblEmployees", "[ID]=" & Nz([AssignedTo], 0)), , ,
Replace(Replace("Request |1: |2", "|1", Nz([ID], "")), "|2", Nz([Title], ""))

DoCmd.Close acReport, stDocName

------------------------------------------------------------------------------------------------
:

I have a report that I can open using DoCmd.OpenReport and pass it filter
criteria in the WHERE clause. Now I need to send that same report as an RTF
in an email, but I’m not sure how I can get the filter to work. I was
applying the filter in code because it can change based on what is selected
in the form. I thought of putting each filter in a separate report, but I
would prefer to avoid that since it would end up adding multiple reports to
my database that all do the same thing.

 

[FMS Development Team]

I have a report that I can open using DoCmd.OpenReport and pass it filter
criteria in the WHERE clause.  Now I need to send that same report as an RTF
in anemail, but I’m not sure how I can get the filter to work.  I was
applying the filter in code because it can change based on what is selected
in the form.  I thought of putting each filter in a separate report, but I
would prefer to avoid that since it would end up adding multiple reports to
my database that all do the same thing.

Hi Jeff,

Have you checked out our Total Access Emailer program? It'll take
care of this very easily for you. One of its features is the abilty
to filter Access reports for each recipient and include it in their
email as either the body of the HTML or text message, or an
attachment. In Access 2007, the report can be attached as a PDF file.

For more info vsit http://www.fmsinc.com/MicrosoftAccess/Email.asp

A free 30 day demo/trial version is available.

Good luck!

Luke Chung
FMS, Inc.
http://www.fmsinc.com

P.S. Visit our Micosoft Access Help Center for more resources:
http://www.fmsinc.com/MicrosoftAccess/help.html

 

[Marilyn Myers]

I use Total Access Emailer, but I'm having problems sending emails with
graphics. The emailer works great with just text, but when I add a
logo.....the logo doesn't show up. I have looked at the Total Access Emailer
Sample database and I still can't get the logo to show up on my email. What
am I doing wrong?