Sending Email To A List

[Wayne]

I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

[lyle]

On Jan 15, 10:43 pm, Wayne <[e-mail]:

I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

Error 2295 is a special number that means that God is unwilling to
allow messages sent with such supremely ugly code through the ether.

[Wayne]

Thanks Lyle.

[Stuart McCall]

"Wayne" <cq*******@volcanomail.comwrote in message
news:1b**********************************@t1g2000p ra.googlegroups.com...

I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

I think you need to remove the trailing semicolon from emailTo after your
loop terminates:

....
Loop
emailTo = Left(emailTo, Len(emailTo) - 1)
DoCmd.SendObject acSendNoObject, , , emailTo

[Dominic Vella]

Actually, it's -2 in this case (Space and Semicolon), being:

emailTo = Left(emailTo, Len(emailTo) - 2)

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fm*******************@news.demon.co.uk...

"Wayne" <cq*******@volcanomail.comwrote in message
news:1b**********************************@t1g2000p ra.googlegroups.com...

>I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

I think you need to remove the trailing semicolon from emailTo after your
loop terminates:

...
Loop
emailTo = Left(emailTo, Len(emailTo) - 1)
DoCmd.SendObject acSendNoObject, , , emailTo

[Stuart McCall]

"Dominic Vella" <do***********@optusnet.com.auwrote in message
news:47***********************@news.optusnet.com.a u...

Actually, it's -2 in this case (Space and Semicolon), being:

emailTo = Left(emailTo, Len(emailTo) - 2)

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fm*******************@news.demon.co.uk...

>"Wayne" <cq*******@volcanomail.comwrote in message
news:1b**********************************@t1g2000 pra.googlegroups.com...

>>I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

I think you need to remove the trailing semicolon from emailTo after your
loop terminates:

...
Loop
emailTo = Left(emailTo, Len(emailTo) - 1)
DoCmd.SendObject acSendNoObject, , , emailTo

Well spotted! Thanks.

[Fred Zuckerman]

"Wayne" <cq*******@volcanomail.comwrote in message

news:1b**********************************@t1g2000p ra.googlegroups.com...
I've searched and found the following code on this forum to send a
single email to a list of people that is contained in a table. The
table is "CustTable" and the list field is "CustEmail":

Dim db As Database, rs As Recordset, sql As String, emailTo As String
Set db = CurrentDb()
emailTo = ""
sql = "select CustEMail from CustTable"
Set rs = db.OpenRecordset(sql)
Do Until rs.EOF
If Not IsNull(rs!CustEmail) Then
'build up email addresses separated by a semicolon
emailTo = emailTo & rs!CustEmail & "; "
End If
rs.MoveNext
Loop
DoCmd.SendObject acSendNoObject, , , emailTo

When I run the code I get a runtime error 2295: Unknown message
recipient(s); the message was not sent. Is there some obvious reason
that this code is failing? Any help is appreciated.

1. Your email has no subject nor message. Possibly your email system is
balking at this.

2. Try checking the actual value for emailTo (Debug.Print). You might have a
record in your query that is not null but has a zero-length string, or
bizarre characters, or ???

3. You might need a rs.MoveFirst before your loop begins

Fred Zuckerman

[Wayne]

Thanks Stuart and Dominic. It works well.

[Stuart McCall]

"Wayne" <cq*******@volcanomail.comwrote in message
news:2f**********************************@i29g2000 prf.googlegroups.com...

Thanks Stuart and Dominic. It works well.

Glad that fixed it. Now I can show you a much cleaner, more efficient way to
build your recipient string:

sql = "select CustEMail from CustTable"
emailTo = CurrentProject.Connection.Execute(sql).GetString(2 , , ";")
DoCmd.SendObject acSendNoObject, , , emailTo

That replaces your loop with a one-liner, making use of Access' built-in ADO
Connection object. The literal number 2 is the value of the constant
adClipString, which, if you want to use it, requires a reference to ADO be
set, however, using the literal it works without a reference.

This technique was demonstrated recently by Lyle Fairfield.