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

[TSawyer1226]

Hello Everyone,

I am trying to have a macro run automatically when a workbook is opened. This macro was built to send an email out if certain criteria was met on a specific sheet and it currently works for that sheet when it is triggered; I would just like the "opening" of the workbook to now trigger it. I have tried to paste my notify/mail macro in the "ThisWorkbook" Object in the Visual Basic window with an updated reference (Sheet and Cell reference instead of just the Cells) as I have seen others do, but something isn't quite right.

I would like for someone to take a look and see if they can either help me update the references correctly so it works under the "ThisWorkbook" object or maybe have another code that just tells Excel to go to the sheet that has the Macro (working & referencing everything correctly) and run it.

I have attached a copy of my workbook. It will be Sheet 15 (Level III Inspections) that has the Notify and Email macros listed. Essentially, what I have going on is when operating days for a rig fall between a specific range of days it will return a "yes" and that yes value will then trigger an email to go out to notify a group of people of the rig status.

FYI I have removed all links and the emails from my mail macro for privacy purposes, just know that normally I would have legitimate emails there and cell links and reference would be active. Also, there are other macros listed in the workbook for other sheets and purposes.

**I uploaded the wrong workbook originally - Correct workbook is now attached**

Thank you.

[#2 · Accepted solution]

@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

[#3]

@Hans Vogelaar Thank you for your help! I did copy everything over but am coming up against a variable issue on line 21 of the code. I do not think it is pulling the right information from xOutApp Object set un the previous function.

I did move the xOutApp object from the notify function to the mymacro function just to test it and it did work but I wanted to let you know just in case there was a specific reason it was moved to the main notify function.

  

[#4]

@TSawyer1226 

Your original code created the Outlook.application object repeatedly inside the loop. That is inefficient. I moved the line to create that object to the notify macro, so that it is executed only once.

But a side effect is that you cannot call mymacro by itself, it only works when called from notify.

[#5]

I apologize - I thought I had been running the notify function not the mymacro one when I went to test it lol. I went back and made sure the notify function was selected and it worked just fine.

Thank you so much for your help and catching the loop!