Email Mail Merge Code Run Time Error

  • Thread starter Thread starter Confused
  • Start date Start date
C

Confused

I have this Function to run on a query called "EmailQuery" . This code opens
my email and populates all of the names in the BCC field. Now when I click
the Cmd button I get a Run-Time Error with the message 'Too Few Parameters.
Expected 1.' The line it highlights in the code below is Set RS =
DBEngine(0)(0).OpenRecordset(sSQL)

I added a subquery to the SQL to filter the form EmailQuery when it opens
to this query, but I didn't do anything to the code below ( not sure if this
is related to the problem).


Private Sub Command14_Click()
DoCmd.SendObject acSendNoObject, , , , , EmailonQ1, "Subject", "Message",
True, False

End Sub

Public Function EmailonQ1()
Dim RS As DAO.Recordset
Dim tmpEM As String
Dim sSQL As String
sSQL = "SELECT emailquery.emailaddress " & _
"FROM emailquery where [send to?] = -1 ;"
Set RS = DBEngine(0)(0).OpenRecordset(sSQL)
RS.MoveFirst
Do While Not RS.EOF
tmpEM = tmpEM & Nz(RS.Fields(0).Value, "") & ";"
RS.MoveNext
Loop
EmailonQ = tmpEM
Set RS = Nothing

End Function

Much appreciate any help resolving...
 
The Select Query works fine. It asks for a parameter value, which is what I
built. The EmailQuery opens fine and displays all of the records or the
records as a result of the parameter classification. The code was working
fine and I didn't change anything on the function. It's just now the only
thing different is there is a parameter (subquery) on the SQL Statement.
I'm not sure if this is related to the problem, because I haven't ran the
code in several days.

ruralguy via AccessMonster.com said:
The error indicates you ave something misspelled in the SQL string. Try and
run that Select query directly and see what happens.
I have this Function to run on a query called "EmailQuery" . This code opens
my email and populates all of the names in the BCC field. Now when I click
the Cmd button I get a Run-Time Error with the message 'Too Few Parameters.
Expected 1.' The line it highlights in the code below is Set RS =
DBEngine(0)(0).OpenRecordset(sSQL)

I added a subquery to the SQL to filter the form EmailQuery when it opens
to this query, but I didn't do anything to the code below ( not sure if this
is related to the problem).

Private Sub Command14_Click()
DoCmd.SendObject acSendNoObject, , , , , EmailonQ1, "Subject", "Message",
True, False

End Sub

Public Function EmailonQ1()
Dim RS As DAO.Recordset
Dim tmpEM As String
Dim sSQL As String
sSQL = "SELECT emailquery.emailaddress " & _
"FROM emailquery where [send to?] = -1 ;"
Set RS = DBEngine(0)(0).OpenRecordset(sSQL)
RS.MoveFirst
Do While Not RS.EOF
tmpEM = tmpEM & Nz(RS.Fields(0).Value, "") & ";"
RS.MoveNext
Loop
EmailonQ = tmpEM
Set RS = Nothing

End Function

Much appreciate any help resolving...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
I removed the parameter (the where statement on the SQL string) and now the
code runs fine. It opens my email and populates all of the names. Just need
to know if I can make it still work with the subquery? Or another way to
accomplish the same thing? Just trying to populate names automatically in a
BCC message in email. Thanks!

Confused said:
The Select Query works fine. It asks for a parameter value, which is what I
built. The EmailQuery opens fine and displays all of the records or the
records as a result of the parameter classification. The code was working
fine and I didn't change anything on the function. It's just now the only
thing different is there is a parameter (subquery) on the SQL Statement.
I'm not sure if this is related to the problem, because I haven't ran the
code in several days.

ruralguy via AccessMonster.com said:
The error indicates you ave something misspelled in the SQL string. Try and
run that Select query directly and see what happens.
I have this Function to run on a query called "EmailQuery" . This code opens
my email and populates all of the names in the BCC field. Now when I click
the Cmd button I get a Run-Time Error with the message 'Too Few Parameters.
Expected 1.' The line it highlights in the code below is Set RS =
DBEngine(0)(0).OpenRecordset(sSQL)

I added a subquery to the SQL to filter the form EmailQuery when it opens
to this query, but I didn't do anything to the code below ( not sure if this
is related to the problem).

Private Sub Command14_Click()
DoCmd.SendObject acSendNoObject, , , , , EmailonQ1, "Subject", "Message",
True, False

End Sub

