Email a group based on records in a form

[bymarce]

I have a form called "WorkAssginments". There are unbound combo boxes at the
top that are used to build the form's filter. I want to use the forms filter
to select email addresses from a table that is related to the table table
onwhich the form is built. The form "WorkAssignments" is based on the data
table and has a field "AssignedTo" that is related to "Initials" in the
personel Table. One post I read said to build a query to select the email
adresses and use it in the following code. When click the button to run this
code I get error 3061 "Too Few Parameters. Expected 1" and the Set rst =
db.OpenRecordset("qryEmails") line is highlighted. How can I get this to
work or is there a way to use the filter that is already built in the form
rather than a seperate query? Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmails")
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub

 

[Allen Browne]

The request for a parameter indicates that there is a name in the query that
Access can't resolve.

If the query contains a parameter such as:
[Forms]![form]1![text0]
then Access passes it to the Expression Service which looks for the text box
on the form. The ES is not available when you OpenRecordset in code, so you
have to either explicitly supply the parameter, or redesign the query so it
does not use a parameter. I prefer the redesign, so the query is completely
independent of the form.

If you prefer to supply the parameter in code, this is the approach:
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![form]1![text0]") = [Forms]![form]1![text0]
Set rst = qdf.OpenRecordset

 

[bymarce]

Thanks for the help. How would I redesign the query to return the proper
email addresses without using a parameter? Also is the 1 in your code after
[form] a typo?

The request for a parameter indicates that there is a name in the query that
Access can't resolve.

If the query contains a parameter such as:
[Forms]![form]1![text0]
then Access passes it to the Expression Service which looks for the text box
on the form. The ES is not available when you OpenRecordset in code, so you
have to either explicitly supply the parameter, or redesign the query so it
does not use a parameter. I prefer the redesign, so the query is completely
independent of the form.

If you prefer to supply the parameter in code, this is the approach:
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![form]1![text0]") = [Forms]![form]1![text0]
Set rst = qdf.OpenRecordset
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called "WorkAssginments". There are unbound combo boxes at
the
top that are used to build the form's filter. I want to use the forms
filter
to select email addresses from a table that is related to the table table
onwhich the form is built. The form "WorkAssignments" is based on the
data
table and has a field "AssignedTo" that is related to "Initials" in the
personel Table. One post I read said to build a query to select the email
adresses and use it in the following code. When click the button to run
this
code I get error 3061 "Too Few Parameters. Expected 1" and the Set rst =
db.OpenRecordset("qryEmails") line is highlighted. How can I get this to
work or is there a way to use the filter that is already built in the form
rather than a seperate query? Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmails")
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

 

[bymarce]

I have the code set up this way now and it runs without error but when the
email opens the send to field blank. Thanks.

Private Sub Email_Work_Click()

Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim SendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub

The request for a parameter indicates that there is a name in the query that
Access can't resolve.

If the query contains a parameter such as:
[Forms]![form]1![text0]
then Access passes it to the Expression Service which looks for the text box
on the form. The ES is not available when you OpenRecordset in code, so you
have to either explicitly supply the parameter, or redesign the query so it
does not use a parameter. I prefer the redesign, so the query is completely
independent of the form.

If you prefer to supply the parameter in code, this is the approach:
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![form]1![text0]") = [Forms]![form]1![text0]
Set rst = qdf.OpenRecordset
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called "WorkAssginments". There are unbound combo boxes at
the
top that are used to build the form's filter. I want to use the forms
filter
to select email addresses from a table that is related to the table table
onwhich the form is built. The form "WorkAssignments" is based on the
data
table and has a field "AssignedTo" that is related to "Initials" in the
personel Table. One post I read said to build a query to select the email
adresses and use it in the following code. When click the button to run
this
code I get error 3061 "Too Few Parameters. Expected 1" and the Set rst =
db.OpenRecordset("qryEmails") line is highlighted. How can I get this to
work or is there a way to use the filter that is already built in the form
rather than a seperate query? Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmails")
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

 

[Allen Browne]

Debug.Print SendTo

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have the code set up this way now and it runs without error but when the
email opens the send to field blank. Thanks.

Private Sub Email_Work_Click()

Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim SendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

The request for a parameter indicates that there is a name in the query
that
Access can't resolve.

If the query contains a parameter such as:
[Forms]![form]1![text0]
then Access passes it to the Expression Service which looks for the text
box
on the form. The ES is not available when you OpenRecordset in code, so
you
have to either explicitly supply the parameter, or redesign the query so
it
does not use a parameter. I prefer the redesign, so the query is
completely
independent of the form.

If you prefer to supply the parameter in code, this is the approach:
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![form]1![text0]") = [Forms]![form]1![text0]
Set rst = qdf.OpenRecordset
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called "WorkAssginments". There are unbound combo boxes
at
the
top that are used to build the form's filter. I want to use the forms
filter
to select email addresses from a table that is related to the table
table
onwhich the form is built. The form "WorkAssignments" is based on the
data
table and has a field "AssignedTo" that is related to "Initials" in the
personel Table. One post I read said to build a query to select the
email
adresses and use it in the following code. When click the button to
run
this
code I get error 3061 "Too Few Parameters. Expected 1" and the Set rst
=
db.OpenRecordset("qryEmails") line is highlighted. How can I get this
to
work or is there a way to use the filter that is already built in the
form
rather than a seperate query? Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmails")
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

 

[bymarce]

Thanks. I changed that line and it still doesn't fill in the send to.
Marcie

Debug.Print SendTo

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have the code set up this way now and it runs without error but when the
email opens the send to field blank. Thanks.

