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:

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

You can not call Outlook vba from Excel or any other Application — 0m3r, Apr 15 '18 at 03:36
According to the second link i provided, it is possible: https://stackoverflow.com/questions/5096353/call-outlook-vba-from-excel — Tlatwork, Apr 16 '18 at 06:37
@TarunLalwani: I am not sure I understand you correctly: I face the same challenge as posted above und was not able to run an outlook macro from R via RDCOMClient. Attempts listed above also didnt work for me,... — Tonio Liebrand, Apr 18 '18 at 18:55
There isn't a `Visible` property of the Outlook.Application object. Is that your question? — Ryan Wildry, Apr 19 '18 at 01:47
@BigDataScientist, please also update the Outlook version you are using — Tarun Lalwani, Apr 19 '18 at 04:34
For me it is Outlook 2016; @RyanWildry: Visible property is not relevant for me. I just want to trigger that an outlook macro is triggered from R via RDCOMClient. — Tonio Liebrand, Apr 19 '18 at 09:09
@BigDataScientist I'd be interesting seeing some documentation on that. Also, your question is really unclear on what your expected outcome is. Consider revising it. — Ryan Wildry, Apr 19 '18 at 11:46

score 3 · Answer 1 · answered Apr 19 '18 at 07:35

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

hmm,...first of all thanks for your effort. 1) Running: `OutApp$Run("dss")` returns `Error in .COM(x, name, ...) : Cannot locate 0 name(s) Run in COM object (status = -2147352570) ` as described in the question. Macro is placed as described in "ThisOutlook..." and runs properly if called in outlook. The macro itself is just an example. Reproducing it in R would not be the question at hand. Thank you! — Tonio Liebrand, Apr 19 '18 at 09:21
`os<-OutApp$Session$dss()` Try execute the procedure against the `Outlook.Application.Session` as above. — steve biko, Apr 19 '18 at 09:53
that returns: `Error in OutApp$Session$dss : object of type 'closure' is not subsettable`. Does that code work for you? — Tonio Liebrand, Apr 19 '18 at 11:23

Tarun Lalwani · Answer 2 · 2018-04-24T17:38:37.020

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)

Run Outlook VBA Code from R

2 Answers2

Two ways

1. In Outlook Application

2. In Excel Application