Collection object help - driving me crazy

[ck]

Sorry for the cross post.

What is wrong with this code?--ado recordset to populate a collection--see
reason below
Dim myCol2 As New Collection
On Error Resume Next
Do While Not rs.EOF
myCol2.Add rs!Email, rs!Email
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

MsgBox myCol2.Count

For Each Item In myCol2
Set rcp = ml.Recipients.Add(myCol2(Item))
rcp.Type = olBCC
Next

-------The msgbox shows the correct count but I can not get the string data
from each item. What am I missing?? I have tried a counting for x = 1 to
myCol2.count and fox x=mycol2.count to 1 step -1... I can not figure out
what I am overlooking. I believe the collection object is being populated
correctly. I think it's something around my on error resume next line of
code. I am trying to get email addresses from my db. That works fine. I
want to loop through those records and add the email address as the item
and the key values to the collection object. This will prevent more than one
of the same email address. I then want to loop through the collection and
add each item to the recipients collection of an outlook.mailitem. I can
make everything work if I skip the collection, but this lets the same email
address be added any number of times. Thanks everybody.
~ck

[Salad]

ck wrote:

Sorry for the cross post.

What is wrong with this code?--ado recordset to populate a collection--see
reason below
Dim myCol2 As New Collection
On Error Resume Next
Do While Not rs.EOF
myCol2.Add rs!Email, rs!Email
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

MsgBox myCol2.Count

For Each Item In myCol2
Set rcp = ml.Recipients.Add(myCol2(Item))
rcp.Type = olBCC
Next

-------The msgbox shows the correct count but I can not get the string data
from each item. What am I missing?? I have tried a counting for x = 1 to
myCol2.count and fox x=mycol2.count to 1 step -1... I can not figure out
what I am overlooking. I believe the collection object is being populated
correctly. I think it's something around my on error resume next line of
code. I am trying to get email addresses from my db. That works fine. I
want to loop through those records and add the email address as the item
and the key values to the collection object. This will prevent more than one
of the same email address. I then want to loop through the collection and
add each item to the recipients collection of an outlook.mailitem. I can
make everything work if I skip the collection, but this lets the same email
address be added any number of times. Thanks everybody.
~ck

Here's a quick example
Sub ColTest()
Dim col As New Collection
Dim var As Variant
col.Add "test1"
col.Add "test2"

For Each var In col
MsgBox var
Next
Set col = Nothing
End Sub

[rkc]

"ck" <c_**********@hotmail.com> wrote in message
news:1w***************@newssvr27.news.prodigy.com. ..

Sorry for the cross post.

What is wrong with this code?--ado recordset to populate a collection--see
reason below
Dim myCol2 As New Collection
On Error Resume Next
Do While Not rs.EOF
myCol2.Add rs!Email, rs!Email
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

MsgBox myCol2.Count

For Each Item In myCol2
Set rcp = ml.Recipients.Add(myCol2(Item))
rcp.Type = olBCC
Next

First of all, rs!Email is a ado.field object. What you are doing is adding
the
ado.field object that rs!Email represents to to the collection. What you
think
you are doing is adding the rs!Email.Value to the collection.

So the solution is do what you think you are doing. Use rs!Email.Value.

Also, your for each loop should look like:

For Each Item In myCol2
Set rcp = ml.Recipients.Add(Item)
rcp.Type = olBCC
Next

[Mustafa\(News\)]

Hi,
Try This.

Dim myCol2 As Collection
Dim Item as variant

On Error Resume Next

set myCol2=new collection

Do While Not rs.EOF
myCol2.Add rs!Email,"'" & rs!Email & "'" ' Otherwise if not required
just remove the collection key
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
oConn.Close
Set oConn = Nothing

MsgBox myCol2.Count
For Each Item In myCol2
Set rcp = ml.Recipients.Add(myCol2(Item))
rcp.Type = olBCC
Next

set myCol2=nothing

Mustafa,
"Salad" <oi*@vinegar.com> wrote in message
news:RQ******************@newsread2.news.pas.earth link.net...

ck wrote:

Sorry for the cross post.

What is wrong with this code?--ado recordset to populate a collection--see reason below
Dim myCol2 As New Collection
On Error Resume Next
Do While Not rs.EOF
myCol2.Add rs!Email, rs!Email
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
oConn.Close
Set oConn = Nothing

MsgBox myCol2.Count

For Each Item In myCol2
Set rcp = ml.Recipients.Add(myCol2(Item))
rcp.Type = olBCC
Next

-------The msgbox shows the correct count but I can not get the string data from each item. What am I missing?? I have tried a counting for x = 1 to myCol2.count and fox x=mycol2.count to 1 step -1... I can not figure out
what I am overlooking. I believe the collection object is being populated correctly. I think it's something around my on error resume next line of
code. I am trying to get email addresses from my db. That works fine. I want to loop through those records and add the email address as the item and the key values to the collection object. This will prevent more than one of the same email address. I then want to loop through the collection and add each item to the recipients collection of an outlook.mailitem. I can make everything work if I skip the collection, but this lets the same email address be added any number of times. Thanks everybody.
~ck

Here's a quick example
Sub ColTest()
Dim col As New Collection
Dim var As Variant
col.Add "test1"
col.Add "test2"

For Each var In col
MsgBox var
Next
Set col = Nothing
End Sub