G
Guest
Someone from the message board forwarded me this code...however I think that
there is something wrong with it. Can someone please review and let me how
to proceed with using it? Thanks!
The logic goes as follows:
A recordset is created which has the user's ID and Email. This should
be the same recordset as the report is based off of. The report is then
opened. Now we loop through the recordset and grab the UserID and
Email. Taking this information we then call two procedures. The first
filters the report based on the UserID(or whatever unique identifier you
have on your report - could be email). Now that the report is filtered
the second procedure is called. This time we pass the email (along with
report name) and send a snapshot of the filtered report to this
employee. Then we loop back and process the rest of the recordset.
There are many ways to send emails and many formats to send them in.
This should give you some ideas.
Matt
Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR
' Declare variables
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAcountStatus As String
Dim strEmail As String
Dim strUserID As String
Dim fOk As Boolean
' Build our SQL string
strSQL = "SELECT UserID, Email From [Table1]"
' Set our database and recordset objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
' Open the report
DoCmd.OpenReport "rptEmployeeData", acPreview
' Turn the filter on
Reports![rptEmployeeData].FilterOn = True
' Loop the recordset
Do While Not rst.EOF
' Grab the Email string
strEmail = rst.Fields("Email")
' Grab the UserID string
strUserID = rst.Fields("UserID")
' Call the procedure used to filter the report based on the
current employee
Call FilterReport("rptEmployeeData", strUserID)
' Allow the report to refresh after filtering
DoEvents
' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rptEmployeeData", strEmail)
' Display message if failure
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
' Move and loop
rst.MoveNext
Loop
' Clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Private Sub FilterReport(strReportName As String, strUserID As String)
' Comments: Filters Report based on UserID parameter
' Parameters: strUserID - employee's email UserID
' strReportName - report name
On Error GoTo PROC_ERR
' Declare variables
Dim strSQL As String
'Build SQL String
strSQL = "[UserID] " & " = '" & strUserID & "'"
' Filter the report
Reports(strReportName).Filter = strSQL
' Turn the filter on
Reports(strReportName).FilterOn = True
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Function SendReportByEmail(strReportName As String, strEmail As String)
As Boolean
' Comments: Sends an email using SendObject method
' Parameters: strEmail - employee's email address
' strReportName - report name
' Returns: True of False
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
'set the mail varaibles
strRecipient = strEmail
strSubject = Reports(strReportName).Caption
strMessageBody = "Snapshot Attached"
'send the report as a snapshot
DoCmd.SendObject acSendReport, strReportName, acFormatSNP,
strRecipient, , , strSubject, strMessageBody, False
SendReportByEmail = True
PROC_EXIT:
Exit Function
PROC_ERR:
SendReportByEmail = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function
there is something wrong with it. Can someone please review and let me how
to proceed with using it? Thanks!
The logic goes as follows:
A recordset is created which has the user's ID and Email. This should
be the same recordset as the report is based off of. The report is then
opened. Now we loop through the recordset and grab the UserID and
Email. Taking this information we then call two procedures. The first
filters the report based on the UserID(or whatever unique identifier you
have on your report - could be email). Now that the report is filtered
the second procedure is called. This time we pass the email (along with
report name) and send a snapshot of the filtered report to this
employee. Then we loop back and process the rest of the recordset.
There are many ways to send emails and many formats to send them in.
This should give you some ideas.
Matt
Private Sub cmdSendReport_Click()
On Error GoTo PROC_ERR
' Declare variables
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strAcountStatus As String
Dim strEmail As String
Dim strUserID As String
Dim fOk As Boolean
' Build our SQL string
strSQL = "SELECT UserID, Email From [Table1]"
' Set our database and recordset objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
' Open the report
DoCmd.OpenReport "rptEmployeeData", acPreview
' Turn the filter on
Reports![rptEmployeeData].FilterOn = True
' Loop the recordset
Do While Not rst.EOF
' Grab the Email string
strEmail = rst.Fields("Email")
' Grab the UserID string
strUserID = rst.Fields("UserID")
' Call the procedure used to filter the report based on the
current employee
Call FilterReport("rptEmployeeData", strUserID)
' Allow the report to refresh after filtering
DoEvents
' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rptEmployeeData", strEmail)
' Display message if failure
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " &
strEmail
End If
' Move and loop
rst.MoveNext
Loop
' Clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Private Sub FilterReport(strReportName As String, strUserID As String)
' Comments: Filters Report based on UserID parameter
' Parameters: strUserID - employee's email UserID
' strReportName - report name
On Error GoTo PROC_ERR
' Declare variables
Dim strSQL As String
'Build SQL String
strSQL = "[UserID] " & " = '" & strUserID & "'"
' Filter the report
Reports(strReportName).Filter = strSQL
' Turn the filter on
Reports(strReportName).FilterOn = True
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Function SendReportByEmail(strReportName As String, strEmail As String)
As Boolean
' Comments: Sends an email using SendObject method
' Parameters: strEmail - employee's email address
' strReportName - report name
' Returns: True of False
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
'set the mail varaibles
strRecipient = strEmail
strSubject = Reports(strReportName).Caption
strMessageBody = "Snapshot Attached"
'send the report as a snapshot
DoCmd.SendObject acSendReport, strReportName, acFormatSNP,
strRecipient, , , strSubject, strMessageBody, False
SendReportByEmail = True
PROC_EXIT:
Exit Function
PROC_ERR:
SendReportByEmail = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function