For Each Next problem

[tgavin]

I am embarrassed to say I have never been able to get an each, next to
work...probably due to my learn by problem method instead of an organized
class but I am hoping that by conquering this one, I will finally get it.

I need to send an email to each of the people returned by an query. I
created a form with the email info and a button for sending it, which works
fine for the first record but none of the others. Here is the code I wrote
for the cmd button...what am I doing wrong???

Private Sub cmdSend_Click()
Dim ctl As Control
Dim rst As Recordset

ctl = [txtEmail]

For Each ctl In rst
DoCmd.SendObject acSendNoObject, , , [txtEmail], [CC], , [Subject],
[Message]
Next ctl

End Sub


Terri

 

[Rick Brandt]

I am embarrassed to say I have never been able to get an each, next to
work...probably due to my learn by problem method instead of an
organized class but I am hoping that by conquering this one, I will
finally get it.

I need to send an email to each of the people returned by an query. I
created a form with the email info and a button for sending it, which
works fine for the first record but none of the others. Here is the
code I wrote for the cmd button...what am I doing wrong???

Private Sub cmdSend_Click()
Dim ctl As Control
Dim rst As Recordset

ctl = [txtEmail]

For Each ctl In rst
DoCmd.SendObject acSendNoObject, , , [txtEmail], [CC], , [Subject],
[Message]
Next ctl

End Sub


Terri

Lots of problems here.

You dim a Recordset but never set it to anything.