Private Sub Email_Work_Click()

Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim SendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

The request for a parameter indicates that there is a name in the query
that
Access can't resolve.

If the query contains a parameter such as:
[Forms]![form]1![text0]
then Access passes it to the Expression Service which looks for the text
box
on the form. The ES is not available when you OpenRecordset in code, so
you
have to either explicitly supply the parameter, or redesign the query so
it
does not use a parameter. I prefer the redesign, so the query is
completely
independent of the form.

If you prefer to supply the parameter in code, this is the approach:
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![form]1![text0]") = [Forms]![form]1![text0]
Set rst = qdf.OpenRecordset
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form called "WorkAssginments". There are unbound combo boxes
at
the
top that are used to build the form's filter. I want to use the forms
filter
to select email addresses from a table that is related to the table
table
onwhich the form is built. The form "WorkAssignments" is based on the
data
table and has a field "AssignedTo" that is related to "Initials" in the
personel Table. One post I read said to build a query to select the
email
adresses and use it in the following code. When click the button to
run
this
code I get error 3061 "Too Few Parameters. Expected 1" and the Set rst
=
db.OpenRecordset("qryEmails") line is highlighted. How can I get this
to
work or is there a way to use the filter that is already built in the
form
rather than a seperate query? Thanks.
Marcie

Private Sub Email_Work_Click()

Dim SendTo As String
Dim rst As dao.Recordset
Dim db As dao.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("qryEmails")
With rst
If Not .BOF And .EOF Then
Do Until .EOF
SendTo = SendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO &
"."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", SendTo, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

 

[Allen Browne]

You have some debugging to do.
The Debug.Print will help you identify what's in the variable.
You need the right info in the variable get get SendTo to work.

 

[bymarce]

Thanks. When I put Debug.Print SendTo in the immediate window nothing shows
up.

 

[Allen Browne]

That makes good sense of why nothing shows up in the email's To box.

Now you need to track back why there is nothing in this variable. I strongly
suggest that you use Option Explicit in all modules. This will help you
track down things like mis-spelling a variable, or clashes between
variables. Dim the variable inside the procedure. Try changing the name,
e.g. use:
Dim strSendTo
and replace each SendTo with strSendTo in this procedure.

 

[bymarce]

I put option explicit at the top of the module. I dimentioned a few more
variables. My query has two fields, the MLO from the data table and email
from the personel table. The criteria for MLO is
[Forms]![WorkAssignments]![fMLO] . At one point Debug.Print strSendTo
produced the MLO rather than any emails. Do I need to pass a parameter for
the email? Thanks.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim strSendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
strSendTo = strSendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", strSendTo, "(e-mail address removed)", ,
MySubject, MyMessage, True

End Sub

 

[Allen Browne]

I'm not sure there's any more I can contribute here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I put option explicit at the top of the module. I dimentioned a few more
variables. My query has two fields, the MLO from the data table and email
from the personel table. The criteria for MLO is
[Forms]![WorkAssignments]![fMLO] . At one point Debug.Print strSendTo
produced the MLO rather than any emails. Do I need to pass a parameter
for
the email? Thanks.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim strSendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
strSendTo = strSendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", strSendTo, "(e-mail address removed)", ,
MySubject, MyMessage, True

End Sub

That makes good sense of why nothing shows up in the email's To box.

Now you need to track back why there is nothing in this variable. I
strongly
suggest that you use Option Explicit in all modules. This will help you
track down things like mis-spelling a variable, or clashes between
variables. Dim the variable inside the procedure. Try changing the name,
e.g. use:
Dim strSendTo
and replace each SendTo with strSendTo in this procedure.

 

[bymarce]

Thanks. I've gotten alot of help from your posts.
Marcie

I'm not sure there's any more I can contribute here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I put option explicit at the top of the module. I dimentioned a few more
variables. My query has two fields, the MLO from the data table and email
from the personel table. The criteria for MLO is
[Forms]![WorkAssignments]![fMLO] . At one point Debug.Print strSendTo
produced the MLO rather than any emails. Do I need to pass a parameter
for
the email? Thanks.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("qryEmails")
qdf.Parameters("[Forms]![WorkAssignments]![fMLO]") =
[Forms]![WorkAssignments]![fMLO]
Set rst = qdf.OpenRecordset
Dim strSendTo As String
With rst
If Not .BOF And .EOF Then
Do Until .EOF
strSendTo = strSendTo & .Fields("Email") & "; "
.MoveNext
Loop
End If
.Close
End With

Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
Debug.Print strWhere

Dim MySubject As String, MyMessage As String
MySubject = Me.MLO
MyMessage = "Please complete the following tests for " & Me.MLO & "."
DoCmd.SendObject acSendReport, "rptWorkAssignments",
"SnapshotFormat(*.snp)", strSendTo, "(e-mail address removed)", ,
MySubject, MyMessage, True

End Sub

That makes good sense of why nothing shows up in the email's To box.

Now you need to track back why there is nothing in this variable. I
strongly
suggest that you use Option Explicit in all modules. This will help you
track down things like mis-spelling a variable, or clashes between
variables. Dim the variable inside the procedure. Try changing the name,
e.g. use:
Dim strSendTo
and replace each SendTo with strSendTo in this procedure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. When I put Debug.Print SendTo in the immediate window nothing
shows
up.

:

You have some debugging to do.
The Debug.Print will help you identify what's in the variable.
You need the right info in the variable get get SendTo to work.

Thanks. I changed that line and it still doesn't fill in the send
to.
Marcie

:

Debug.Print SendTo