MS-Excel VBA代码，用于访问MS-Outlook中的特定文件夹并按顺序提取附件 [英] MS-Excel VBA code to access a specific folder in MS-Outlook and extract the attachments in order

问题描述

你好，

这是我在这里的第一个请求。我正在Excel中编写一些VBA代码以访问Outlook中名为  ScannedDocs的特定文件夹。

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

我在Outlook中有一条规则，将来自复印机/扫描仪的所有邮件保存到ScannedDocs中。

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

然后我让Excel VBA代码访问此文件夹  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.

我的问题是：

A）我有这行代码：

 设置OlFolder = olNameSpace.GetDefaultFolder（olFolderContacts）。文件夹（"ScannedDocs"）

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

给我一​​个运行时错误-2147221233（8004010f）尝试的操作失败。无法找到对象。

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

OlFolder声明为  Outlook.MAPIFolder

OlFolder declared as Outlook.MAPIFolder

我在相关行的正上方有一行代码：  设置olNameSpace = OlApp.GetNamespace（" MAPI"）并且我已声明  olNameSpace  as  Outlook.Namespace和  OlApp声明为  Outlook.Application。

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.

当我运行Excel中的代码时，MS-Outlook正在运行，并且文件夹 下  [e-mail]，我可以在Outlook的名为  ScannedDocs的文件夹窗格中看到它。

MS-Outlook is running while I run the code from Excel is running, and a folder  under  [e-mail] 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）我有要按照Excel工作表中的顺序扫描的文件。我希望Excel VBA代码处理带有第一个附件的第一封电子邮件，并将附件命名为第一个单元格中显示的名称，然后将下一个带有第二个附件的电子邮件
消息命名为附件，并将附件命名为出现在第二个单元格中，这将继续，直到所有附件都已提取并根据Excel中的名称列表命名。如果我使用For Each循环，如何知道电子邮件
消息的处理顺序？或者我应该使用For Next循环，因为我将根据Excel工作表范围中的名称数知道需要处理多少条消息？我还想知道是否有一种方法可以按接收时间对
电子邮件进行排序，如果第二次分开将使电子邮件与Excel中的列表按顺序匹配。

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.

提前感谢一百万，每个Outlook项目我都会了解有关Outlook VBA的更多信息。 

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

推荐答案

Hello Waseem，

Hello Waseem,

A）我建议打破属性和方法调用链并在单独的代码行上声明每个调用，这样你就能找到哪个调用完全失败或者给出了错误。

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.

请注意，OOM中的商店类也提供 GetDefaultFolder
方法。 此方法类似于  GetDefaultFolder
NameSpace 对象。区别
是此方法获取与该帐户关联的交货商店的默认文件夹，而
NameSpace.GetDefaultFolder 返回当前配置文件的默认商店中的默认文件夹。如果您在Outlook中配置了多个商店，则需要先找到所需的商店，然后只需
尝试获取目标文件夹。

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.

此外，您可以遍历所有子文件夹并创建一个文件夹（如果它尚不存在）。

Folder.Folders 属性r eturns  文件夹
集合，表示指定的所有文件夹 文件夹 。

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）

>或者我应该使用For Next循环，因为我将根据Excel工作表范围中的名称数知道需要处理多少条消息？

> 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?

您在正确的大道上。 For / Next正是您所需要的。

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

>我还想知道是否有一种方法可以按接收时间对电子邮件进行排序，如果有一种方法可以使电子邮件与Excel中的列表按顺序匹配。

> 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.

GetTable 方法允许获得    表 包含父项中的项目
文件夹 那个符合
过滤器 中的条件。默认情况下， TableContents 是
olUserItems 和返回的
表格 仅包含未隐藏的已过滤项目。

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.

如果  过滤器是一个空白字符串，或省略
过滤器参数， GetTable 返回
表，其中行代表
中的所有项文件夹。如果过滤器是一个空字符串，或者省略了
过滤器参数，并且 TableContents 是
olHiddenItems ，
GetTable 返回表，行代表
文件夹中的所有隐藏项目。

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

排序类的方法允许s 输出 
表 按
SortProperty 并将当前行重置为
表中的第一行之前。 SortProperty 可以是任何显式内置属性或自定义属性，但二进制和多值属性除外。该属性必须通过其显式字符串名称引用
;它不能被命名空间引用。有关指定排序属性的更多信息，请参阅
对文件夹中的项目进行排序 。

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

这篇关于MS-Excel VBA代码，用于访问MS-Outlook中的特定文件夹并按顺序提取附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！