Auto Run macros when workbook is opened (Call out from different sheets)

TSawyer1226 

Move the following code to a new or existing standard module.

Dim xOutApp As Object

'This is the main function
Sub notify()
    Set xOutApp = CreateObject("Outlook.Application")
    Dim rng As Range
    For Each rng In Worksheets("Level III Inspections").Range("E3:E8")
        If rng.Text = "YES" Then
            Call mymacro
        End If
    Next rng
    Set xOutApp = Nothing
End Sub
'-----------------------------------------------------------------------

  'This is the function that sends an email when called by the main function

Private Sub mymacro()
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello Everyone," & vbNewLine & vbNewLine & _
              "This is an automated message to let you know there is a Level III inspection due for one of the rigs. " & _
              "Please contact Taylor for more information or check the Level III Inspection excel sheet within our G:Drive." & vbNewLine & _
              "Thanks!"
    On Error Resume Next
    With xOutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "Level III Inspection Due"
        .Body = xMailBody
        .Send   'or use .Send or .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
End Sub

In the ThisWorkbook module:

Private Sub Workbook_Open()
    Call notify
End Sub