Help with VB

P

Please help James

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
 
D

Damian S

Hi Matt,

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

Damian.

Please help James said:
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
 
P

Please help James

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

Damian S said:
Hi Matt,

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

Damian.

Please help James said:
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
 
D

Damian S

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 said:
I tried to enter this code and it did not work, nothing happened....I entered
it under the report VB.

Damian S said:
Hi Matt,

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

Damian.

Please help James said:
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
 
P

Please help James

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

Damian S said:
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 said:
I tried to enter this code and it did not work, nothing happened....I entered
it under the report VB.

Damian S said:
Hi Matt,

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

Damian.

:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top