Send e-mail by check values in checkboxes

Question

I want to check all checkboxes in a UserForm. Each checkbox is numbered from 2 to 15. When a checkbox is checked, it will send an email from Excel cells.

E.g. Checkbox2 reads data from A2 in the sheet

I tried to make loops.

Sub MailExcelVbaOutlookDisplay()
    Dim zm1 As Variant
    Dim ctl As Control
    Dim i As Variant
    For i = 2 To 15
        Set ctl = Me.Controls("CheckBox" & i)
        If ctl.Visible = True Then
            zm1 = i
            Dim kola As Variant
            kola = Sheets("DataBase").Range("A" & zm1.Value).Value
            Dim kolb As Variant
            kolb = Sheets("Database2").Range("B" & zm1.Value).Value
            Dim OutApp As Object
            Dim OutMail As Object
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = kolb
                .CC = ""
                .Subject = "subject"
                .HTMLBody = "body"
                .Attachments.Add Attachment_Box.Value
                .Display
            End With
            Set OutMail = Nothing
            Set OutApp = Nothing
         End If
    Next i
    Unload Me
End Sub

TechnoDabbler TechnoDabbler · Accepted Answer · 2020-10-25T23:15:54

I assume that there are 14 checkboxes on the form (CheckBox2 to CheckBox15). I don't quite understand the link between A2 and CheckBox2 ... but I am assuming that A2 holds the boolean value of whether the checkbox is selected or not; in which case you don't need to refer to it as you're referring to the CheckBox directly. So ... this cleaned up version of your code will generate emails:

Sub MailExcelVbaOutlookDisplay()
    
    Dim CheckBoxControl As Control
    Dim Counter As Integer
    Dim ToRecipient As String
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    
    For Counter = 2 To 15
        Set CheckBoxControl = Me.Controls("CheckBox" & Counter)
        If CheckBoxControl.Value = True Then
            ToRecipient = Sheets("Database2").Range("B" & Counter).Value
            Set OutMail = OutApp.createitem(0)
            With OutMail
                .To = ToRecipient
                .CC = ""
                .Subject = "subject " & Counter
                .HTMLBody = "body"
                .Attachments.Add Attachment_box.Value
                .Send
            End With
        End If
    Next

    Unload Me
    
End Sub

Send e-mail by check values in checkboxes

1 Answers