"item not found in this collection"

[Tim]

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 = "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" +
"(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)"

Dim email2 As String
email2 = "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)"

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

 

[David W. Fenton]

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 = "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)" +
";" + "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)"

Dim email2 As String
email2 = "(e-mail address removed)" + ";" + "(e-mail address removed)" + ";" + "(e-mail address removed)"

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

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 "; ".

 

[Tim]

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]

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.

 

[John W. Vinson]

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.

 

[David W. Fenton]

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.