Windows Task Manager Schedule VBA Macro to Send An Email Using OutLook Daily Running Manually and Not Automatically

Question

I have my macro which is running, & for this I created a vb script and used windows task manager to schedule its run every day.

Whenever I run manually or attempt changing the time in the trigger, I always make sure that both excel and outlook are not running. If I run the macro in Excel VBA, it sends the email. After scheduling the task to run everyday, just as a test if I go to (in TaskScheduler) View -> Hidden Tasks and manually click Run, it sends the email.

However, if I schedule it to run at a specific time everyday, say maybe starting today, 15 minutes from now, it does not send the email & the last run result is (0x0).

I have enabled all macros in the trust center settings and selected 'Trust access to the VBA project object model', & it's not an issue about administrator privileges. This is my VB script

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("Excel.Application")
Set ObjWB = ObjExcel.Workbooks.Open("C:\Users\myUser\MyLocation\MyFile.xlsm")
ObjExcel.Visible = False
ObjExcel.DisplayAlerts = False
ObjExcel.AskToUpdateLinks = False
ObjExcel.AlertBeforeOverwriting = False

'vbs opens a file specified by the path below
'either use the Workbook Open event (if macros are enabled), or Application.Run

ObjExcel.Application.Run "MyFile.xlsm!main_macro"
ObjWB.Save
ObjWB.ActiveWorkbook.Close
ObjExcel.Quit

Set ObjWB = Nothing
Set ObjExcel = Nothing
WScript.Echo "Finished."
WScript.Quit

Also, here are my TaskScheduler Settings:

Ref:

Sending email from excel automatically based on date

How to set recurring schedule for xlsm file using Windows Task Scheduler

How can you run an Excel macro through a schedule task

Task Scheduler does not run Excel VBA Code to send PDF as Email Attachment

If I set the below to True:

ObjExcel.Visible = False
ObjExcel.DisplayAlerts = False
ObjExcel.AskToUpdateLinks = False
ObjExcel.AlertBeforeOverwriting = False

Excel opens in read-only mode and says click notify to receive the notification that you can now edit the workbook but I cannot edit it, says another user has the workbook open for editing.

I assume, you are logged in, while the task is supposed to run, right? — Julian Kuchlbauer
Is there an open Excel-Process in the background? Maybe your Script doesnt close your workbook cleanly. — Julian Kuchlbauer
I think the line "ObjWB.ActiveWorkbook.Close" should be "ObjWB.Close". ObjWB is already the workbook — Julian Kuchlbauer
If you run the script directly (the file, not the task), does there occur an error? — Julian Kuchlbauer
so now the script runs without errors, but the excel processes dont quit? — Julian Kuchlbauer

Julian Kuchlbauer Julian Kuchlbauer · Accepted Answer · 2016-11-15T14:40:00

I think the point is that the line

ObjWB.ActiveWorkbook.Close

must be

ObjWB.Close

.

Please try this and comment your results.

Windows Task Manager Schedule VBA Macro to Send An Email Using OutLook Daily Running Manually and Not Automatically

1 Answers