dimanche 25 octobre 2020

VBA code to send e-mail by check values in checkboxes

I have a problem with the code I wanted to convert it to check all checkboxes in userform. Each checkbox is numbered from 2 to 15. When a checkbox is checked, it sends an email from excel cells.

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

I tried to make loops, unfortunately I have a problem with implementing it. Is someone able to look at the code below and help? I would be very grateful

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



Aucun commentaire:

Enregistrer un commentaire