"item not found in this collection"

I try to send mass emails in my VBA codes, but I got the above error message. 
Can you help me to debug it. Here are the codes.
Dim email1 As String
email1 = "a@hotmail.com" + ";" + "b@yahoo.com" + ";" + "c@c.com" + ";" + 
"d@d.com" + ";" + "e@e.com" + ";" + "f@pony.ocn.ne.jp"
  
Dim email2 As String
email2 = "g.1@g.com" + ";" + "h.52@h.com" + ";" + "i.34@i.com"
     
Dim strEmail As String
Dim strE As String
Dim rstE As DAO.Recordset
    
    strE = "SELECT * FROM qryPullDataForEmails"  
    Set rstE = DB.OpenRecordset(strE, dbOpenDynaset)
    rstE.MoveFirst
    
    Do Until rstE.EOF
        strEmail = rstE("email") + ";" + strEmail
        rstE.MoveNext
    Loop

    rstE.Close
    Set rstE = Nothing
----------
When I test the above codes by sending to myself (only one email), it works, 
BUT when I try to send the message to these emails using DoCmd.SendObject, it 
pops up, "item not found in this collection"
----------
DoCmd.SendObject acSendNoObject, , acFormatRTF, email1, email2, strEmail, 
"Subject","Body Message",false

Please help me to identify what cause the error? thank you
0
Utf
6/4/2010 3:35:55 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
1123 Views

Similar Articles

[PageSpeed] 10

=?Utf-8?B?VGlt?= <Tim@discussions.microsoft.com> wrote in
news:2AC31BEB-8FA5-4D13-A73D-B6E8A048DBDD@microsoft.com: 

> I try to send mass emails in my VBA codes, but I got the above
> error message. Can you help me to debug it. Here are the codes.
> Dim email1 As String
> email1 = "a@hotmail.com" + ";" + "b@yahoo.com" + ";" + "c@c.com" +
> ";" + "d@d.com" + ";" + "e@e.com" + ";" + "f@pony.ocn.ne.jp"
>   
> Dim email2 As String
> email2 = "g.1@g.com" + ";" + "h.52@h.com" + ";" + "i.34@i.com"
>      
> Dim strEmail As String
> Dim strE As String
> Dim rstE As DAO.Recordset
>     
>     strE = "SELECT * FROM qryPullDataForEmails"  
>     Set rstE = DB.OpenRecordset(strE, dbOpenDynaset)
>     rstE.MoveFirst
>     
>     Do Until rstE.EOF
>         strEmail = rstE("email") + ";" + strEmail
>         rstE.MoveNext
>     Loop
> 
>     rstE.Close
>     Set rstE = Nothing
> ----------
> When I test the above codes by sending to myself (only one email),
> it works, BUT when I try to send the message to these emails using
> DoCmd.SendObject, it pops up, "item not found in this collection"
> ----------
> DoCmd.SendObject acSendNoObject, , acFormatRTF, email1, email2,
> strEmail, "Subject","Body Message",false

Which line is highlighted as producing the error?

Your code snipped doesn't define email1 and email2, for instance.

The collection error suggests that "rstE("email")" is the reference
that's not working. 

FWIW, I always concatenate these kinds of lists using the & operator
instead of +. So, I'd do this: 

  strEmail = rstE("email") & "; " & strEmail

....and then before sending:

  strEmail = Mid(strEmail, 3)

....which strips off any opening "; ".

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
6/4/2010 6:00:35 PM
Hi David:Thank you! I replaced the "+" with "&" and added strEmail = 
Mid(strEmail, 3). But it still pops up the same error message.
The highlighted line as producing the error is:
DoCmd.SendObject acSendNoObject, , acFormatRTF, email1, email2, strEmail, 
"Subject","Body Message",false

You said  my codes snipped doesn't define email1 and email2, for instance. 
How should I re-write it ? Thank you 


"David W. Fenton" wrote:

> =?Utf-8?B?VGlt?= <Tim@discussions.microsoft.com> wrote in
> news:2AC31BEB-8FA5-4D13-A73D-B6E8A048DBDD@microsoft.com: 
> 
> > I try to send mass emails in my VBA codes, but I got the above
> > error message. Can you help me to debug it. Here are the codes.
> > Dim email1 As String
> > email1 = "a@hotmail.com" + ";" + "b@yahoo.com" + ";" + "c@c.com" +
> > ";" + "d@d.com" + ";" + "e@e.com" + ";" + "f@pony.ocn.ne.jp"
> >   
> > Dim email2 As String
> > email2 = "g.1@g.com" + ";" + "h.52@h.com" + ";" + "i.34@i.com"
> >      
> > Dim strEmail As String
> > Dim strE As String
> > Dim rstE As DAO.Recordset
> >     
> >     strE = "SELECT * FROM qryPullDataForEmails"  
> >     Set rstE = DB.OpenRecordset(strE, dbOpenDynaset)
> >     rstE.MoveFirst
> >     
> >     Do Until rstE.EOF
> >         strEmail = rstE("email") + ";" + strEmail
> >         rstE.MoveNext
> >     Loop
> > 
> >     rstE.Close
> >     Set rstE = Nothing
> > ----------
> > When I test the above codes by sending to myself (only one email),
> > it works, BUT when I try to send the message to these emails using
> > DoCmd.SendObject, it pops up, "item not found in this collection"
> > ----------
> > DoCmd.SendObject acSendNoObject, , acFormatRTF, email1, email2,
> > strEmail, "Subject","Body Message",false
> 
> Which line is highlighted as producing the error?
> 
> Your code snipped doesn't define email1 and email2, for instance.
> 
> The collection error suggests that "rstE("email")" is the reference
> that's not working. 
> 
> FWIW, I always concatenate these kinds of lists using the & operator
> instead of +. So, I'd do this: 
> 
>   strEmail = rstE("email") & "; " & strEmail
> 
> ....and then before sending:
> 
>   strEmail = Mid(strEmail, 3)
> 
> ....which strips off any opening "; ".
> 
> -- 
> David W. Fenton                  http://www.dfenton.com/ 
> usenet at dfenton dot com    http://www.dfenton.com/DFA/
> .
> 
0
Utf
6/4/2010 7:26:22 PM
=?Utf-8?B?VGlt?= <Tim@discussions.microsoft.com> wrote in
news:BE81BCD1-12D4-45D3-8361-26C402A0D036@microsoft.com: 

