Email file if checkbox = true

zJenkins

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.

c741cf5894dc66251cff935fc906f139.jpg


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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top