使用 Excel VBA 选择 Outlook 文件夹

Select Outlook Folder With Excel VBA

提问by Mark S

I'm trying to bypass having to select the folder I want and just tell Excel to go ahead and count the "Inbox"

我试图绕过必须选择我想要的文件夹，然后告诉 Excel 继续计算“收件箱”

Sub Get_Emails()

Dim OLF As Outlook.MAPIFolder
Dim EmailItemCount As Long

Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").PickFolder
EmailItemCount = OLF.Items.Count

Range("A1") = EmailItemCount

Set OLF = Nothing

Application.StatusBar = False

End Sub

Does anyone know how I can just get the count without having to select the folder? Excel VBA should just automatically go into the "Inbox" and give me my count.

有谁知道如何无需选择文件夹即可获得计数？Excel VBA 应该会自动进入“收件箱”并给我计数。

Note: You have to go to Tools > References > and select "Microsoft Outlook 14.0 Object Library" in order for this macro to work.

注意：您必须转到“工具”>“参考”> 并选择“Microsoft Outlook 14.0 对象库”才能使此宏工作。

回答by Vityata

Here is something that works:

这是有效的方法：

Option Explicit

Sub LoopFoldersInInbox()

    Dim ns              As Outlook.Namespace
    Dim myfolder        As Outlook.Folder
    Dim mysubfolder     As Outlook.Folder

    Set ns = GetObject("", "Outlook.Application").GetNamespace("MAPI")

    Set myfolder = ns.GetDefaultFolder(olFolderInbox)

    For Each mysubfolder In myfolder.Folders
        Debug.Print mysubfolder.name
        Debug.Print mysubfolder.Items.Count
    Next mysubfolder

End Sub

With some credits here. It is with early binding. Thus, if you press the dot in nsor mysubfolderyou will see the properties and the actions they have:

这里有一些学分。有早装。因此，如果您按 inns或中的点，mysubfolder您将看到它们的属性和操作：

Here is the late binding, thus you do not need to refer to the Outlook Library explicitly and the code would work on more users:

这是后期绑定，因此您不需要显式引用 Outlook 库，代码将适用于更多用户：

Option Explicit

Sub LoopFoldersInInbox()

    Dim ns                  As Object
    Dim objFolder           As Object
    Dim objSubfolder        As Object

    Set ns = GetObject("", "Outlook.Application").GetNamespace("MAPI")
    Set objFolder = ns.GetDefaultFolder(6) ' 6 is equal to olFolderInbox

    For Each objSubfolder In objFolder.Folders
        Debug.Print objSubfolder.name
        Debug.Print objSubfolder.Items.Count
    Next objSubfolder

End Sub

In this late binding, I have used 6in stead of olFolderInbox.

在这个后期绑定中，我使用6了olFolderInbox.

Edit: If you want the results in the cells, use this code:

编辑：如果您想要单元格中的结果，请使用以下代码：

Option Explicit

Sub LoopFoldersInInbox()

    Dim ns                  As Object
    Dim objFolder           As Object
    Dim objSubfolder        As Object
    Dim lngCounter          As Long

    Set ns = GetObject("", "Outlook.Application").GetNamespace("MAPI")
    Set objFolder = ns.GetDefaultFolder(6) ' 6 is equal to olFolderInbox

    For Each objSubfolder In objFolder.Folders
        With ActiveSheet
            lngCounter = lngCounter + 1
            .Cells(lngCounter, 1) = objSubfolder.Name
            .Cells(lngCounter, 2) = objSubfolder.Items.Count
        End With

        Debug.Print objSubfolder.Name
        Debug.Print objSubfolder.Items.Count

    Next objSubfolder

End Sub

回答by Mark S

The below is more of what I am looking for but Vityana's code works very well too. It all depends on what you need. I would like to specify a folder within the "Inbox" but am currently unable to. This only gets the count for the "Inbox" but there are folders nested under the "Inbox" folder that I am unable to specify. Anyone know how to do that?

以下是我正在寻找的更多内容，但 Vityana 的代码也运行良好。这一切都取决于你需要什么。我想在“收件箱”中指定一个文件夹，但目前无法指定。这只会获取“收件箱”的计数，但在“收件箱”文件夹下嵌套了一些我无法指定的文件夹。有谁知道怎么做？

Sub HowManyEmails()
Dim objOutlook As Object, objnSpace As Object, objFolder As Object
Dim EmailCount As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objnSpace = objOutlook.GetNamespace("MAPI")

On Error Resume Next
Set objFolder = objnSpace.Folders("[email protected]").Folders("Inbox")
If Err.Number <> 0 Then
Err.Clear
MsgBox "No such folder."
Exit Sub
End If

EmailCount = objFolder.Items.Count
Set objFolder = Nothing
Set objnSpace = Nothing
Set objOutlook = Nothing

[B2].Value = EmailCount

End Sub

回答by Ron L11

You can just "continue the specification".

您可以“继续规范”。

You had:

你有过：

Set objFolder = objnSpace.Folders("[email protected]").Folders("Inbox")

To get -for example- the content of the subfolder Temp under the Inbox, specify:

例如，要获取收件箱下子文件夹 Temp 的内容，请指定：

Set objFolder = objnSpace.Folders("[email protected]").Folders("Inbox").Folders("Temp")

Hope this helps

希望这可以帮助