How to open Excel file saved in Outlook folder?

[Antonis]

Operating system::    Windows
Outlook version:     new Outlook
Email type or host:    Exchange

---

Hello,

I'm writing a VBA macro in Excel where I need to open an Excel file saved in an Outlook folder, make some modification and then save it.
Here's the last part of my code:

...
Set myFolder = myFolder.Folders("XXX XXX")

Dim myItem As Object
Set myItem = myFolder.Items("YYYYY.xlsx")

Could you please help?

Thank you.

 

[Diane Poremsky]

You need to 'find' the file, unless you select it first, then you can use the selection.

I did not test this (yet) so it might have some errors - and it assumes the file is in the folder, not as an attachment on a message.

If you have the exact name and there is no other match, this should work instead of getting the count and looping.
Set myItem = ResItems.Item(1)

    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")

Set olFolder = olNs.GetDefaultFolder(olFolderInbox)
Set myFolder = olFolder.Folders("subfoldername")
    Set olItems = myfolder.items
   
    exFile = "filename.xlxs"
 
    sFilter = "[Subject] = " & exFile
   
    Set ResItems = olItems.Restrict(sFilter)
     iNumRestricted = 0
   fCount = ResItems.Count
 
   'Loop through the items in the collection.
   For counter = fCount To 1 Step -1
 
Set myItem  = ResItems.Item(counter)
   iNumRestricted = iNumRestricted + 1

' do whatever
Debug.Print myItem.Subject

 

[Antonis]

You need to 'find' the file, unless you select it first, then you can use the selection.

I did not test this (yet) so it might have some errors - and it assumes the file is in the folder, not as an attachment on a message.

If you have the exact name and there is no other match, this should work instead of getting the count and looping.
Set myItem = ResItems.Item(1)

    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")

Set olFolder = olNs.GetDefaultFolder(olFolderInbox)
Set myFolder = olFolder.Folders("subfoldername")
    Set olItems = myfolder.items
  
    exFile = "filename.xlxs"
 
    sFilter = "[Subject] = " & exFile
  
    Set ResItems = olItems.Restrict(sFilter)
     iNumRestricted = 0
   fCount = ResItems.Count
 
   'Loop through the items in the collection.
   For counter = fCount To 1 Step -1
 
Set myItem  = ResItems.Item(counter)
   iNumRestricted = iNumRestricted + 1

' do whatever
Debug.Print myItem.Subject

Hi Diane,

Thanks a lot for your reply.
I have found the file and selected it but I don't know how to open it in Excel (what method/function to use) since it's an item.
Do you know how to do it?

Thank you.

 

[Diane Poremsky]

Testing it now -
if I know the position of the file, I can get the file:
Set myItem = olItems(3)

But the filter is not finding it or errors.

Oh, and the filter needs apostrophes -
sFilter = "[Subject] = '" & exFile & "'"

 

[Diane Poremsky]

I don't know how to open it in Excel (what method/function to use) since it's an item

If you just need to open it:
myItem.Display