By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

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

P: n/a
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 = "hu***********@usa.com"
.To = "hu***********@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
Jan 10 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"HughMcMenamin" <u39813@uwewrote 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 = "hu***********@usa.com"
.To = "hu***********@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 = "hu***********@usa.com"
.To = rs!email
.Subject = "Hugh Message"
'...
.MoveNext
Loop
rs.Close
Set rs = Nothing

Just to satisfy my curiosity, what is objCDOConfig?
Jan 10 '08 #2

P: n/a
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 = "hu***********@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 = "hu***********@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-access/200801/1

Jan 11 '08 #3

P: n/a
On Jan 10, 8:20 am, "HughMcMenamin" <u39813@uwewrote:
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, ,";")

Jan 12 '08 #4

P: n/a
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/For...ccess/200801/1

Jan 12 '08 #5

P: n/a
.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?
Jan 13 '08 #6

P: n/a
On Jan 12, 7:23 pm, "Stuart McCall" <smcc...@myunrealbox.comwrote:
.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.
Jan 13 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.