Send Email if check box is checked

Question

Hi I want to send email mail to the address which is checked I have:

1. Check box
2. Column Name

3. Column Email

   Sub reminder1()
   
   Dim lRow As Integer
   Dim i As Integer
   Dim toList As String
   Dim eSubject As String
   Dim eBody As String
   
   With Application
       .ScreenUpdating = False
       .EnableEvents = False
       .DisplayAlerts = False
   End With
   
   Sheets(1).Select
   lRow = Cells(Rows.Count, 4).End(xlUp).Row
   
   For i = 2 To lRow
   
       If Sheets("Sheet1").CheckBox1.Value = True Then
   
           Set OutApp = CreateObject("Outlook.Application")
           Set OutMail = OutApp.CreateItem(0)
           Cells(i, 5) = "Mail Sent " & Date + Time
           Cells(i, 5).Font.Bold = True
   
           toList = Cells(i, 3)
   
           eSubject = "Your "
   
           eBody = "Good Day"
   
           On Error Resume Next
   
           With OutMail
               .To = toList
               .CC = ""
               .BCC = ""
               .Subject = eSubject
               .BodyFormat = olFormatHTML
               .Display
               .HTMLBody = eBody & vbCrLf & .HTMLBody
               '.Send
           End With
   
           On Error GoTo 0
           Set OutMail = Nothing
           Set OutApp = Nothing 
       End If
       Next i
   
       ActiveWorkbook.Save
   
       With Application
           .ScreenUpdating = True
           .EnableEvents = True
           .DisplayAlerts = True
       End With
   End Sub
   

Problem is that if i check first one it send email to all if not checked it is not sending email even other check boxes are checked

Answer 1

You need to loop through the check boxes. Your current code is hardcoded to check only the first check box, which is the "CheckBox1".

Instead of:

If Sheets("Sheet1").CheckBox1.Value = True Then
'code
end if

Use something like this:

If ActiveSheet.OLEObjects("Checkbox"&i-1).Object.Value  = True Then
'code
End If

Alternative Instead of a checkbox, use dropdown with true / false Then use something like this:

if cells(i,1).value = True then
'code
end if

Answer 2

I 'd propose that you loop through all check boxes and try to find the check box which is applicable to the row you are currently on. So, in order to stick with your solution and having a checkbox in each row you need to verify which checkbox applies to the row you are in and see if the checkbox is checked.

Sub reminder1()

Dim lRow As Integer
Dim i As Integer
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim oleControl As OLEObject

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 2 To lRow
    For Each oleControl In Sheets("Sheet1").OLEObjects
        If Range(oleControl.TopLeftCell.Address).Row = i Then
            If oleControl.Object.Value = True Then

                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                Cells(i, 5) = "Mail Sent " & Date + Time
                Cells(i, 5).Font.Bold = True

                toList = Cells(i, 3)

                eSubject = "Your "

                eBody = "Good Day"

                On Error Resume Next

                With OutMail
                    .To = toList
                    .CC = ""
                    .BCC = ""
                    .Subject = eSubject
                    .BodyFormat = olFormatHTML
                    .Display
                    .HTMLBody = eBody & vbCrLf & .HTMLBody
                    '.Send
                End With

                On Error GoTo 0
                Set OutMail = Nothing
                Set OutApp = Nothing

            End If
        End If
    Next oleControl 
Next i

ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub

Note that this code assumes that the top left corner of the checkbox is within the row to which the checkbox applies. If that's not the case then you can also use the .BottomRightCell.Address or a mix of the two. Also note that this code does not verify if there are other shapes on the sheet such as combo boxes or buttons or other things.