Public Function EmailonQ1()
Dim RS As DAO.Recordset
Dim tmpEM As String
Dim sSQL As String
sSQL = "SELECT emailquery.emailaddress " & _
"FROM emailquery where [send to?] = -1 ;"
Set RS = DBEngine(0)(0).OpenRecordset(sSQL)
RS.MoveFirst
Do While Not RS.EOF
tmpEM = tmpEM & Nz(RS.Fields(0).Value, "") & ";"
RS.MoveNext
Loop
EmailonQ = tmpEM
Set RS = Nothing

End Function

Much appreciate any help resolving...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
When I remove the Where clause on the below SQL, the function runs fine.
More details if needed -The where clause restricts the form EmailQuery to the
value in CboEmployees, which is based on the query of the same name-
EmailQuery. The form opens and I hit the command button to send an email to
those restricted records and I get the error...

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.CLECID,
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.CLECID
WHERE (((Contacts.EmailAddress) Is Not Null) AND ((CLECS2.CLECID) In (SELECT
CLECID FROM tblEmployeeAssignments WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContactsSubform]![cboEmployees]
Or[Forms]![CLECS2wContactsSubform]![cboEmployees] is null )));


ruralguy via AccessMonster.com said:
What is the SubQuery?
I removed the parameter (the where statement on the SQL string) and now the
code runs fine. It opens my email and populates all of the names. Just need
to know if I can make it still work with the subquery? Or another way to
accomplish the same thing? Just trying to populate names automatically in a
BCC message in email. Thanks!
The Select Query works fine. It asks for a parameter value, which is what I
built. The EmailQuery opens fine and displays all of the records or the
Click to expand...
[quoted text clipped - 41 lines]
Much appreciate any help resolving...
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
I copied and pasted. I added the space and got the same results (really
hoping that would have fixed it). Maybe something in the function needs to
be changed? The where clause for some reason interfereing with the function
code?

ruralguy via AccessMonster.com said:
Did you Copy and Paste the SQL or type it again because the last OR does not
have a space after it.
When I remove the Where clause on the below SQL, the function runs fine.
More details if needed -The where clause restricts the form EmailQuery to the
value in CboEmployees, which is based on the query of the same name-
EmailQuery. The form opens and I hit the command button to send an email to
those restricted records and I get the error...

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.CLECID,
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.CLECID
WHERE (((Contacts.EmailAddress) Is Not Null) AND ((CLECS2.CLECID) In (SELECT
CLECID FROM tblEmployeeAssignments WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContactsSubform]![cboEmployees]
Or[Forms]![CLECS2wContactsSubform]![cboEmployees] is null )));
What is the SubQuery?
Click to expand...
[quoted text clipped - 9 lines]
Much appreciate any help resolving...
Click to expand...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
This was in the help section. This sort of looks like it's expecting the
parameter again...Maybe some way to make the command button not look at the
subquery again but just run the code....Grasping at straws.

Too few parameters. Expected <number>. (Error 3061)
This parameter query requires the specified number of parameters, which you
did not supply. Provide the expected number of parameters, and run the query
again.

In some cases, this message is generated when unknown field names or
expressions are interpreted as query parameters. Be sure to enclose field
names containing spaces or punctuation with square brackets [ ].


Confused said:
I copied and pasted. I added the space and got the same results (really
hoping that would have fixed it). Maybe something in the function needs to
be changed? The where clause for some reason interfereing with the function
code?

ruralguy via AccessMonster.com said:
Did you Copy and Paste the SQL or type it again because the last OR does not
have a space after it.
When I remove the Where clause on the below SQL, the function runs fine.
More details if needed -The where clause restricts the form EmailQuery to the
value in CboEmployees, which is based on the query of the same name-
EmailQuery. The form opens and I hit the command button to send an email to
those restricted records and I get the error...

SELECT CLECS2.[CLEC Name], Contacts.[First Name], Contacts.[Last Name],
Contacts.EmailAddress, Contacts.[Send to?], Contacts.CLECID,
Contacts.[Contact ID]
FROM CLECS2 RIGHT JOIN Contacts ON CLECS2.CLECID = Contacts.CLECID
WHERE (((Contacts.EmailAddress) Is Not Null) AND ((CLECS2.CLECID) In (SELECT
CLECID FROM tblEmployeeAssignments WHERE tblEmployeeAssignments.EmployeeID =
[Forms]![CLECS2wContactsSubform]![cboEmployees]
Or[Forms]![CLECS2wContactsSubform]![cboEmployees] is null )));

What is the SubQuery?

[quoted text clipped - 9 lines]

Much appreciate any help resolving...
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Back
Top