Sending Email To A List

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.

Re: Sending Email To A List

On Jan 15, 10:43 pm, Wayne wrote:
> 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.

Re: Sending Email To A List

Thanks Lyle.

Re: Sending Email To A List

"Wayne" wrote in message
news:1b4fe37b-667e-41af-bc28-9a0e2bc052b1@t1g2000pra.googleg roups.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

Re: Sending Email To A List

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

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

"Stuart McCall" wrote in message
news:fmkjhk$mgd$1$8300dec7@news.demon.co.uk...
> "Wayne" wrote in message
> news:1b4fe37b-667e-41af-bc28-9a0e2bc052b1@t1g2000pra.googleg roups.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
>
>

Re: Sending Email To A List

"Dominic Vella" wrote in message
news:478df9d7$0$20842$afc38c87@news.optusnet.com.au...
> Actually, it's -2 in this case (Space and Semicolon), being:
>
> emailTo = Left(emailTo, Len(emailTo) - 2)
>
> "Stuart McCall" wrote in message
> news:fmkjhk$mgd$1$8300dec7@news.demon.co.uk...
>> "Wayne" wrote in message
>> news:1b4fe37b-667e-41af-bc28-9a0e2bc052b1@t1g2000pra.googleg roups.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.

Re: Sending Email To A List

> "Wayne" wrote in message
> news:1b4fe37b-667e-41af-bc28-9a0e2bc052b1@t1g2000pra.googleg roups.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

Re: Sending Email To A List

Thanks Stuart and Dominic. It works well.

Re: Sending Email To A List

"Wayne" wrote in message
news:2fd79ae7-eb63-46fc-bb8d-8dba376b44ac@i29g2000prf.google groups.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.