Forum Discussion
TSawyer1226
Oct 28, 2022Copper Contributor
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.
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
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
- TSawyer1226Copper 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.
āā
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.