Forum Discussion

TSawyer1226's avatar
TSawyer1226
Copper Contributor
Oct 28, 2022

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

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.

  • 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
  • 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
    • TSawyer1226's avatar
      TSawyer1226
      Copper Contributor

      HansVogelaar 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.

      ā€ƒā€ƒ

       

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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.

Resources