save outlook email attachment after looking up recipient in excel file

[nils7]

Hi all,

I have been struggling with an Outlook VBA problem for a while, and after trying all sorts of things, I hope that someone here can help.

My problem is:

I have a VBA code that extracts attachments from email in a selected outlook folder and saves these. So far so good. I want to save the attachments into subfolders based on the name of the recipients. There are about 750 possible different recipients, and they should go into about 20 different paths. My plan was to use an exisiting excel file that lists recipients and the path against each name. I then wanted to extract the recipient from the file and lookup the path using index/match or some other lookup function. That value is then returned to the VBA procedure and used in the saving of the attachment.

I have most of the procedure working, but I am struggling with the bit that calls the excel file into the Outlook session to do the lookup. When I run just the lookup snippet in Excel VBE, it works fine - it opens the reference file, processes the lookup and returns the path as a string into the VBA procedure. However, when I try the same from within Outlook VBE, I get error codes. The reference file still opens but somehow the procedure of the lookup is unknown.
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

I am using Office 2010 on Windows 7.

The code is the following:

Sub lookupinexcel()

'This is the path that is the result of the lookup
  Dim fullpath As String
  
  Dim xlApp As Object
  Dim xlWkb As Object

  'this is the recipient from the email
  Dim recip As String

  'the recip is hard coded here for testing - in my final code this will be the name from the recipient
  recip = "A03"

   
  Set xlApp = CreateObject("excel.application")
  xlApp.Visible = True
  
  'this opens the excel workbook that has the reference in it 
  Set xlWkb = xlApp.Workbooks.Open("C:\Users\Nico\lookup.xlsx")
  
  'this is the lookup function using recip and looking it up in col C, the resulting path is in col E 
  fullpath = Application.WorksheetFunction.Index(xlWkb.Sheets("list").Range("E3:E870"), Application.WorksheetFunction.Match(recip,_
  xlWkb.Sheets("list").Range("C3:C870"), 0), 1)

  'the MsgBox is just for testing so I can see what the procedure has calculated 
  MsgBox "looking up " & recip & " has returned the following path: " & fullpath
   
  End Sub

I really hope someone might have an idea how I can get this to work.

Thanks a lot in advance for your help!

Nils

p.s: I had posted a question about this in the MrExcel forum last week, but noone seems to have an idea. Maybe it was too Outlook specific. So I thought I give it a try and post in here.

[gmayor]

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

The glaring issue is that this code makes two references to 'Application'. This is fine when you run in Excel, but when you run in Outlook, 'Application' is the Outlook application. You need to change it to refer to the Excel application ' xlApp'. If the line runs in Excel, it should then work when run from Outlook, but I don't have your data file to test.

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

[nils7]

Hi Graham,

That's it - the lookup works from within Outlook now, and I will be able to finish the rest of my macro.

Thank you so much for your quick and accurate help, very much appreciated!

Nils