looking up a variable using index/match in an excel file using outlook VBA (code works in Excel but not in Outlook)

[nils7]

Good evening,

I am trying to write a VBA macro to be used within outlook. The macro picks the recipient from an email within an outlook folder (variable "recip"). I then want to use this variable to look something up in a list in an excel file. The return values for the lookup are in range E3:E870 in the tab "list", and the lookup values are in C3:C870 in the same tab. The lookup result is stored in the variable "fullpath".

I am pasting my code below. When I run this from within Excel VBA it runs correctly. However when I run it in Outlook VBA, I get an error message in the following line:

fullpath = Application.WorksheetFunction.Index(xlWkb.sheets("list").Range("E3:E870")... [the error is runtime error 438: Object doesn't support this property or method]

If i remove the xlWkb reference before .Sheets("list") in the same line, the error message i get is Sub or Function not defined

I am using MS Office 2010 on a Windows 7 machine. I can run other macros I have written in Outlook, i am just struggling to integrate Excel files in my VBA within Outlook.

For the purpose of this post i am just declaring a fixed value for "recip".

Thanks a lot for your help in advance.

Cheers,

Nico



Sub lookupinexcel()

Dim fullpath As String​

Dim xlApp As Object​

Dim xlWkb As Object​

Dim recip As String
​

recip = "A03"​

Set xlApp = CreateObject("excel.application")
xlApp.Visible = True

Set xlWkb = xlApp.Workbooks.Open("C:\Users\Nico\lookup.xlsx")

fullpath = Application.WorksheetFunction.Index(xlWkb.Sheets("list").Range("E3:E870"), Application.WorksheetFunction.Match(recip, xlWkb.Sheets("list").Range("C3:C870"), 0), 1)


MsgBox "looking up " & recip & " has returned the following path: " & fullpath

End Sub

 

[nils7]

This question has now been solved.

When using the Application.WorksheetFunction within Outlook, this refers to the Outlook application rather than Excel. That's the reason why the VBA code runs fine in Excel VBE but not in Outlook VBE.
Replacing "Application.WorksheetFunction" with xlApp.WorksheetFunction does the trick, and meanrs that the procedure uses Excel when referring to the WorksheetFunction.

Cheers

Nils