sending email from different rows with selected checkbox

[emwaj]

Hey

I am not really good in VBA and try to find examples in the web, but I didn't find something that could help me.
I hope someone here will be able able to help me...

I have a file with a list of hotel names, and emails- you can see the file here: example file with my code

Each time I write or delete a hotel name, it creates a new checkbox in column A (this macro works well).

I would like to do that:
- choose one or many checkboxes, then click on a button "send email" and one or many (different) emails will be sent to the selected hotel addresses (each checkbox selected to the email of the same selected line)
- if I select some checkbox but hotel name (in column C) or email address (in column E) is missing, then email will be sent only to the selected checkboxes where all details exist (hotel name and email).
- after sending email, the date and time is inserted in column G (that macro works well)

If someone can help me into improving and correcting my code, I would be very grateful !

Sub SEND()
For Each c In Sheets("Sheet1").CheckBoxes
If c.Value = 1 Then
Call eMail


 End If
 Next
 

End Sub


Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim Sheets As Worksheet
Dim OutApp, OutMail As Object


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


lRow = Cells(Rows.Count, 5).End(xlUp).Row


     For i = 6 To lRow
         
    If (Cells(i, 3)) <> "" And (Cells(i, 5)) <> "" Then
                
                
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)


        toList = Cells(i, 5)    'gets the recipient from col E
        eSubject = "Missing bank account information -" & " " & Cells(i, 3)
        
        eBody = "At the attention of " & Cells(i, 3) & "," & vbNewLine & vbNewLine & _
            "The following bank details are missing in our data" & vbNewLine & vbNewLine & _
            "Please send us the missing bank details" & vbNewLine & vbNewLine & _
            "Sincerely, " & vbNewLine & _
            "Me "


        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        
        .Display
      
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 7) = Date + Time 'Marks the row as "email sent in Column G"



End If

Next i



ActiveWorkbook.Save


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

 

[ajetrumpet]

it looks pretty good to me. i didn't test it though, as i can't having o365 because my stupid outlook is configured wrong at the moment. the only I see that you're missing is a SEND command before the END WITH statement in this block:

        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        .SEND
        .Display 'IF YOU'RE SENDING, THIS IS NOT NECESSARY
      
        End With

the only other possibility lies with a couple of other lines, such as this:

toList = Cells(i, 5)    'gets the recipient from col E

that code is running in a standard module, and the list string is carrying values found on a worksheet. I believe that, if you don't use a worksheets qualifier before *cells()*, it refers to the activesheet whatever that is. so you're probably fine. obviously you know how to code, so googling how to check the boxes as you're iterating through the records should be a problem for you, I wouldn't think.

 

[emwaj]

Thank you for your answer! Unfortunately I don't know to code and I found all on the web, and tried until it works.
But the main function doesn't work well (if you try it in Outlook you will see what's the problem)