Running subroutines based on formula value

[Aidan1000]

Hi, apologies Im a little new to this but I have a spreadsheet that is intended to automatically send an email based on the result of a formula. The code I have in ThisWorksheet for sending the email is:

Sub Test1()
    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("AH").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "AG").Value) = "yes" _
           And LCase(Cells(cell.Row, "AJ").Value) <> "yes" Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "J").Value _
                      & vbNewLine & vbNewLine & _
                        "Action. " & Cells(cell.Row, "AF").Value

                .Send
            End With
            On Error GoTo 0
            Cells(cell.Row, "AJ").Value = "yes"
            Cells(cell.Row, "AK").Value = Date
            Set OutMail = Nothing
        End If
    Next cell

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

 Private Sub Worksheet_Calculate()
   Worksheet_Change Range("AG:AG")
 End Sub

And in the sheet monitoring the value I have put this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AG1:AG100")) Is Nothing Then
    Call ThisWorkbook.Test1
    End If
 End Sub

Any help appreciated how I can get this to work automatically. Thanks

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...35822-running-subroutines-from-userforms.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[RoryA]

Welcome to the forum.

First off, I can't see any reason for the Test1 routine to be in the ThisWorkbook module. It really belongs in a normal module.

But anyway, you can simply add:

Thisworkbook.Test1

to whatever code you want to use to send the email - e.g. your Save and Close button code.