Running Python or PowerShell in VBA Outlook

Question

I am trying to run a Python script using VBA in Outlook. The Python script is to fetch data using REST API and write to file in the system. I am using VBA to orchestrate this with sending emails.

The Python script (get_people.py) runs from OS. It calls a REST API and gets a list of peoples names and writes to a file in OS.

I tried two methods:

Wrapping the Python execution in a batch file called getNames.bat:
```
#getNames.bat file
 C:\Python37\python.exe "C:\scripts\get_people.py"
```
and VBA to call this .bat file:

    Sub GetNames()

       Dim wsh as Object
       Dim waitOnReturn As Boolean: waitOnReturn = True
       Dim windowStyle As Integer: windowStyle = 1
       Dim sScript As String

       Set wsh = VBA.CreateObject("WScript.Shell")
       Set sScript = "C:\scripts\getNames.bat"
       wsh.Run sScript, windowStyle, waitOnReturn
       ... 
       'Rest is reading the content of the file that written by python script and send emails to someone.
       ...
    Exit Sub

However, the Python line does not run within VBA, but it runs if I manually execute from cmd.exe. In VBA the cmd screen pops up quickly and disappears.

Running Python scripts directly in VBA:

    Sub GetNames()

       Dim wsh as Object
       Dim waitOnReturn As Boolean: waitOnReturn = True
       Dim windowStyle As Integer: windowStyle = 1
       Dim sScript As String
       Dim PyExe As String
       Dim PyScript As String

       Set wsh = VBA.CreateObject("WScript.Shell")
       Set PyExe = "C:\Python37\python.exe"
       Set PyScript = "C:\scripts\get_people.py"

       wsh.Run PyExe & " " & PyScript

       ....
       'This one crashes Outlook
       ...

       Exit Sub

I have tried PowerShell commands instead of Python in the .bat file:

 #getNames.bat file
      C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command "Get-Date"

and this fails as well.

dodell dodell · Accepted Answer · 2019-05-15T10:17:35

I created code very similar to your initial case and it worked for me. Try putting a 'pause' at the end of the batch file.

trybat.bat

@echo off
echo "hello world"
pause

VBA code

Sub Test()

  Dim wsh As Object
  Dim waitOnReturn As Boolean: waitOnReturn = True
  Dim windowStyle As Integer: windowStyle = 1

  Set wsh = VBA.CreateObject("WScript.Shell")

  wsh.Run "C:\testdir\trybat.bat", windowStyle, waitOnReturn
  MsgBox "ran"
End Sub

Running Python or PowerShell in VBA Outlook

2 Answers