How to send email via Excel when the value of cell is "Yes"

Question

I have an Excel table and a bit of macro. I wanted to automatically send email to a certain person when cell value == to "Yes". Also I want to send the email only if the date is today.

Please see screenshot:

Error Screenshot Sir

Private Sub cmdMove_Click()


    'Sub TestFile()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("J").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "H").Value) = "Yes" Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next

            With OutMail
                .To = ThisWorkbook.ActiveSheet("Server").Range("I3").Value

                .Subject = "Reminder"

                .Body = "Dear " & Cells(cell.Row, "Ryan").Value _
                      & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing " & _
                        "your account up to date"
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

End Sub

Answer 1

Try something like the following. Assumes Date is in column A and is an actual Date and that can be compared with what the Date function returns. There is a fair bit of tidying up that could be done on this.

I would take note of @BruceWayne's comment regarding using a Worksheet_Change event. If you can decide which cell(s), or column, determine(s) the triggering of the sub e.g. if column H has a value that changes then test each condition and determine whether to send e-mail, then you can call this sub via that event.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 8 Then 'e.g. for column H

       TestFile   'name of your sub

    End If

End Sub

Note I changed your LCase test as it could never be True with LCase = "Yes" and I used the typed function LCase$.

I have commented out the line for the body as this:

.Cells(cell.Row, "Ryan").Value

will throw an error. The "Ryan" part should be a column reference e.g. "A" or 1. If the "Ryan" is a named range then you might use something like:

.Cells(cell.Row, .Range("Ryan").Column)

Code:

Option Explicit

Public Sub TestFile()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim wb As Workbook

    Set wb = ThisWorkbook

    Application.ScreenUpdating = False

    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup

    With ActiveSheet

        For Each cell In .Columns("J").Cells.SpecialCells(xlCellTypeConstants)

            If cell.Value Like "?*@?*.?*" And _
               LCase$(.Cells(cell.Row, "H")) = "yes" And .Cells(cell.Row, "A") = Date Then

                Set OutMail = OutApp.CreateItem(0)

                With OutMail

                    .To = wb.Worksheets("Server").Range("I3").Value

                    .Subject = "Reminder"

                  '  .Body = "Dear " & .Cells(cell.Row, "Ryan").Value _
                          & vbNewLine & vbNewLine & _
                            "Please contact us to discuss bringing " & _
                            "your account up to date"
                    'You can add files also like this
                    '.Attachments.Add ("C:\test.txt")
                    .Display                     'Or use Display
                End With

                Set OutMail = Nothing

            End If
        Next cell

    End With

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Example of Worksheet_Event code in Sheet2 code window

And the associated standard module: