Probably simple for you guys.

[Dumb_Man_Here]

Hello everyone,

I have heard allot about this site from a coworker and finally decided to give it a shot. Hopefully I will be able to explain myself properly.

I am trying to send an email out to everyone who has a chk box checked by their name. It seems to be working but till I get to the very end. I think I did not dimension a variable right and I am sure allot of other stuff too. I know this is an Excel forum but since it deals with allot of VB I thought it would be ok. The below is my crappy code:

Function email_send()
On Error GoTo email_send_Err
Dim Fxdb As Object
Dim rs As Recordset
Dim Addrs As Variant
Dim X As Object
Dim Y As Object

Set Fxdb = DBEngine.Workspaces(0).OpenDatabase("C:Documents and SettingscfennerMy DocumentsAccessFxdbfxrmgmtxp.mdb")
Set rs = Fxdb.OpenRecordset("Policy-eMailTrds")

rs.MoveFirst

While Not rs.EOF

Set Addrs = rs.Fields("email")
Set X = Addrs
Set Y = X

rs.MoveNext

Wend

MsgBox "here."
DoCmd.SendObject , , , Y, , , "Final Test", "Testing Limit Notify.", True, ""

email_send_Exit:
Exit Function

email_send_Err:
MsgBox Error$
Resume email_send_Exit

End Function

 

[Corticus]

Hi!

I think you need to change
<pre>While Not rs.EOF</pre>
to
<pre>Do Until rs.EOF</pre>

HTH,
Corticus

 

[Dumb_Man_Here]

Hello thinks for responding. I am not at work right now so I cant test it. But what is the difference from what I am doing and using Do until?

What happend at first I was getting it to work but it would put everyone's email on a different email. I was hoping that the previuos code would get them all an one email insteade of having to press send 20 times. Here is the very first set of code.
Function test_e_mail()

On Error GoTo test_e_mail_Err

Dim Fxdb As Object

Dim rs As Recordset

Dim Addrs As Object

Dim X As Object

Dim Y As Object

Set Fxdb = DBEngine.Workspaces(0).OpenDatabase("C:Documents and SettingscfennerMy DocumentsAccessFxdbfxrmgmtxp.mdb")

Set rs = Fxdb.OpenRecordset("Policy-eMailTrds")

rs.MoveFirst

While Not rs.EOF

DoCmd.SendObject , , , rs.Fields("email"), , , "Final Test", "Testing Limit Notify for " & rs.Fields("#Test") & " new trades.", True, ""



rs.MoveNext



Wend



test_e_mail_Exit:

Exit Function

test_e_mail_Err:

MsgBox Error$

Resume test_e_mail_Exit

End Function


Thanks allot for anyone help.

 

[Russell Hauf]

Your loop is fine as-is. But if you want to email only those with "a check box checked by their name", then you need to find the field that populates those check boxes in your recordset and then test to see if the value is true. Also, you don't need X and Y in your code. Addrs, X, and Y are all the same, so X & Y are redundant.

Hope this helps,

Russell

 

[Dumb_Man_Here]

Thank you so very much. I will give it a try when I get back into the office tomorrow. So do you think that the redundant X Y is what is making the code to error?

 

[Russell Hauf]

No, I don't think that's where you're getting your error. Can you tell us where the error is occuring? If you don't know, I suggest that you step through the code.

 

[Dumb_Man_Here]

Hello there!!

I am getting my error right here.

DoCmd.SendObject , , , Y, , , "Final Test", "Testing Limit Notify.", True, ""

The msgBx before it is the last thing that it will do before it junks out on me. This is using my first code procedure in my intial post. I do think though that the code is picking up the values of the checked boxes. I have only had one class in VB but I am pretty sure of that.

 

[Russell Hauf]

Ok - I didn't account for the fact that your recordset could be based on a query. Sorry about that. Try taking out the "" at the end and just leaving it empty - you are passing a null string for the template, and I think that is where the error may be. Let me know if it works.

 

[Dumb_Man_Here]

Ok...let me give it a shot.

Thanks a bunch. I hope this works