sending email from different rows with selected checkbox

emwaj

New Member
Joined
Jan 14, 2019
Messages
12
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 ! :)

VBA Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
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:
Code:
        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:
Code:
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

New Member
Joined
Jan 14, 2019
Messages
12
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) :(
 

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,666
Members
415,917
Latest member
kungsleden

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
Top