Re: Concencate Emails, Error type declaration does not match

Thanks. That worked.
Marcie

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"bymarce" <bymarce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3756E98B-E010-43A0-BFE9-7DC672655EE8@xxxxxxxxxxxxxxxx
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
george.fultz@xxxxxxxxxxxx; marcie.roberts@xxxxxxxxxxxx;


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" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"bymarce" <bymarce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AA141357-F0AA-4DCF-A8AB-EE1DE6A86A2B@xxxxxxxxxxxxxxxx
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, "marcie.roberts@xxxxxxxxxxxx", ,
MySubject,
MyMessage, True

End Sub

"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"bymarce" <bymarce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC2001F0-F63E-4CB7-8FE5-F8A69271A2B5@xxxxxxxxxxxxxxxx
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" wrote:

Try either

ToVar = ToVar & rs!Email & "; "

or

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"bymarce" <bymarce@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CEC287AC-1391-4390-95F6-622AFFF15C85@xxxxxxxxxxxxxxxx
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, "marcie.roberts@xxxxxxxxxxxx", ,
MySubject,
MyMessage, True

End Sub












.

Relevant Pages