Excel VBA userform选择要拷贝的Outlook文件夹

I am trying to create a user form that will allow the user to select the folder to copy a set of emails from to an excel spreadsheet. I have done all the rest (ie created the copy process) but currently I have to manually enter the namespace and folder hierarchy for each new installation of this macro. Below is my manual process

我正在尝试创建一个用户表单，允许用户选择文件夹来从一个excel电子表格中复制一组邮件。我已经完成了所有剩下的工作(即创建了复制过程)，但是目前我必须为这个宏的每个新安装手动输入名称空间和文件夹层次结构。下面是我的手工流程

Set ol_App = New Outlook.Application
Set ol_Namespace = ol_App.GetNamespace("MAPI")
' Set ol_Folder = olNamespace.GetDefaultFolder(olFolderInbox)

' reference the folder that the emails are stored in
Set ol_Folder = ol_Namespace.Folders("Their own namespace")
Set ol_Folder = ol_Folder.Folders("Inbox")
Set ol_Folder = ol_Folder.Folders("Required_Folder")

Now this vba will be shared among a fair few people and each person has a different setup. Is there a way I can set this up in a userform using say a list-box and all they do is select the correct folder and click continue and the folder selection is stored in a variable or some sort?

现在，这个vba将被分享在一个公平的少数人和每个人有不同的设置。有没有一种方法我可以用一个列表框把它设置成一个userform他们所做的就是选择正确的文件夹然后点击continue文件夹选择被存储在一个变量或某种排序中?

Thank you in advance,

谢谢你提前,

2 个解决方案

#1

Is this what you are trying? This will also negate the need to use a listbox. :)

这就是你想要的吗?这也将否定使用列表框的必要性。:)

Option Explicit

'~~> Set a reference to Outlook Object x.x Library
Sub Sample()
    Dim oOlApp As Outlook.Application
    Dim objNmSpc As Namespace
    Dim ofldr As Object

    Set oOlApp = Outlook.Application
    Set objNmSpc = oOlApp.GetNamespace("MAPI")
    Set ofldr = objNmSpc.PickFolder

    If Not ofldr Is Nothing Then MsgBox ofldr
End Sub

And here is via Late Binding i.e, if you do not want to add the reference to Outlook Object x.x Library

这是通过后期绑定i。e，如果不想添加对Outlook对象x的引用。x库

Option Explicit

Sub Sample()
    Dim oOlApp As Object, objNmSpc As Object, ofldr As Object

    '~~> Establish an Outlook application object
    On Error Resume Next
    Set oOlApp = GetObject(, "Outlook.Application")

    If Err.Number <> 0 Then
        Set oOlApp = CreateObject("Outlook.Application")
    End If
    Err.Clear
    On Error GoTo 0

    Set objNmSpc = oOlApp.GetNamespace("MAPI")
    Set ofldr = objNmSpc.PickFolder

    If Not ofldr Is Nothing Then MsgBox ofldr
End Sub

EDIT:

编辑:

SNAPSHOT

快照

#2

What you want to do is loop through the Outlook Folders and have each Folder name populate the listbox /combobox in the activate event of the form. By doing that, as each person runs it, their own Outlook configuration will be what is used.

您要做的是循环遍历Outlook文件夹，并让每个文件夹的名称在表单的激活事件中填充listbox /combobox。通过这样做，每个人都运行它，他们自己的Outlook配置将被使用。

This link should give a good start Outlook Folder Loop

这个链接应该给出一个良好的开始Outlook文件夹循环