Question Email Report

[rudders]

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

 

[pbaldy]

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

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

 

[rudders]

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

 

[pbaldy]

That form would have to be open.

 

[rudders]

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]"

 

[pbaldy]

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.