Run Outlook VBA Code from R

Question

I am aware of a potential shell/vbs script solution. However, I am seeking a solution with the library(RDCOMClient) package.

I looked into:

• Run VBA script from R

• Call outlook VBA from Excel

Some attempts from me (given a Public Sub dss() in ThisOutlookSession) in Outlook:

library(RDCOMClient)
> OutApp <- COMCreate("Outlook.Application")
> oa<-OutApp[["Session"]][["Accounts"]]
> OutApp$dss()
Error in .COM(x, name, ...) : 
  Cannot locate 0 name(s) dss in COM object (status = -2147352570)
> OutApp$Application$dss()
Error in OutApp$Application$dss : 
  object of type 'closure' is not subsettable
> OutApp$Run("dss")
Error in .COM(x, name, ...) : 
  Cannot locate 0 name(s) Run in COM object (status = -2147352570)

Macro could be simply:

Public Sub dss()
    Dim excApp As Object
    Dim excWkb As Object
    Dim excWks As Object

    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Add()

    excWkb.SaveAs "AXX.xlsx"
    excWkb.Close
End Sub

Answer 1

Two ways

1. In Outlook Application

Outlook.Application does not have a visible property, see this post -> http://www.vbaexpress.com/forum/archive/index.php/t-8287.html You will use a folder or mail item Display method to show outlook To display the outlook window add below code to your 'dss' macro ThisOutlookSession.ActiveExplorer.Display

Also you will need to put your public 'dss' macro inside outlook ThisOutLookSession to be able to call it out of outlook.

Then try your R code this way

    library(RDCOMClient)
    OutApp <- COMCreate("Outlook.Application")
    OutApp$Run("dss")

2. In Excel Application

If your macro is simply to create an Excel workbook why are you doing it in Outlook.Application use Excel.Application. Create an excel workbook with the macro in a module, and execute as below

    library(RDCOMClient)
    #Open a specific workbook in Excel:
    xlApp <- COMCreate("Excel.Application")
    xlWbk <- xlApp$Workbooks()$Open("C:\\Temp\\macro_template.xlsm")

    # this line of code might be necessary if you want to see your spreadsheet:
    xlApp[['Visible']] <- TRUE 

    # Run the macro called "dss":
    xlApp$Run("dss")

    # Close the workbook and quit the app:
    xlWbk$Close(FALSE)
    xlApp$Quit()`

Above is slight modified code from https://stackoverflow.com/a/43222477/5871610

Answer 2

As far as I can tell, it's not possible to run macros that are stored in Outlook from other applications. Outlook lacks the Application.Run method that is available in Word and Excel.

https://social.msdn.microsoft.com/Forums/office/en-US/5a6396c4-ad24-42a4-b711-101e24254334/how-to-fire-outlook-macro-through-excel-vba?forum=exceldev

So leave R out of the question here, I can assure you can't even execute this from the Outlook macro editor itself, starting with the top level Application object

I have tried every combination I could think of with the outlook macro and I am afraid this not possible now. It may have been in older version of outlook like 2007 or so but not now

If you can get this working in VBScript or outlook, it will work in R as well. But I am afraid you can't make it working anywhere

The possible workaround that you could do may be

• Define a rule with a trigger point and associate the macro with the same. Try to simulate the trigger instead of calling macro directly.
• Create a custom button in toolbar and see if it is possible to invoke the command from external vba (this will be really tough to crack)