Excel macro runs from Excel, but errors when called by Python script-CodePudding

I have an excel file that runs a macro. The macro runs with no problem when I run it from the Excel file. However, I want to open it and run the macro automatically each day. I know there are ways to do this other than Python, but I wanted to see if I could get it to work with a PY script. The PY script below manages to open my desired excel file and start the macro:

import win32com.client as win32
import os, os.path
import win32com.client
#from pathlib import Path

excel = win32.DispatchEx("Excel.Application") 
book = excel.Workbooks.Open(Filename=r'C:\Gov\ExcelFile.xlsm')
excel.Application.Run('ExcelFile.xlsm!Module1.MainMacro') 
book.Save()
book.Close()
excel.Application.Quit()

However, once the macro is running it stops and gives the following error:

Error Box

It says Run-time error '-2146959355 (80080005)': Server execution failed

When I run the debugger on the VBA code, it highlights the line: Set OutApp = CreateObject("Outlook.Application") in the below code: (There is more to the VBA script than teh below, but that's what the debugger shows when clicked).

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set rng = Nothing
Set rng = Sheets("BuildMain").Range("A1:E5000")
'You can also use a sheet name
'Set rng = Sheets("YourSheet").UsedRange

**Set OutApp = CreateObject("Outlook.Application")** 'THIS IS THE LINE THAT THE DEBUGGER HIGHLIGHTS. 
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Subject"
    .HTMLBody = RangetoHTML(rng)
    .Send   'or use .Display
End With
On Error GoTo 0

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing

CodePudding user response：

Run-time error '-2146959355 (80080005)': Server execution failed

The Considerations for server-side Automation of Office states the following:

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.

If you deal with Exchange accounts in Outlook you may consider using EWS or Graph API instead.

CodePudding user response：

CO_E_SERVER_EXEC_FAILURE means the COM system does not want to marshal calls between COM objects running in different security contexts. Even if you are not running in a service, make sure Outlook is either not running (then your script will start it) or that the security contexts match, i.e. neither or both apps are running with elevated privileges (Run As Administrator).