Confused! Need help with email and checkbox

[3Mark3]

Hello all,
I've tried fixing this to no avail. I'm hoping someone here can help me. I have a button that is on a form that will send an e-mail based on some selections on this form. What I need to have it do is send an e-mail to every rep that has a checkbox. Please see below code. As it stands right now, it will only send an e-mail to the selected rep, not to whoever I have selected in the checkbox. Any help would be GREATLY appreciated!!!

Private Sub Command41_Click()
On Error GoTo ErrorHandler


Dim strToWhom As String
Dim strSubject As String

strSubject = Forms!main_menu!class_select & " Training Class"
strToWhom = Me.multi_selection_nt_main_subform.Form!email
DoCmd.SendObject , , , strToWhom, , , strSubject, , True

Exit Sub
ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Err " & Err.Number & ": " & Err.Description
End Select


End Sub

 

[MajP]

You obviously only return the selected record

strToWhom = Me.multi_selection_nt_main_subform.Form!email

You are going to have to loop through the recordset and put it together. Something like

strToWhom = fncToWhom(Me.multi_selection_nt_main_subform.Form.recordsetClone,"Checked","Name")
'verify it works
debug.print strToWhom

[code]
In the above your use the correct field names from your table. I used Checked and Names
build a public function in a module

Public Function fncToWhom(rs As DAO.Recordset, blnField As String, nameField As String) As String
Do While Not rs.EOF
If rs.Fields(blnField) And Not IsNull(rs.Fields(nameField)) Then
fncToWhom = fncToWhom & rs.Fields(nameField) & ";"
End If
rs.MoveNext
Loop
If Right(fncToWhom, 1) = ";" Then
fncToWhom = Left(fncToWhom, Len(fncToWhom) - 1)
End If
End Function
[/code]

 

[MajP]

Oops. A little easier to read

strToWhom = fncToWhom(Me.multi_selection_nt_main_subform.Form.recordsetClone,"Checked","Name")
'verify it works
debug.print strToWhom

In the above your use the correct field names from your table. I used Checked and Names
build a public function in a module

Public Function fncToWhom(rs As DAO.Recordset, blnField As String, nameField As String) As String
  Do While Not rs.EOF
    If rs.Fields(blnField) And Not IsNull(rs.Fields(nameField)) Then
       fncToWhom = fncToWhom & rs.Fields(nameField) & ";"
    End If
    rs.MoveNext
  Loop
  If Right(fncToWhom, 1) = ";" Then
      fncToWhom = Left(fncToWhom, Len(fncToWhom) - 1)
  End If
End Function

 

[3Mark3]

Thank you for your help. This is a topic that has frustrated me a little. Ok I added what you posted, but I'm getting an error. Can you spot where I am making this error? I think it has something to do with the fncToWhom function. Can you confirm this?

Here's what my code looks like.

rivate Sub Click_To_E_mail_Reps_Click()
On Error GoTo ErrorHandler

Dim strToWhom As String
Dim strSubject As String
Dim ctl As Object

strSubject = "Training"
strToWhom = fncToWhom(Me.multi_selection_nt_main_subform.Form.RecordsetClone, "checkbox1", "email")
'verify it works
Debug.Print strToWhom

DoCmd.SendObject , , , strToWhom, , , strSubject, , True

Exit Sub
ErrorHandler:
Select Case Err.Number
Case 2501
Resume Next
Case Else
MsgBox "Err " & Err.Number & ": " & Err.Description
End Select



Public function...

Public Function fncToWhom(rs As DAO.Recordset, blnField As String, nameField As String) As String
Do While Not rs.EOF
If rs.Fields(blnField) And Not IsNull(rs.Fields(nameField)) Then
fncToWhom = fncToWhom & rs.Fields(nameField) & ";"
End If
rs.MoveNext
Loop
If Right(fncToWhom, 1) = ";" Then
fncToWhom = Left(fncToWhom, Len(fncToWhom) - 1)
End If
End Function

 

[MajP]

Help me out. What is the error and where is it at?

 

[3Mark3]

Ooops! My apologies, I left that out. It get the error when clicking my action button. The error reads as follows:

"Compile error: User-defined type not defined"

The error goes to the fncToWhom module.

 

[PHV]

You have to reference the Microsoft DAO 3.x Object Library:
when in VBE menu Tools -> References ...

"checkbox1" and "email" should be fields names in the multi_selection_nt_main_subform's underlaying table/query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3Mark3]

You have to reference the Microsoft DAO 3.x Object Library:
when in VBE menu Tools -> References ...

"checkbox1" and "email" should be fields names in the multi_selection_nt_main_subform's underlaying table/query.

Ahh. I selected "Microsoft DAO 3.6 Object Library." (There is a 3.5 available as well)

With that, I get a different error..looks like this:

"Compile error: Expected variable or procedure, not module

 

[PHV]

Seems you named a module "fncToWhom" ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886