Oct 28 2022 10:15 AM - edited Oct 28 2022 12:00 PM
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.
Oct 28 2022 12:23 PM
SolutionMove 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
Oct 31 2022 09:49 AM
@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.
Oct 31 2022 10:16 AM
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.
Oct 31 2022 10:37 AM
Oct 28 2022 12:23 PM
SolutionMove 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