Please help- Send an email using if check box is selected to email within that row

[Helloall]

Hello All,

I am starting to learn advanced functions in Excel so please forgive me if I do not explain this well.

What I am trying to do:

1. I need to create a check box per row. (not a big deal to explain, I can figure that one out using videos/forums I have found already). Check box begins in cell V3, ends indefinite.

2. I need to create a button/macro that when V3 is checked sends an email to all emails in their respective rows- emails reside beginning in cell W3 and also ends indefinite as more entries begin to compile. If V3 is not checked- no action taken.

3. Subject line of email reads "You have an update to your log"

4. Body reads "Please review your log and take appropriate action"


Any help or guidance is much appreciated!

 

[Logit]

.
You 'll need to change the ranges so they align with your worksheet columns .

This part of the macro pertains to "check box" feature you mention in #1 of your post. Rather than a checkbox, you can easily use the cell reference:

If (Cells(i, 1)) <> "" Then '<---- the number one [ 1 ] refers to Column A. Just count the columns from the left to find V3's number.

Here is the entire macro :

Option Explicit
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, 4).End(xlUp).Row


For i = 2 To lRow
  If (Cells(i, 1)) <> "" Then  '<--------------------------------------------- change this range
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)


        toList = Cells(i, 3)    'gets the recipient from col B
        eSubject = "Bonus Assignment"
        
        eBody = "Hello " & Cells(i, 2) & "," & vbNewLine & vbNewLine & _
            "I am pleased to inform you that your annual bonus is $" & Cells(i, 4) & vbNewLine & vbNewLine & _
            "Sincerely, " & vbNewLine & _
            "William Rose, President "


        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        
        .Display   ' ********* Creates draft emails. Comment this out when you are ready
        '.Send     '********** UN-comment this when you  are ready to go live
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i


ActiveWorkbook.Save


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

This is what the worksheet looks like. You can change the Columns as required so long as you edit the range references in the code :


Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#376091]

X = Send​

[/td][td=bgcolor:#376091]

Customer Name​

[/td][td=bgcolor:#376091]

Email Address​

[/td][td=bgcolor:#376091]

Bonus​

[/td][td=bgcolor:#376091]

Email Sent​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

​

[/td][td]

Amit​

[/td][td]

me1@yahoo.com​

[/td][td]

$1,000.00​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

x​

[/td][td]

Jack​

[/td][td]

me2@yahoo.com​

[/td][td]

$2,000.25​

[/td][td]Mail Sent 8/31/2017 8:52:13 PM[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

x​

[/td][td]

Jill​

[/td][td]

me3@yahoo.com​

[/td][td]

$3,000.00​

[/td][td]Mail Sent 8/31/2017 8:52:13 PM[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Smitty]

If your e-mail client is Outlook, see Excel MVP Ron DeBruin's site. He's got all the methods you'll ever need.

I would strongly advise against using a Checkbox though, and try an actual font character like an X, as Logit used. You can easily format a "Check" column as Marlett, then use "a", which is a checkmark in that font. Or use a Data Validation drop-down. Either option lets you trigger a VBA Change Event to initiate your emails one at a time instead of bulk.

 

[My Aswer Is This]

Logit has a good plan. Putting a check box in every row and then assigning a script to every check box is not a good plan.

Now if you wanted to just send one piece of email at a time you could remove all the x marks or use a double click command or other ways. You should never need a large number of controls like command buttons check boxes and comboboxes on your sheet to do things.

 

[Helloall]

This will be very helpful thank you! Another question if you do not mind-

Would this be able to be set up using a macro button? And, if the X is not checked it will not send an email correct? Lastly, will the x need to be unchecked for continuous use to avoid it from sending out duplicate emails every time the sheet is updated?

Again, thanks for the help on this.

 

[Logit]

.
To use a Command Button for running the macro :

On the Menu Bar up top, select DEVELOPER / INSERT.

In the small window that show, left click on the small button symbol, upper left corner.

Move cursor to location on sheet where you want the button, left click again.

In the small window that appears, click on the macro name ( eMail ).

Click OK.

You can now run the macro by clicking on the button.


Regarding the X for sending emails. If the X is present, the email will be sent for that row. If the X is not present, no email for that row will be sent.

 

[My Aswer Is This]

Edit.

 

[Helloall]

Sent you a PM. Thanks for your help, as well as all others who have commented.

If you or others could just help clear up the last few questions I think I am good to go:



1. In the code line- "lRow = Cells(Rows.Count, 4).End(xlUp).Row" Do I need to change the row count so it reads infinitely? If so how do I do so?

2. Do I need to remove the comments I.E. The underlined "If (Cells(i, 22)) <> "" Then '<--------------------------------------------- change this range"

3. Can you explain what I am to do here? Underlined: .Display ' ********* Creates draft emails. Comment this out when you are ready
'.Send '********** UN-comment this when you are ready to go live

 

[Logit]

.
HelloAll : Your mailbox is saying it's full and cannot accept any more private messages.

Here are the answers you were seeking ...

ANSWERS:

#1 - Simple answer - No.

The "End(xlUp)" tells VBA to look as far down the column as there is data. When it finds the last cell that contains data, it knows where the bottom
of the column is, then it starts looking at all the column data going back up the column. The number 4 indicates Column D. You can edit the 4 to any column you desire by changing
the number accordingly. Col A is 1; Col B is 2, etc. etc.

#2 - Simple answer - No.

Anything in code the follows a single quotation mark [ ' ] tells VBA to disregard. This is useful for adding instructions or comments to future programmers or providing an explanation
of what the code does.

#3 - The code .Display means just that. The email will be created but not sent. The email will appear on screen for review prior to sending. You will be given the opportunity to send it with a button click
on the email after you have reviewed it.

The code .SENDdoes just that. It automatically sends the email. You are not given an opportunity to review it ... it is not displayed on screen prior to sending.
Remember the use of the single quotation mark. If you want to automatically send the email, place a single quotation mark before .Display .... like this ... '.Display and then
uncomment .Send .. remove the single quotation mark in front of it. Or do the opposite if you want to view the email before sending.[/COLOR]