You refer to a Control as a member of a Recordset when that is not true.
Recordsets contain Fields. Forms contain Controls and there is nothing about
what you are attempting that should even involve a form (although the code could
be run from a Form's module).

For - Each is used on collections. A Recordset is not a collection so For -
Each is not appropriate. You should use a Do - Until testing for EOF.

(untested air code follows)

Private Sub cmdSend_Click()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("YourQueryName", dbOpenSnapshot)

Do Until rst.EOF
DoCmd.SendObject acSendNoObject, , , rst!EmailAddressField, Me.CC, ,
Me.Subject, Me.Message
rst.MoveNext
Loop

rst.close
Set rst = Nothing
Set db = Nothing

End Sub

I realize now that your form might be bound to the query containing the Email
recipients. I wouldn't do it that way as it is not necessary, but in that case
the code above could be modified to use the Form's own Recordset...

Private Sub cmdSend_Click()


With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, , , !EmailAddressField, Me.CC, ,
Me.Subject, Me.Message
.MoveNext
Loop
End With

End Sub

 

[tgavin]

As I said, I'm clueless, but I totally appreciate the help.

I switched the code to below and it is sticking on the set rst line
returning the message of type mismatch.

rivate Sub cmdSend_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryCurrentEmail", dbOpenSnapshot)


With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, , , rst!txtEmail, Me.CC, ,
Me.Subject, Me.Message
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing
Set db = Nothing


End Sub

 

[tgavin]

Rick, thanks! I am getting closer! I already had the library chosen, I just
didn't realize I had to mark things as DAO.

It stopped on the docmd line with a 2498 error. It says 'an expression you
entered is the wrong data type for one of the arguments but when I move my
mouse over each of the To through Message arguments, the tool tip shows me
the right answer based on the first record. Counted commas and it seems
right??

DoCmd.SendObject acSendNoObject, , , rst!txtEmail, Me.CC, , Me.Subject,
Me.Message

Terri

As I said, I'm clueless, but I totally appreciate the help.

I switched the code to below and it is sticking on the set rst line
returning the message of type mismatch.

Ah, you are using one of the "broken" versions of Access that by default include
a reference to ADO rather than DAO

While in your code module go to Tools - References. You will see at the top the
"checked" references that your file is using. Scrolling down through the list
you shoud see one for Microsoft DAO version 3.6 Object Library. Check that.

If your project doesn't need the ADO library you can uncheck that. If you need
it or you are not sure you can leave both checked, but then you will need to
disambiguate all object references that exist in both libraries. Recordset is
one of these. Just change to...

Dim rst As DAO.Recordset

rivate Sub cmdSend_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("qryCurrentEmail", dbOpenSnapshot)


With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, , , rst!txtEmail, Me.CC, ,
Me.Subject, Me.Message
.MoveNext
Loop
End With

rst.Close
Set rst = Nothing
Set db = Nothing


End Sub

I am embarrassed to say I have never been able to get an each, next
to work...probably due to my learn by problem method instead of an
organized class but I am hoping that by conquering this one, I will
finally get it.

I need to send an email to each of the people returned by an query.
I created a form with the email info and a button for sending it,
which works fine for the first record but none of the others. Here
is the code I wrote for the cmd button...what am I doing wrong???

Private Sub cmdSend_Click()
Dim ctl As Control
Dim rst As Recordset

ctl = [txtEmail]

For Each ctl In rst
DoCmd.SendObject acSendNoObject, , , [txtEmail], [CC], ,
[Subject], [Message]
Next ctl

End Sub


Terri

 

[Rick Brandt]

Rick, thanks! I am getting closer! I already had the library chosen,
I just didn't realize I had to mark things as DAO.

It stopped on the docmd line with a 2498 error. It says 'an
expression you entered is the wrong data type for one of the
arguments but when I move my mouse over each of the To through
Message arguments, the tool tip shows me the right answer based on
the first record. Counted commas and it seems right??

DoCmd.SendObject acSendNoObject, , , rst!txtEmail, Me.CC, ,
Me.Subject, Me.Message

How about the Email address field? Is it really named "txtEMail"? Does it
actually contain string data? I cannot see anything else that looks incorrect.

As a test, temporarily replace the field reference with a hard-coded string. If
the error goes away then it does not like what is contained in that field.
Could some records have a Null value there? You will need to filter those out.

 

[tgavin]

Rick, I am still getting the same error message and I don't know which field
could be wrong. The txtEmail is a text field from the table/query, the other
3 are unbound controls added to the form.

Terri

I am embarrassed to say I have never been able to get an each, next to
work...probably due to my learn by problem method instead of an
organized class but I am hoping that by conquering this one, I will
finally get it.

I need to send an email to each of the people returned by an query. I
created a form with the email info and a button for sending it, which
works fine for the first record but none of the others. Here is the
code I wrote for the cmd button...what am I doing wrong???

Private Sub cmdSend_Click()
Dim ctl As Control
Dim rst As Recordset

ctl = [txtEmail]

For Each ctl In rst
DoCmd.SendObject acSendNoObject, , , [txtEmail], [CC], , [Subject],
[Message]
Next ctl

End Sub


Terri

Lots of problems here.

You dim a Recordset but never set it to anything.

You refer to a Control as a member of a Recordset when that is not true.
Recordsets contain Fields. Forms contain Controls and there is nothing about
what you are attempting that should even involve a form (although the code could
be run from a Form's module).

For - Each is used on collections. A Recordset is not a collection so For -
Each is not appropriate. You should use a Do - Until testing for EOF.

(untested air code follows)

Private Sub cmdSend_Click()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("YourQueryName", dbOpenSnapshot)

Do Until rst.EOF
DoCmd.SendObject acSendNoObject, , , rst!EmailAddressField, Me.CC, ,
Me.Subject, Me.Message
rst.MoveNext
Loop

rst.close
Set rst = Nothing
Set db = Nothing

End Sub

I realize now that your form might be bound to the query containing the Email
recipients. I wouldn't do it that way as it is not necessary, but in that case
the code above could be modified to use the Form's own Recordset...

Private Sub cmdSend_Click()


With Me.RecordsetClone
Do Until .EOF
DoCmd.SendObject acSendNoObject, , , !EmailAddressField, Me.CC, ,
Me.Subject, Me.Message
.MoveNext
Loop
End With

End Sub

 

[Rick Brandt]

Rick, I am still getting the same error message and I don't know
which field could be wrong. The txtEmail is a text field from the
table/query, the other 3 are unbound controls added to the form.

Did you try my suggestion to replace them one at a time with hard-coded values?
That should tell you which one is causing the error.

 

[tgavin]

One solve, 2 found. I had already set the query to filter out the blank
emails, so there were no nulls there but I had not been filling in the cc on
the form. Once I did, it sent...however???

Is there a way to leave a cc field that may be left blank in some cases, or
do I need to just eliminate the field?

Also, when I sent the emails I got a security message and needed to click
allow or deny for each record...rather tiresome for 259 emails. Now I am
doing this on my own computer which has 3 versions of Office on it (I'm an
trainer) including Office XP Developer which is what I use to create dbs and
Office 2007 which is the Outlook I am using (haven't had the time to try to
deal with Access 2007). This actual db sits on a computer with only Office XP
on it for remote access. Rather than bother you, I would test it there but I
don't have access again till Monday and I really want to solve this before
then. Solving the emailing problem here is also going to solve a problem on
another db I have where they run 2003, will I have the security problem
there? And if so, how do I solve it?

 

[Rick Brandt]

One solve, 2 found. I had already set the query to filter out the
blank emails, so there were no nulls there but I had not been filling
in the cc on the form. Once I did, it sent...however???

Is there a way to leave a cc field that may be left blank in some
cases, or do I need to just eliminate the field?

Use...

Nz(Me.CC,"")

The value can be blank, just not Null

Also, when I sent the emails I got a security message and needed to
click allow or deny for each record...rather tiresome for 259 emails.

If you use Outlook then you will get those prompts. You can use CDO automation
code instead or you can obtain a free utility that will automatically respond to
the security prompt (AutoClick I think?).

Now I am doing this on my own computer which has 3 versions of Office
on it (I'm an trainer) including Office XP Developer which is what I
use to create dbs and Office 2007 which is the Outlook I am using
(haven't had the time to try to deal with Access 2007). This actual
db sits on a computer with only Office XP on it for remote access.
Rather than bother you, I would test it there but I don't have access
again till Monday and I really want to solve this before then.
Solving the emailing problem here is also going to solve a problem on
another db I have where they run 2003, will I have the security
problem there? And if so, how do I solve it?

The prompts started with Outlook 2000 (after some service pack) Office 2007 gets
rid of the prompt. Otherwise do as indicated above.

 

[tgavin]

Rick thanks! Since I have no idea what CDO automation is, I guess I will try
to find the utility. Understanding this one is going to help me with several
problems.

Terri

 

[Rick Brandt]

Rick thanks! Since I have no idea what CDO automation is, I guess I
will try to find the utility. Understanding this one is going to help
me with several problems.

Another option is to append all of the Email addresses into one string variable
seperated by semi-colons and then just send one Email to all of them instead of
a separate one. I understand that in some cases that is not acceptable.

 

[tgavin]

I wish I could. The last time I tried it, I had a bunch bounced by spam
filters! So I am also trying to find a way to mail merge pdf's but that's
another problem! If it's not one thing, it's another. But they do say an
active brain keeps us young!

Thanks!

 

[David W. Fenton]

Use...

Nz(Me.CC,"")

The value can be blank, just not Null

Or just pass

Me!CC & vbNullString

instead. Why call a function for that?

If you're going to use Nz(), though, it's more efficient
(particularly in a loop) to use one of the predefined constants for
a zero-length string, so it would be Nz(Me!CC, vbNullString) in
code.

Naturally, the constants are available only in code. If you're
calling Nz() or concatenating in a query, you'd use the "" literal.

 

[David W. Fenton]

Another option is to append all of the Email addresses into one
string variable seperated by semi-colons and then just send one
Email to all of them instead of a separate one. I understand that
in some cases that is not acceptable.

It will likely get the emails classified as SPAM, any many ISPs have
their SMTP servers set to reject any email with >N addresses in the
TO: of CC: or BCC: headers.

Whenever my clients want to send mass emails, I send them to a bulk
email service provider, as it is just no longer the case that you
can safely send mass emails from Access.