Email file if checkbox = true

zJenkins

Board Regular
Joined
Jun 5, 2015
Messages
144
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!

Code:
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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:

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

Board Regular
Joined
Jun 5, 2015
Messages
144
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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.


Code:
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

Board Regular
Joined
Jun 5, 2015
Messages
144
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
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?
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,086
Messages
5,466,544
Members
406,486
Latest member
varmehta

This Week's Hot Topics

Top