save outlook email attachment after looking up recipient in excel file
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:
Code:
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.
