Concencate Emails, Error type declaration does not match

B

bymarce

I found this code on the message boards to build a string of email addresses
but I'm getting an error "Type Declaration does not match declared data type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; " How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub
 
D

Douglas J. Steele

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "
 
B

bymarce

Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; " How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; "
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

bymarce

Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", , MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

Douglas J. Steele said:
Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & "; "
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You're missing spaces between Data and INNER JOIN, between ON and Data and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. Both seem to work but I'm getting a different error now, "Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & ";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

bymarce

Thanks. I didn't know what the underscores ment. Now it's saying "Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

Douglas J. Steele said:
Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now, "Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") & ";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

One possibility is the semi-colon at the end. Try adding Left(ToVar,
Len(ToVar)-1):

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop
ToVar = Left$(ToVar, Len(ToVar) - 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. I didn't know what the underscores ment. Now it's saying
"Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print
ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN
Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data
and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is
a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to
ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. This is how I have the code now per your advise. Now I'm
getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN
Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

:

Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the
quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now,
"Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match
declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") &
";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

bymarce

Thanks. That worked.
Marcie

Douglas J. Steele said:
One possibility is the semi-colon at the end. Try adding Left(ToVar,
Len(ToVar)-1):

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop
ToVar = Left$(ToVar, Len(ToVar) - 1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


bymarce said:
Thanks. I didn't know what the underscores ment. Now it's saying
"Runtime
Error 2295. Unknkown Message Recipients...". When I type Debug.Print
ToVar
it gives the right string.

Debug.Print ToVar
(e-mail address removed); (e-mail address removed);


Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data " & "INNER JOIN
Personel
ON " & "Data.TestAssignedTo = Personel.Initials " & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, , , MySubject, MyMessage, True

End Sub

Douglas J. Steele said:
You're missing spaces between Data and INNER JOIN, between ON and Data
and
between Initials and WHERE.

There was a reason I typed my response the way I did! Space underscore is
a
line continuation character. By splitting it into "bite size" pieces, I
ensured that there would be no problem with word-wrap in my post. I also
made sure that there was a space before each of the closing quotes to
ensure
that the resultant string would be correct.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. This is how I have the code now per your advise. Now I'm
getting
the error "Syntax Error in From Clause" wth the same line highlighted.
Marcie

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data" & "INNER JOIN
Personel
ON" & "Data.TestAssignedTo = Personel.Initials" & "WHERE Data.MLO='" &
[Forms]![WorkAssignments]![fMLO] & "'"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs.Fields("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub

:

Sorry: didn't look closely enough at your code.

You need to put the reference to the form control outside of the
quotes:

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO=" & [Forms]![WorkAssignments]![fMLO]

That assumes that MLO is a numeric field. If it's text, you'll need

sql = "SELECT DISTINCT Personel.Email FROM Data " & _
"INNER JOIN Personel ON " & _
"Data.TestAssignedTo = Personel.Initials " & _
"WHERE Data.MLO='" & [Forms]![WorkAssignments]![fMLO] & "'"

where that last line, exagerated for clarity, is

"WHERE Data.MLO=' " & [Forms]![WorkAssignments]![fMLO] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks. Both seem to work but I'm getting a different error now,
"Too
few
parameters. Expected 1." on the line Set rs = db.OpenRecordset(sql,
dbOpenSnapshot) .
Marcie

:

Try either

ToVar = ToVar & rs!Email & "; "

or

ToVar = ToVar & rs.Fields("Email") & "; "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found this code on the message boards to build a string of email
addresses
but I'm getting an error "Type Declaration does not match
declared
data
type"
and rs! is highlighted in the line ToVar = ToVar & rs!("Email") &
";
"
How
can I fix this

Private Sub Email_Work_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim ToVar As String
Dim sql As String
sql = "SELECT DISTINCT Personel.Email FROM Data INNER JOIN
Personel
ON
Data.TestAssignedTo = Personel.Initials WHERE
(((Data.MLO)=[Forms]![WorkAssignments]![fMLO]))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
ToVar = ToVar & rs!("Email") & "; "
rs.MoveNext
Loop

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)", ToVar, "(e-mail address removed)", ,
MySubject,
MyMessage, True

End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Send Records from Form, Method or Data Error not Found" 0
Dlookup invalid use of null 6
Email a group based on records in a form 11
DLookup Syntax for text criteria 3
"Procedure declaration does not match description" error, _dblclic 6
Run-time error 430: Class does not support Automation..... 4
Query error "type mis-match" 8
Compile error - User defined type not defined 1

Top