> I replaced the "+" with "&" and added strEmail = 
> Mid(strEmail, 3). But it still pops up the same error message.

I wasn't suggesting that those edits would fix the problem, just
that they were better coding. 

> The highlighted line as producing the error is:
> DoCmd.SendObject acSendNoObject, , acFormatRTF, email1, email2,
> strEmail, "Subject","Body Message",false
> 
> You said  my codes snipped doesn't define email1 and email2, for
> instance. How should I re-write it ?

Well, what are they? What do they refer to? Fields on a form? If
they are underfined, maybe that's the source of the problem. 

As is, those two items are falling as the To and CC fields of your
email message. If you're not assigning anything to them, then just
leave them out: 

  DoCmd.SendObject acSendNoObject,  ,  acFormatRTF, , , strEmail, _
     "Subject","Body Message",false

However, I don't believe you can send an email message with with
only a BCC field. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
6/5/2010 10:44:25 PM
On 5 Jun 2010 22:44:25 GMT, "David W. Fenton" <XXXusenet@dfenton.com.invalid>
wrote:

>However, I don't believe you can send an email message with with
>only a BCC field. 

Depends on the mail server (some bitbucket such messages as potential SPAM)
but yes, you can - I have used just this method to send event announcements to
the members of my contradance group. I now use a single To: address (the
address of the group itself) to prevent messages from being deleted by zealous
antispam software, but it does work.
-- 

             John W. Vinson [MVP]
0
John
6/6/2010 12:53:42 AM
John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:f8sl06tj7rtmo2nvvkb7kcqdgtiutu8t0d@4ax.com: 

> On 5 Jun 2010 22:44:25 GMT, "David W. Fenton"
> <XXXusenet@dfenton.com.invalid> wrote:
> 
>>However, I don't believe you can send an email message with with
>>only a BCC field. 
> 
> Depends on the mail server (some bitbucket such messages as
> potential SPAM) but yes, you can - I have used just this method to
> send event announcements to the members of my contradance group. I
> now use a single To: address (the address of the group itself) to
> prevent messages from being deleted by zealous antispam software,
> but it does work. 

But SendObject uses your MAPI email client, and the email client may
very well be the one that requires a TO address. I would hope that
mail servers and email clients would do exactly that, as BCC-only
emails are the peak of spamminess. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
6/6/2010 5:51:40 PM
Reply:

Similar Artilces:

item not found in this collection
I have the following code behind a combo box "on not in list" event. It runs Ok until I add the new publication number and then I get the message "item not found in this collection". Can anyone assist me please? Private Sub Combo423_NotInList(NewData As String, Response As Integer) Dim Db As DAO.Database Dim Rs As DAO.Recordset Dim Msg As String Dim NewID As String On Error GoTo Err_Combo423_NotInList ' Exit this subroutine if the combo box was cleared. If NewData = "" Then Exit Sub ' Confirm that the user wants to add th...

item not found in this collection
I am trying to modify the criteria parameters in a query "Query1" from a command button in a form, using VBA code. The following code triggers the "item not found in this collection" message. I don't know how to correct it. What am I doing wrong? Private Sub Command79_Click() Dim db As Database Dim qdf As QueryDef Set db = CurrentDb Set qdf = db.QueryDefs("Query1") qdf.Parameters("TagIndex").Value = 1 End Sub Which line gives the error? If its the "Set qdf = ..." line, Query1 doesn't exist (space in nam...

"item not found in this collection"
I try to send mass emails in my VBA codes, but I got the above error message. Can you help me to debug it. Here are the codes. Dim email1 As String email1 = "a@hotmail.com" + ";" + "b@yahoo.com" + ";" + "c@c.com" + ";" + "d@d.com" + ";" + "e@e.com" + ";" + "f@pony.ocn.ne.jp" Dim email2 As String email2 = "g.1@g.com" + ";" + "h.52@h.com" + ";" + "i.34@i.com" Dim strEmail As String Dim strE As String Dim rstE As DAO....