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

Hi Matt,

What exactly is your question? What do you think is wrong with the code?

Damian.

"Please help James" wrote:

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

I tried to enter this code and it did not work, nothing happened....I entered
it under the report VB.

"Damian S" wrote:

> Hi Matt,
>
> What exactly is your question? What do you think is wrong with the code?
>
> Damian.
>
> "Please help James" wrote:
>
> > 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
> >

Ah, well, the code appears to go behind a form. The form has a button called
cmdSendReport and uses the two functions to perform what is required. You
will need to update the code to put your own report names and where
conditions in there...

Hope this helps.

Damian.

"Please help James" wrote:

> I tried to enter this code and it did not work, nothing happened....I entered
> it under the report VB.
>
> "Damian S" wrote:
>
> > Hi Matt,
> >
> > What exactly is your question? What do you think is wrong with the code?
> >
> > Damian.
> >
> > "Please help James" wrote:
> >
> > > 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
> > >

Damian the code gets hung up at

"Function SendReportByEmail(strReportName As String, strEmail As String)
As Boolean

Any suggestions?

"Damian S" wrote:

> Ah, well, the code appears to go behind a form. The form has a button called
> cmdSendReport and uses the two functions to perform what is required. You
> will need to update the code to put your own report names and where
> conditions in there...
>
> Hope this helps.
>
> Damian.
>
> "Please help James" wrote:
>
> > I tried to enter this code and it did not work, nothing happened....I entered
> > it under the report VB.
> >
> > "Damian S" wrote:
> >
> > > Hi Matt,
> > >
> > > What exactly is your question? What do you think is wrong with the code?
> > >
> > > Damian.
> > >
> > > "Please help James" wrote:
> > >
> > > > 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
> > > >

Damian I keep getting a "Invalid use of Null" whenever I run the program, any
suggestions?

"Damian S" wrote:

> Ah, well, the code appears to go behind a form. The form has a button called
> cmdSendReport and uses the two functions to perform what is required. You
> will need to update the code to put your own report names and where
> conditions in there...
>
> Hope this helps.
>
> Damian.
>
> "Please help James" wrote:
>
> > I tried to enter this code and it did not work, nothing happened....I entered
> > it under the report VB.
> >
> > "Damian S" wrote:
> >
> > > Hi Matt,
> > >
> > > What exactly is your question? What do you think is wrong with the code?
> > >
> > > Damian.
> > >
> > > "Please help James" wrote:
> > >
> > > > 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
> > > >