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