Access2003 + CDO + TO: (how do I pull TO: list from table)

Hi All ~ First time posting.

I have a module that works ... it contains:

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.From = "PSS Mass Mail Mailbox"
.Sender = "hughmcmenamin@usa.com"
.To = "hughmcmenamin@usa.com"
.Subject = "Hugh Message"

How can I get it to pull email addresses from a table or query?
I want it to send "To" this list rather than just one email address.
Both in same database. Table1 & Query1 (field: email)
Your help is appreciated
Hugh

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

"HughMcMenamin" wrote in message news:7df937d650446@uwe...
> Hi All ~ First time posting.
>
> I have a module that works ... it contains:
>
> Set objCDOMessage = CreateObject("CDO.Message")
> With objCDOMessage
> Set .Configuration = objCDOConfig
> .From = "PSS Mass Mail Mailbox"
> .Sender = "hughmcmenamin@usa.com"
> .To = "hughmcmenamin@usa.com"
> .Subject = "Hugh Message"
>
> How can I get it to pull email addresses from a table or query?
> I want it to send "To" this list rather than just one email address.
> Both in same database. Table1 & Query1 (field: email)
> Your help is appreciated
> Hugh

First make sure you have a reference to 'Microsoft DAO 3.6 Object Library'
(In the VBE, Tools->References), then:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(Query1, dbOpenSnapshot)

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
Do Until rs.EOF
.From = "PSS Mass Mail Mailbox"
.Sender = "hughmcmenamin@usa.com"
.To = rs!email
.Subject = "Hugh Message"
'...
.MoveNext
Loop
rs.Close
Set rs = Nothing

Just to satisfy my curiosity, what is objCDOConfig?

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

Stuart ~ Thanks a million for your reply!
I tried to do what you said but it didnt work .....I've played with it, so
now it is at least working and pulling from the query, but just the first
entry. I could not get the LOOP or go to END OF FILE to work for me.
I have no idea where this code came from, a friend gave it to me and asked me
to see if I could get it to work from a query. I'm waiting to hear back from
him to answer your question. Perhaps you can understand from the full code
below. Can you teach me how to make it work, pulling the entire field from
the query? I would be most grateful, sir!
' ************************************************************ ********************
Public Sub testCDO()

Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String

strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "smtp.server.Com"

.Update
End With

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig

.From = "Hugh Mailbox"
.Sender = "hughmcmenamin@usa.com"
.To = rs![email]
.Subject = "Hugh Message"

.HTMLBody = "Here is the body of Hughs email"
.send
End With
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
End Sub
' ************************************************************ ****************************


Stuart McCall wrote:
>> Hi All ~ First time posting.
>>
>[quoted text clipped - 13 lines]
>> Your help is appreciated
>> Hugh
>
>First make sure you have a reference to 'Microsoft DAO 3.6 Object Library'
>(In the VBE, Tools->References), then:
>
>Dim rs As DAO.Recordset
>
>Set rs = CurrentDb.OpenRecordset(Query1, dbOpenSnapshot)
>
>Set objCDOMessage = CreateObject("CDO.Message")
> With objCDOMessage
> Set .Configuration = objCDOConfig
> Do Until rs.EOF
> .From = "PSS Mass Mail Mailbox"
> .Sender = "hughmcmenamin@usa.com"
> .To = rs!email
> .Subject = "Hugh Message"
> '...
> .MoveNext
> Loop
> rs.Close
> Set rs = Nothing
>
>Just to satisfy my curiosity, what is objCDOConfig?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

On Jan 10, 8:20 am, "HughMcMenamin" wrote:
> Hi All ~ First time posting.
>
> I have a module that works ... it contains:
>
> Set objCDOMessage = CreateObject("CDO.Message")
> With objCDOMessage
> Set .Configuration = objCDOConfig
> .From = "PSS Mass Mail Mailbox"
> .Sender = "hughmcmena...@usa.com"
> .To = "hughmcmena...@usa.com"
> .Subject = "Hugh Message"
>
> How can I get it to pull email addresses from a table or query?
> I want it to send "To" this list rather than just one email address.
> Both in same database. Table1 & Query1 (field: email)
> Your help is appreciated
> Hugh

With Access 2000, 2002, or 2003 you could try:

..To = CurrentProject.Connection.Execute("Select EmailAddress FROM
SomeTable").GetString(adClipString, ,";")

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

Thank you Lyle - got it to work. Have a great weekend.

lyle wrote:
>> Hi All ~ First time posting.
>>
>[quoted text clipped - 13 lines]
>> Your help is appreciated
>> Hugh
>
>With Access 2000, 2002, or 2003 you could try:
>
>.To = CurrentProject.Connection.Execute("Select EmailAddress FROM
>SomeTable").GetString(adClipString, ,";")

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

> .To = CurrentProject.Connection.Execute("Select EmailAddress FROM
> SomeTable").GetString(adClipString, ,";")

Very neat, and I would think much faster than my offering. Do you know what
the character limit is for the .To field?

Re: Access2003 + CDO + TO: (how do I pull TO: list from table)

On Jan 12, 7:23 pm, "Stuart McCall" wrote:
> > .To = CurrentProject.Connection.Execute("Select EmailAddress FROM
> > SomeTable").GetString(adClipString, ,";")
>
> Very neat, and I would think much faster than my offering. Do you know what
> the character limit is for the .To field?

I don't know. I suspect that we could "send" the .TO property any VBA
string which is ... hmmm ... limited to 2 ^ 16 - 1 = 65535? characters
(just guessing as I think length of strings is stored in the 2 bytes
immediately preceeding the actual characters in memory.)
But what would our smtp server accept? Could we expect all smtp
servers to be the same? I wouldn't but maybe they are. I expect it
might require a "try it with whatever size you like until it fails"
investigation. That size may be too big, assuming there is no other
cause for the failure.