none
MS-Excel VBA code to access a specific folder in MS-Outlook and extract the attachments in order RRS feed

  • Question

  • Hello,

    This is my first request here. I am writing some VBA code in Excel to access a specific folder in Outlook called ScannedDocs.

    I have a rule in Outlook that saves all messages from our copier/scanner into that ScannedDocs.

    Then I have the Excel VBA code access this folder ScannedDocs and extract all the attachments, name them according to a list of strings in a worksheet.

    My questions are:

    A) I have this line of code:

     Set OlFolder = olNameSpace.GetDefaultFolder(olFolderContacts).Folders("ScannedDocs")

    giving me a Run-time error -2147221233 (8004010f) The attempted operation failed. An object could not be found.

    OlFolder declared as Outlook.MAPIFolder

    I have a line of code right above the line in question as: Set olNameSpace = OlApp.GetNamespace("MAPI") and I have declared olNameSpace  as Outlook.Namespace and OlApp declared as Outlook.Application.

    MS-Outlook is running while I run the code from Excel is running, and a folder  under  name@compnay.com and I can see it in the folder pane in Outlook that is named ScannedDocs.

    What is going on, and how do I fix this?

    B) I have the documents to be scanned in order as they appear in the Excel worksheet. I want the Excel VBA code to process the first email message with the first attachment and name the attachment the name that appears in the first cell, then the next email message with the second attachment and name the attachment the name that appears in the second cell, and this will go on until all attachments have been extracted and named according to the list of names in Excel. How do I know the order in which the email messages will be processed if I use a For Each loop? Or should I use a For Next loop since I will know how many messages I need to process based on the number of names in the Excel worksheet range? I also would like to know if there is a way I can sort the email messages by receive time, and if a second apart will make the emails match in order with the list in Excel.

    Thanks a million in advance, with each Outlook project I learn more about Outlook VBA. 

    Wednesday, April 25, 2018 4:42 PM

All replies

  • Hello Waseem,

    A) I'd suggest breaking the chain of property and method calls and declaring each of them on a separate line of code, so you will be able to find which call exactly fails or gives an error.

    Be aware, the Store class from the OOM provides the GetDefaultFolder method too. This method is similar to the  GetDefaultFolder method of the NameSpace object. The difference is that this method gets the default folder on the delivery store that is associated with the account, whereas NameSpace.GetDefaultFolder returns the default folder on the default store for the current profile. If you have multiple stores configured in Outlook you need to find the required store first and only then try to get the target folder there.

    Also you may iterate over all subfolders and create a folder if it doesn't exist there yet. The Folder.Folders property returns the  Folders collection that represents all the folders contained in the specified Folder.

    B)

    > Or should I use a For Next loop since I will know how many messages I need to process based on the number of names in the Excel worksheet range?

    You are on the right avenue. For/Next is exactly what you need.

    > I also would like to know if there is a way I can sort the email messages by receive time, and if a second apart will make the emails match in order with the list in Excel.

    The GetTable method of the Folder class allows to get a  Table that contains items in the parent Folder that meet the criteria in Filter . By default, TableContents is olUserItems and the returned Table contains only the filtered items that are not hidden.

    If  Filter is a blank string or the Filter parameter is omitted, GetTable returns a Table with rows representing all the items in the Folder . If Filter is a blank string or the Filter parameter is omitted and TableContents is olHiddenItems , GetTable returns a Table with rows representing all the hidden items in the Folder .

    For more information on filters, see Filtering Items andReferencing Properties by Namespace.

    Sub DemoTable()  
        'Declarations  
        Dim Filter As String  
        Dim oRow As Outlook.Row  
        Dim oTable As Outlook.Table  
        Dim oFolder As Outlook.Folder  
    
        'Get a Folder object for the Inbox  
        Set oFolder = Application.Session.GetDefaultFolder(olFolderInbox)  
    
        'Define Filter to obtain items last modified after May 1, 2005  
        Filter = "[LastModificationTime] > '5/1/2017'"  
        'Restrict with Filter  
        Set oTable = oFolder.GetTable(Filter)  
    
        'Enumerate the table using test for EndOfTable  
        Do Until (oTable.EndOfTable)  
            Set oRow = oTable.GetNextRow()  
            Debug.Print (oRow("Subject"))  
            Debug.Print (oRow("LastModificationTime"))  
        Loop  
    End Sub

    The Sort method of the Table class allows to sort the rows of the  Table by the property specified in SortProperty and resets the current row to just before the first row in the Table . SortProperty can be any explicit built-in property or custom property, with the exception of binary and multi-valued properties. The property must be referenced by its explicit string name; it cannot be referenced by namespace. For futher information on specifying sort properties, see Sorting Items in a Folder.

    Sub SortTableByReceivedTime() 
    
     Dim oT As Outlook.Table 
    
     Dim oRow As Outlook.Row 
    
     Set oT = Session.GetDefaultFolder(olFolderInbox).GetTable 
    
     'Add normalized subject (subject without RE:, FW: and other prefixes)to the column set 
    
     oT.Columns.Add ("http://schemas.microsoft.com/mapi/proptag/0x0E1D001E") 
    
    
    
     'Sort by ReceivedTime in descending order 
    
     oT.Sort "[ReceivedTime]", True 
    
    
    
     Do Until oT.EndOfTable 
    
     Set oRow = oT.GetNextRow 
    
     'Print the normalized subject of each row 
    
     Debug.Print oRow("http://schemas.microsoft.com/mapi/proptag/0x0E1D001E") 
    
     Loop 
    
    End Sub


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Wednesday, April 25, 2018 6:16 PM
  • Hello WaseemN,

    >>a folder  under  name@compnay.com and I can see it in the folder pane in Outlook that is named ScannedDocs.

    Where is the folder? Your code is trying to find a "ScannedDocs" sub folder in your contacts folder. I think it should not be this, right?

    According to your description, it seems that the folder in a sub folder in specific account which is the same level as an Inbox, Draft. 

    If so, I would suggest you try below code. If not, please detail the folder location more detail. A screenshot will be more helpful.

    Set olFolder = olNamespace.Folders("name@compnay.com ").Folders("ScannedDocs")

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 6:11 AM
  • Thank You Eugene, and Terry

    I read your reply very intently. Now I have this to report Terry's suggestion worked. And I am sorry for the mishap with the Contacts before, it was my Copy and Paste glitch.

    The code now executes after I adopted Terry's suggestion.

    Thanks to both of you again

    Thursday, April 26, 2018 3:45 PM
  • Thank You Eugene, and Terry

    I read your reply very intently. Now I have this to report Terry's suggestion worked. And I am sorry for the mishap with the Contacts before, it was my Copy and Paste glitch.

    The code now executes after I adopted Terry's suggestion.

    Thanks to both of you again

    Thursday, April 26, 2018 3:45 PM
  • Hello WaseemN,

    I'm glad to hear that you have solved the issue. I would suggest you mark helpful reply as answer to close this thread.

     

    If you have other issue, pelase feel free to post threads to let us know.

     

    Thanks for understanding.

     

    Best Regards,

     

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 1:42 AM