MS Access Forum / Forms / November 2007
For Each Next problem
|
|
Thread rating:  |
tgavin - 17 Nov 2007 14:19 GMT 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 - 17 Nov 2007 14:47 GMT > 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 [quoted text clipped - 20 lines] > > 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
 Signature Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
tgavin - 17 Nov 2007 17:01 GMT 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 [quoted text clipped - 69 lines] > > End Sub Rick Brandt - 17 Nov 2007 17:07 GMT > 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.
 Signature Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
tgavin - 17 Nov 2007 15:31 GMT 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
> 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 [quoted text clipped - 19 lines] > > Terri Rick Brandt - 17 Nov 2007 15:42 GMT > 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() > [quoted text clipped - 42 lines] > > > > Terri tgavin - 17 Nov 2007 16:14 GMT 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. > > [quoted text clipped - 61 lines] > > > > > > Terri Rick Brandt - 17 Nov 2007 16:28 GMT > Rick, thanks! I am getting closer! I already had the library chosen, > I just didn't realize I had to mark things as DAO. [quoted text clipped - 7 lines] > 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.
 Signature Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
tgavin - 17 Nov 2007 17:50 GMT 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, thanks! I am getting closer! I already had the library chosen, > > I just didn't realize I had to mark things as DAO. [quoted text clipped - 14 lines] > 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. Rick Brandt - 17 Nov 2007 20:39 GMT > 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 [quoted text clipped - 6 lines] > 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.
 Signature Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
tgavin - 17 Nov 2007 21:36 GMT 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
> > 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 [quoted text clipped - 29 lines] > The prompts started with Outlook 2000 (after some service pack) Office 2007 gets > rid of the prompt. Otherwise do as indicated above. Rick Brandt - 17 Nov 2007 22:05 GMT > 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.
 Signature Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
tgavin - 17 Nov 2007 22:33 GMT 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!
> > 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 [quoted text clipped - 3 lines] > 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. David W. Fenton - 17 Nov 2007 22:54 GMT >> 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 [quoted text clipped - 4 lines] > 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.
 Signature David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
David W. Fenton - 17 Nov 2007 22:52 GMT > 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.
 Signature David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
|