使用Excel VBA选择Outlook文件夹 [英] Select Outlook Folder With Excel VBA

问题描述

我试图绕过不得不选择我想要的文件夹，并告诉Excel继续计算收件箱

  Sub Get_Emails（）
 
 Dim OLF As Outlook.MAPIFolder 
 Dim EmailItemCount As Long 
 
设置OLF = GetObject（，Outlook.Application ）.GetNamespace（MAPI）PickFolder 
 EmailItemCount = OLF.Items.Count 
 
范围（A1）= EmailItemCount 
 
设置OLF = Nothing 
 
 Application.StatusBar = False 
 
 End Sub 
  

有谁知道我只能得到计数而不必选择文件夹？注意：您必须转到工具>参考>并选择Microsoft Outlook 14.0对象，然后单击确定。图书馆为了使这个宏工作。

解决方案

这是一些有用的东西：

  Option Explicit 
 
 Sub LoopF​​oldersInInbox（）
 
 Dim ns As Outlook.Namespace 
 Dim myfolder As Outlook.Folder 
 Dim mysubfolder As Outlook.Folder 
 
设置ns = GetObject（，Outlook.Application）。GetNamespace（MAPI）
 
设置myfolder = ns.GetDefaultFolder（olFolderInbox）
 
对于每个mysubfolder在myfolder.Folders 
 Debug.Print mysubfolder.name 
 Debug.Print mysubfolder.Items.Count 
下一个mysubfolder 
 
 End Sub 
  

有些

这是后期绑定，因此您不需要明确地引用Outlook库和代码可以使用更多的用户：

  Option Explicit 
 
 Sub LoopF​​oldersInInbox（）
 
 Dim ns As Object 
 Dim objFolder As Object 
 Dim objSubfolder As Object 
 
设置ns = GetObject（，Outlook.Application）。GetNamespace（MAPI ）
设置objFolder = ns.GetDefaultFolder（6）'6等于olFolderInbox 
 
对于每个objSubfolder在objFolder.Folders 
 Debug.Print objSubfolder.name 
调试。打印objSubfolder.Items.Count 
下一个objSubfolder 
 
 End Sub 
  

在这个晚期绑定中，我使用了 6 代替 olFolderInbox 。

编辑：
如果你想要导致单元格，使用以下代码：

  Option Explicit 
 
 Sub LoopF​​oldersInInbox（）
 
 Dim ns As Object 
 Dim objFolder As Object 
 Dim objSubfolder As Object 
 Dim lngCounter As Long 
 
设置ns = GetObject（， Outlook.Application）GetNamespace（MAPI）
设置objFolder = ns.GetDefaultFolder（6）'6等于olFolderInbox 
 
对于每个objSubfolder在objFolder.Folders 
使用ActiveSheet 
 lngCounter = lngCounter + 1 
 .Cells（lngCounter，1）= objSubfolder.Name 
 .Cells（lngCounter，2）= objSubfolder.Items.Count 
 End With 
 
 Debug.Print objSubfolder.Name 
 Debug.Print objSubfolde r.Items.Count 
 
下一个objSubfolder 
 
 End Sub 
  

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

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.

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

解决方案

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 ns or mysubfolder you will see the properties and the actions they have:

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:

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 6 in stead of 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

这篇关于使用Excel VBA选择Outlook文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！