email from subform

[AJ]

I appreciate the help I have received so far, but I can get nothing to work
correctly.

I have a form for entering an action plan, this form has a subform where all
the action items for that plan are listed. Each action item is assigned to a
person, this field is a combo box which pulls from a table where email
addresses are listed. I want an option button (or as an update from a text
field) so that emails will be sent to each address in the subform. The
option button would be on the main form. I did create a textbox on the form
called email to "convert" the combobox to text.

Here is the code I have on the button right now:

Private Sub OptionButton2_Enter()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Dim tostr As String

With Me![Actions Table subform].Form.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
tostr = tostr & Me.Actions_Table_subform.Form!email & ";"
.MoveNext
Loop
End If
End With

DoCmd.SendObject acSendNoObject, , , tostr, , , "action plan", , , False

End Sub

Right now it seems to be repeating the same email on the first record in
subform for all of them (ie, if there are 3 action items in the subform and
all 3 ahve different person assigned the email has the email address from the
first one repeated 3 times),

If I can ever get this working... is there a way to not repeat the email
addresses, ie, if there are 3 items and 2 are assigned to the same person the
email only lists them once.

Thanks so much for any help. I will be very grateful!

 

[AJ]

I'm getting Run-time erros ''2465', Application-defined or object-defined
error.

when I to into the vb editing the statement Do Until .EOF is highlighted
yellow.

What will correct this?

--
AJ

Try this:
With Me![Actions Table subform].Form
If .RecordSetClone.RecordCount > 0 Then
.RecordSetClone.MoveFirst
Do Until .EOF
tostr = tostr & . & ";"
.RecordSetClone.MoveNext
Loop
End If
End With
Make sure the name of the control for email is different than the field name
so Access does not get confused.
[QUOTE]
I appreciate the help I have received so far, but I can get nothing to work
correctly.

I have a form for entering an action plan, this form has a subform where all
the action items for that plan are listed. Each action item is assigned to a
person, this field is a combo box which pulls from a table where email
addresses are listed. I want an option button (or as an update from a text
field) so that emails will be sent to each address in the subform. The
option button would be on the main form. I did create a textbox on the form
called email to "convert" the combobox to text.

Here is the code I have on the button right now:

Private Sub OptionButton2_Enter()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Dim tostr As String

With Me![Actions Table subform].Form.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
tostr = tostr & Me.Actions_Table_subform.Form!email & ";"
.MoveNext
Loop
End If
End With

DoCmd.SendObject acSendNoObject, , , tostr, , , "action plan", , , False

End Sub

Right now it seems to be repeating the same email on the first record in
subform for all of them (ie, if there are 3 action items in the subform and
all 3 ahve different person assigned the email has the email address from the
first one repeated 3 times),

If I can ever get this working... is there a way to not repeat the email
addresses, ie, if there are 3 items and 2 are assigned to the same person the
email only lists them once.

Thanks so much for any help. I will be very grateful!
[/QUOTE]

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200905/1
[/QUOTE]

 

[Stuart McCall]

I'm getting Run-time erros ''2465', Application-defined or object-defined
error.

when I to into the vb editing the statement Do Until .EOF is highlighted
yellow.

What will correct this?

--
AJ

Try this:
With Me![Actions Table subform].Form
If .RecordSetClone.RecordCount > 0 Then
.RecordSetClone.MoveFirst
Do Until .EOF
tostr = tostr & . & ";"
.RecordSetClone.MoveNext
Loop
End If
End With[/QUOTE][/QUOTE]
<snip>

Make that line read:

Do Until .RecordSetClone.EOF

 

[AJ]

Getting close... Thanks for the help so far.

Now all seems to be working except that the email address on the first
record is repeating for all the records. In other words, if there are 3
records in the subform with the first (e-mail address removed), the second
(e-mail address removed), the third (e-mail address removed), then the email that comes up has
(e-mail address removed) 3 times and the other 2 not at all.

Thoughts?

again, thanks in advance
--
AJ

Stuart is exactly right. My Bad. I really need to work on that AIR CODE
compiler.

I'm getting Run-time erros ''2465', Application-defined or object-defined
error.

[quoted text clipped - 14 lines]

End If
End With

<snip>

Make that line read:

Do Until .RecordSetClone.EOF

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[AJ]

I have a SendObject command at the end of the loop. I use tostr as the "To"

it shows up as:

(e-mail address removed);[email protected];aj.company.com

where it should be

(e-mail address removed);[email protected];[email protected]
--
AJ

After the Loop add a MsgBox tostr to see what was accumulated.

Getting close... Thanks for the help so far.

Now all seems to be working except that the email address on the first
record is repeating for all the records. In other words, if there are 3
records in the subform with the first (e-mail address removed), the second
(e-mail address removed), the third (e-mail address removed), then the email that comes up has
(e-mail address removed) 3 times and the other 2 not at all.

Thoughts?

again, thanks in advance

Stuart is exactly right. My Bad. I really need to work on that AIR CODE
compiler.

[quoted text clipped - 9 lines]

Do Until .RecordSetClone.EOF

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[AJ]

I think so. The field name is email and I named the textbox email2.
--
AJ

Did you make sure the Email control is named different than the field to
which it is bound?
Are you referencing the control or the field? You should be referencing the
field.

I have a SendObject command at the end of the loop. I use tostr as the "To"

it shows up as:

(e-mail address removed);[email protected];aj.company.com

where it should be

(e-mail address removed);[email protected];[email protected]

After the Loop add a MsgBox tostr to see what was accumulated.

[quoted text clipped - 14 lines]

Do Until .RecordSetClone.EOF

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[AJ]

even when I try it on another text box I get the same action with that field.

I do appreciate the help. Thank you.
--
AJ

Did you make sure the Email control is named different than the field to
which it is bound?
Are you referencing the control or the field? You should be referencing the
field.

I have a SendObject command at the end of the loop. I use tostr as the "To"

it shows up as:

(e-mail address removed);[email protected];aj.company.com

where it should be

(e-mail address removed);[email protected];[email protected]

After the Loop add a MsgBox tostr to see what was accumulated.

[quoted text clipped - 14 lines]

Do Until .RecordSetClone.EOF

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[AJ]

Still not working. Could I send you the database?

thanks
--
AJ

Try:
tostr = tostr & .RecordSet! & ";"

I would have used txtEmail for the control name.
[QUOTE]
even when I try it on another text box I get the same action with that field.

I do appreciate the help. Thank you.[QUOTE]
Did you make sure the Email control is named different than the field to
which it is bound?[/QUOTE]
[quoted text clipped - 15 lines][QUOTE]
Do Until .RecordSetClone.EOF[/QUOTE][/QUOTE]

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com
[/QUOTE]

 

[AJ]

on its way, thank you
--
AJ

OK Rural Guy at Wild Blue dot Net will work.

Still not working. Could I send you the database?

thanks

Try:
tostr = tostr & .RecordSet! & ";"[/QUOTE]
[quoted text clipped - 9 lines][QUOTE]
Do Until .RecordSetClone.EOF[/QUOTE][/QUOTE]

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200905/1
[/QUOTE]