Assistance with a routine to be triggered based on specific days and time of the week

[Luthius]

Guys
I developed a spreadsheet that when the file is opened it sends an e-mail after 1 minute and then it auto close the workbook.

Application.OnTime Now + TimeValue("00:01:00"), "SendReport"

There are certain days that I need to send the report. So I created a task scheduled on Windows to send the report every
Wednesday, Friday, Saturday at 12:00:30 and on Sundays at 07:15:30.

Sometimes I will need to use the file for update some information inside or maybe manipulate some data as well.
I need to find a way to open this file and edit it without I send every minute an email with the report.
My Idea is to develop a routine that can verify if the Time and Day are different from what I have on Task Scheduler so the event Workbook_Open() will not send any email.

Below my general ideia. I need your assistance guys on how to make it works properly.

Private Sub Workbook_Open()    
    If TriggerVerifier Then
        Application.OnTime Now + TimeValue("00:01:00"), "SendReport"
    Else: End If
End Sub

Function TriggerVerifier() As Boolean
   
    TriggerVerifier = True
    Select Case Weekday(Date, 1)
    Case 1
        If Time < TimeValue("07:15:30") Or Time > TimeValue("09:46:45") Then
            TriggerVerifier = False
        End If
    Case 4, 6, 7
        If Time < TimeValue("12:00:30") Or Time > TimeValue("12:01:45") Then
            TriggerVerifier = False
        End If
    Case 2, 3, 5
            TriggerVerifier = False
    End Select
End Function

 

[Luthius]

The routine is that one. But I'm not sure if this is the best solution. Anyone can assist on this please?

 

[John_w]

We can use Windows API functions to read the Excel command line, and the Workbook_Open can take different actions depending on whether the workbook was opened by the Task Scheduler or not.

Put this code in a new standard module:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function w_commandline Lib "kernel32.dll" Alias "GetCommandLineW" () As LongPtr
    Private Declare PtrSafe Function w_strlen Lib "kernel32.dll" Alias "lstrlenW" (ByVal lpString As LongPtr) As Long
    Private Declare PtrSafe Sub w_memcpy Lib "kernel32.dll" Alias "RtlMoveMemory" (dst As Any, src As Any, ByVal size As LongPtr)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function w_commandline Lib "kernel32.dll" Alias "GetCommandLineW" () As Long
    Private Declare Function w_strlen Lib "kernel32.dll" Alias "lstrlenW" (ByVal lpString As Long) As Long
    Private Declare Sub w_memcpy Lib "kernel32.dll" Alias "RtlMoveMemory" (dst As Any, src As Any, ByVal size As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Public Function GetCommandLine() As String
    GetCommandLine = String$(w_strlen(w_commandline()), 0)
    w_memcpy ByVal StrPtr(GetCommandLine), ByVal w_commandline(), LenB(GetCommandLine)
    Debug.Print ">" & GetCommandLine & "<"
End Function

Change your Workbook_Open event handler to:

Private Sub Workbook_Open()
    If InStr(1, GetCommandLine, "TaskScheduler") Then
        'The Excel command line contains "TaskScheduler"
        Application.OnTime Now + TimeValue("00:01:00"), "SendReport"
    End If
End Sub

Next, in Task Scheduler create a Basic Task with the following Actions:

Start a program:
"C:\folder\path\to\EXCEL.EXE"

Arguments:
/x "C:\folder\path\to\Macro Workbook.xlsm" /TaskScheduler

Define the Triggers (scheduled times) as required.

The program path must be the full path to EXCEL.EXE, e.g. "C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE" and the Arguments setting must contain the full path to your macro workbook. Double quotes are needed where shown. The /x is an Excel command line switch which starts a new instance (a separate process) of Excel, preserving the specified command line if Excel is already open.