CODEWITHSUNDEEP
excelvbaoutlook
Johan Rheeder
Johan Rheeder

Reputation: 319

Excel VBA userform to Select Outlook folder to copy from

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

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?

Thank you in advance,

Upvotes: 4

Views: 8293

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

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

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

enter image description here

Upvotes: 7

Scott Holtzman
Scott Holtzman

Reputation: 27259

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.

This link should give a good start Outlook Folder Loop

Upvotes: 0

Related Questions