Email query

[Guest]

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[Guest]

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

 

[Guest]

Eric, where do I enter this code...in a form command button or in the report
VB window?

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[Guest]

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the
expert MVP's can help you. I just figured I would give you what I had.

Eric, where do I enter this code...in a form command button or in the report
VB window?

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[Guest]

Thanks Eric....hopefully an MVP will help out.

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the
expert MVP's can help you. I just figured I would give you what I had.

Eric, where do I enter this code...in a form command button or in the report
VB window?

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

:

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[biganthony via AccessMonster.com]

James,

I used the code above and it works.

Put the cmdSendReport_Click() procedure behind a button on a form.

I then put the FilterReport procedure in a module but changed it to Public
(rather than private).

I then put the SendReportByEmail function in a module as well.

It works for me.
Anthony

Thanks Eric....hopefully an MVP will help out.

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the

[quoted text clipped - 169 lines]

 

[Guest]

Eric, I get hung up at "Function SendReportByEmail(strReportName As String,
strEmail As String)
As Boolean"

How did you over come this problem?

Thanks Eric....hopefully an MVP will help out.

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the
expert MVP's can help you. I just figured I would give you what I had.

Eric, where do I enter this code...in a form command button or in the report
VB window?

:

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

:

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[Guest]

Anthony, I get hung up at "Function SendReportByEmail(strReportName As
String,
strEmail As String)
As Boolean"

How did you over come this problem?

James,

I used the code above and it works.

Put the cmdSendReport_Click() procedure behind a button on a form.

I then put the FilterReport procedure in a module but changed it to Public
(rather than private).

I then put the SendReportByEmail function in a module as well.

It works for me.
Anthony

Thanks Eric....hopefully an MVP will help out.

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the

[quoted text clipped - 169 lines]

Any assistance would be very much appreciated. Thanks!

 

[biganthony via AccessMonster.com]

James,

What problem did you get? It works for me as it is.

Anthony

Anthony, I get hung up at "Function SendReportByEmail(strReportName As
String,
strEmail As String)
As Boolean"

How did you over come this problem?

James,

[quoted text clipped - 17 lines]

 

[Guest]

Anthony, it did work....it just took some time for me to try to understand
the process. One more thing though....do you know how to make the program
not send the email if there is no data in the report?

Eric, I get hung up at "Function SendReportByEmail(strReportName As String,
strEmail As String)
As Boolean"

How did you over come this problem?

Thanks Eric....hopefully an MVP will help out.

Sorry, I have not used this. I picked this up from this newsgroup. I have
not used this but tThe code appears to be pretty clear. Hopefully one of the
expert MVP's can help you. I just figured I would give you what I had.

:

Eric, where do I enter this code...in a form command button or in the report
VB window?

:

I got this from MATT in a previous post. I have not tested it.

Good luck

In this example I'm assuming that you only want to send a filtered
report to the employee so he/she only sees his/her data.

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

:

1. Is there a way to get the SendObject command to read email addresses from
a table?

2. Is there a way to seperate a report so that each header gets exported
to a different snapshot allowing a person to only get the information
that pertains to a query selected, referencing a table which links the header
with an email address.

Any assistance would be very much appreciated. Thanks!

 

[biganthony via AccessMonster.com]

James, glad it worked.

Not too sure about if the form is blank.

Could you test if the report has no data using the OnNoData property of the
form?

Could you have something like this before the line where the email gets sent?
I don't know how to test for a report that is blank, so someone else may be
able to help.


If Reports![rptEmployeeData].OnNoData = True

then

'do nothing

else

' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rptEmployeeData", strEmail)

endif

Will be interested in knowing if this worked!

All the best,
Anthony

Anthony, it did work....it just took some time for me to try to understand
the process. One more thing though....do you know how to make the program
not send the email if there is no data in the report?

Eric, I get hung up at "Function SendReportByEmail(strReportName As String,
strEmail As String)

[quoted text clipped - 177 lines]

 

[Guest]

Anthony I keep getting an "Invalid use of Null"...can you shed any light to
my problem? Also, I'd like to insert the following code; Where would be the
place to insert it incorporating the previous code? (this is to use a date
parameter). Thanks!

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptEmployeeData"
strField = "dateactive"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

James, glad it worked.

Not too sure about if the form is blank.

Could you test if the report has no data using the OnNoData property of the
form?

Could you have something like this before the line where the email gets sent?
I don't know how to test for a report that is blank, so someone else may be
able to help.


If Reports![rptEmployeeData].OnNoData = True

then

'do nothing

else

' Send the snapshot of the report to the current employee
fOk = SendReportByEmail("rptEmployeeData", strEmail)

endif

Will be interested in knowing if this worked!

All the best,
Anthony

Anthony, it did work....it just took some time for me to try to understand
the process. One more thing though....do you know how to make the program
not send the email if there is no data in the report?

Eric, I get hung up at "Function SendReportByEmail(strReportName As String,
strEmail As String)

[quoted text clipped - 177 lines]

Any assistance would be very much appreciated. Thanks!

 

[biganthony via AccessMonster.com]

James,

When are you getting the "invalid use of Null" message? When the report is
blank?

As for your code below, would it go into the Filter report procedure? Could
you somehow join the filter you have created (strWhere) in the new code you
have to the filter already in the filter report procedure (strSQL)?

Could you have something like:

strSQL= strSQL + strWhere (I'm not sure about this though)

before the line: Reports(strReportName).Filter = strSQL

I really don't know!

Sorry I'm not much use.

Anthony I keep getting an "Invalid use of Null"...can you shed any light to
my problem? Also, I'd like to insert the following code; Where would be the
place to insert it incorporating the previous code? (this is to use a date
parameter). Thanks!

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptEmployeeData"
strField = "dateactive"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

James, glad it worked.

[quoted text clipped - 34 lines]

 

[Guest]

Anthony, I figured it out....the "invalid use of null" was caused by some
blank lines in "Table1" and as for adding the two codes together, I used the
following code,

Me.cmdSendReport.Requery

(sooooo simple). Thanks for stearing me in the right direction...you really
helped me out!!!

James,

When are you getting the "invalid use of Null" message? When the report is
blank?

As for your code below, would it go into the Filter report procedure? Could
you somehow join the filter you have created (strWhere) in the new code you
have to the filter already in the filter report procedure (strSQL)?

Could you have something like:

strSQL= strSQL + strWhere (I'm not sure about this though)

before the line: Reports(strReportName).Filter = strSQL

I really don't know!

Sorry I'm not much use.

Anthony I keep getting an "Invalid use of Null"...can you shed any light to
my problem? Also, I'd like to insert the following code; Where would be the
place to insert it incorporating the previous code? (this is to use a date
parameter). Thanks!

Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strReport = "rptEmployeeData"
strField = "dateactive"
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

James, glad it worked.

[quoted text clipped - 34 lines]

Any assistance would be very much appreciated. Thanks!

 

[biganthony via AccessMonster.com]

Glad it worked out.
Anthony

Anthony, I figured it out....the "invalid use of null" was caused by some
blank lines in "Table1" and as for adding the two codes together, I used the
following code,

Me.cmdSendReport.Requery

(sooooo simple). Thanks for stearing me in the right direction...you really
helped me out!!!

James,

[quoted text clipped - 49 lines]