I created an Excel VBA that check for data in the cells and send email with WorkBook_Open().

Option Explicit

Private Sub Workbook_Open()
        
        'Declaring variables
        Dim notifyEmailApplication As Object
        Dim notifyEmailContent As Object
        Dim triggerEmailApplication As Object
        Dim triggerEmailContent As Object

        'Create email object
        Set notifyEmailApplication = CreateObject("Outlook.Application")
        Set notifyEmailContent = notifyEmailApplication.CreateItem(0)
        Set triggerEmailApplication = CreateObject("Outlook.Application")
        Set triggerEmailContent = triggerEmailApplication.CreateItem(0)

        ...

I then created a VBScript to run the Excel file.

Call ExcelMacro

Sub ExcelMacro() 

    Set xlApp = CreateObject("Excel.Application") 
    
    xlApp.Visible = True    
    xlApp.DisplayAlerts = False

    Set xlBook = xlApp.Workbooks.Open("....\Email Automation.xlsm", 0, False)       
    xlBook.Close    
    Set xlBook = Nothing
    
    xlApp.Quit

    Set xlApp = Nothing

End Sub 

I also created a cmd file to run the VBScript on cscript.exe

cscript.exe "....\vbscript.vbs"
exit

Whenever I trigger the cmd file manually (double clicking it), the Excel Macro runs perfectly and successfully send email to the designated person.

But when I use Task Scheduler to run the cmd file, the Excel Macro does not run successfully and this line was highlighted.

Set notifyEmailApplication = CreateObject("Outlook.Application")

Notes: I already viewed a lot of forums and didn't find a fix:

1. In 'dcomcnfg' I already set Outlook Message Attachment to Interactive User
2. I tried changing Dim notifyEmailApplication As Object to Dim notifyEmailApplication As Outlook.Application, same line is highlighted
3. I already added Outlook Object Library as reference in Excel VBA

CodePudding user response：

But when I use Task Scheduler to run the cmd file, the Excel Macro does not run successfully

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you are building a solution that runs in a server-side context, you should try to use components that have been made safe for unattended execution. Or, you should try to find alternatives that allow at least part of the code to run client-side. If you use an Office application from a server-side solution, the application will lack many of the necessary capabilities to run successfully. Additionally, you will be taking risks with the stability of your overall solution.

Read more about that in the Considerations for server-side Automation of Office article.

If you deal with open XML documents you may consider using the Open XML SDK instead, see Welcome to the Open XML SDK 2.5 for Office for more information. Also take a look at any third-party components designed for the server-side execution (they don't require Office installed on the system).

CodePudding user response：

I haven't touched this topic for many years, but as I remember, from a long time ago, the Task Scheduler allows, or requires, you to enter your Windows password and if the password is incorrect it won't notify you of the error, so it seems like it's working, but with the incorrect password, it won't work. Can you double-check that your Windows password is entered correctly?