Email file if checkbox = true

[zJenkins]

Hi, I have a userform that populates checkboxes dynamically based on a user list on one of my worsheets. I'd like to send an email to all of the users where the checkbox = true. How do I associate an email with each of the checkboxes?

Further info:

The user list is in column A and their associated email is in column E

Thanks!

Private Sub UserForm_Initialize()

Dim curColumn   As Long
Dim LastRow     As Long
Dim i           As Long
Dim chkBox      As MSForms.CheckBox
Dim ctrl As Control


curColumn = 1 'Set your column index here
LastRow = Worksheets("sht_data").Cells(Rows.Count, curColumn).End(xlUp).Row


For i = 5 To LastRow
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Worksheets("sht_data").Cells(i, curColumn).Value
    chkBox.Left = 5
    chkBox.Top = 5 + ((i - 1) * 20)
Next i


    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            ctrl.Value = True
        End If
    Next ctrl


End Sub

 

[DanteAmor]

It is not necessary that you create checkbox in the sheet and have several chceckbox in the userform.


You can have a ListBox the list of users and each one with a "checkbox", that way you can choose several users and send the mail.

In the userfor example code:

Private Sub UserForm_Initialize()
    With ListBox1
        .ColumnCount = 5
        .ColumnHeads = True
        .ColumnWidths = "40 pt;0 pt;0 pt;0 pt;80 pt"
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        .RowSource = "A2:E10"
    End With
End Sub

 

[zJenkins]

Thanks DanteAmor. I guess I didn't explain it right, I don't have the checkboxes on the worksheet, they are on a userform....you're code is very helpful though as it pulls the emails as well.

Thanks again!

 

[DanteAmor]

Thanks DanteAmor. I guess I didn't explain it right, I don't have the checkboxes on the worksheet, they are on a userform....you're code is very helpful though as it pulls the emails as well.

Thanks again!

You're right, the checkboxes you're creating on the Form, I thought you were creating them on the sheet. Forget this comment.

Me.Controls.Add("Forms.CheckBox.1"

But better use a listbox, since if you have 100 users, you must create 100 checkbox in the form and then control each checkbox to know which user corresponds; Can be done. Once I did it with textbox, but you must also grow your userform or scroll through the form. It's more complicated.


Better the listbox and there you can see all the data.

 

[zJenkins]

That's really good insight, thanks.

Once I have the listbox, how do I reference the items that are checked? Let's say for example, the first 3 boxes are checked, how do I then reference them to perform an action?

 

[DanteAmor]

That's really good insight, thanks.

Once I have the listbox, how do I reference the items that are checked? Let's say for example, the first 3 boxes are checked, how do I then reference them to perform an action?

Private Sub CommandButton1_Click()
  For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) Then
      '[COLOR=#008000]Begin your code to send mail[/COLOR]
      '
      '
      MsgBox "User : " & ListBox1.List(i, 0) & vbCr & "Mail to : " & ListBox1.List(i, 4)
      '
      '
      '[COLOR=#008000]End your code mail[/COLOR]
    End If
  Next
End Sub

 

[zJenkins]

Perfect! Thank you for your help....much appreciated!

 

[DanteAmor]

Perfect! Thank you for your help....much appreciated!

I'm glad to help you. Thanks for the feedback.