Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-20-2017, 10:51 AM   #1
rudders
Newly Registered User
 
Join Date: Oct 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
rudders is on a distinguished road
Email Report

Hi, I am trying to email a report automatically and have come to a point where I am stuck.

I have a list of branch codes, some of which have an email address, that require a report to be emailed

I have used the code below which I have borrowed from else where. The code will create the report and email it but its contents is blank. It will go through all branch codes with an email.

To run the report manually I presently load the from a drop down list, so in the query, "location code" field I have the criteria

"[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"

I think my issue is that I need this line of the code below:

Where = "[qry_data_Dashboard_rpt_email].[Location Code] = " & CStr(rst![Location Code])

to put the Location code "virtuallly" in my drop down box to produce a populated report.

Any help would be appreciated

Thanks

Sub EmailScorecard()

Dim dbName As Database
Dim rst As Recordset
Dim lRecNo As Long
Dim lBillCnt As Long
Dim Where As String
Dim MsgBody As String
Dim Email As String
Dim Subject As String
Dim Docname As String
Docname = "Rpt_RDM_Dashboard_email"
Set dbName = CurrentDb()
Set rst = dbName.OpenRecordset("rdmbranchlist", dbOpenDynaset)
rst.MoveFirst

lBillCnt = 0

Do While Not rst.EOF

If rst![Branch_Email_Address] <> "" Then

Where = "[qry_data_Dashboard_rpt_email].[Location Code] = " & CStr(rst![Location Code])

DoCmd.OpenReport "Rpt_RDM_Dashboard_email", acPreview
Email = rst![Branch_Email_Address]
Subject = "Branch Scorecard" & rst![Location Code] & rst![RDM Name]
MsgBody = "Hi " & rst![Location Code] & vbCrLf & "Please find your Branch Scorecard for Last Week."
DoCmd.SendObject acReport, Docname, acFormatPDF, Email, , , Subject, MsgBody, Save, True
DoCmd.Close acReport, "Rpt_RDM_Dashboard_email", acSaveNo

lBillCnt = lBillCnt + 1 '*** Count Emails Created ***
End If

rst.MoveNext '*** Move to Next Record ***
Loop

MsgBox Format(lBillCnt, "#,###") & " Email Branch Scorecard Created."
Set rst = Nothing '*** Close RecordSet ***



End Sub

rudders is offline   Reply With Quote
Old 10-20-2017, 10:59 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,487
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Email Report

Since the query has the criteria, in your code you should populate that:

[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir] = CStr(rst![Location Code])
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-20-2017, 11:05 AM   #3
rudders
Newly Registered User
 
Join Date: Oct 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
rudders is on a distinguished road
Re: Email Report

Hi I have tried that but I get Runtime 2465 telling me the database cant find the field [Dashboard_Branch_Select_Redir]

rudders is offline   Reply With Quote
Old 10-20-2017, 11:06 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,487
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Email Report

That form would have to be open.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-20-2017, 11:41 AM   #5
rudders
Newly Registered User
 
Join Date: Oct 2017
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
rudders is on a distinguished road
Re: Email Report

hi, thanks for helping.

I have changed it to:-

Where = "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]" = CStr(rst![Location Code])

and it now removes the error and when I hover over the cstr part of the code it is picking up the Location code, but the report is still blank.

So it is acting as if I am opening the report without populating "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"
rudders is offline   Reply With Quote
Old 10-20-2017, 12:06 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,387
Thanks: 15
Thanked 1,623 Times in 1,541 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Email Report

You are not using the where argument of the OpenReport Method.

I prefer to put the criteria into the report's RecordSource query itself. That way, I don't have to open the report, I can just create the PDF and have my criteria recognized. If you are sending only one email at a time, both methods work. However, if you are doing a bulk process where you are sending dozens or even hundreds of emails, you don't want the extra overhead of having to open the report first so that the open report is what is sent to the .pdf. It will seriously slow down the loop.

I run the loop in code and put the criteria into a hidden form field each time I go through the loop. Then the Where clause in the RecordSource query gets the value from the hidden form field:

Where SomeFieldID = Forms!myform!txtSomeFieldID
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Gasman (10-20-2017)
Old 10-20-2017, 01:47 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,487
Thanks: 15
Thanked 4,148 Times in 4,079 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Email Report

Quote:
Originally Posted by rudders View Post
hi, thanks for helping.

I have changed it to:-

Where = "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]" = CStr(rst![Location Code])

and it now removes the error and when I hover over the cstr part of the code it is picking up the Location code, but the report is still blank.

So it is acting as if I am opening the report without populating "[Forms]![Branchselectionform]![Dashboard_Branch_Select_Redir]"
No, I meant to replace that, which does nothing, with what I posted, which should populate the form with the value, allowing the query to find it.


__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
criteria. , email , query , record selection

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer data from form to report and then attach report to email cino89 Reports 0 10-24-2014 04:29 AM
email a report with the email address as a verable Jaymin Reports 3 06-13-2014 07:43 AM
Send report to email & insert email address off current form MichaelWaimauku Forms 17 12-11-2012 06:32 PM
VBA Script to Email Specific Parts of Report to Specific Email Addresses ShadowFox Modules & VBA 27 05-03-2012 06:15 AM
Email a report as body of email? jeffm3434 Reports 3 02-03-2006 09:59 AM




All times are GMT -8. The time now is 01:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World