Calling Outlook Script by using an Excel Macro

0
votes

I have an Outlook rule, to run a VBA script, that sporadically runs into errors. I know the script works because I've tested the script as a Public Sub.

I don't know why my rule encounters errors since there is no error description for Outlook rule failures. For a workaround, I decided to get my Outlook script running by calling it from Excel.

The error I receive is:

Run-time error '438'. Object doesn't support this property or method.

The references I have on Excel are:

  1. Visual Basic For Applications
  2. Microsoft Excel 16.0 Object Library
  3. OLE Automation
  4. Microsoft Office 16.0 Object Library
  5. Microsoft Outlook 16.0 Object Library

Outlook references are:

  1. Visual Basic For Applications
  2. Microsoft Outlook 16.0 Object Library
  3. OLE Automation
  4. Microsoft Office 16.0 Object Library
  5. Microsoft Form 2.0 Object Library
  6. Microsoft Excel 16.0 Object Library

Code for Excel:

Public Sub testexcel()

Dim o As Outlook.Application

On Error Resume Next
Set o = GetObject("", "Outlook.Application")
Err.Clear: On error GoTo 0

If o Is Nothing then
Set o = CreateObject("Outlook.Application")
End If

With o
    .Session.Logon
    .Run "testoutlook" ' <--------- Error Line
End With

o.Close
o.Quit

Set o = Nothing
    
End Sub

Outlook sample code:

Public Sub testoutlook()

Call MsgBox("HellO")

End Sub
1
excelvbaoutlook

1 Answers

0
votes

After further research, I believe that the VBA coding compatibility between Outlook and Excel don't have developer functionality. However, I was able to complete my task by taking my VBA macro from Outlook and inserting into an Excel book.

Essentially, I removed any VBA coding from Outlook so everything was able to be completed using Excel. If anyone is interested in the codes please let me know.