Outlook VBA change GetDefaultFolder dynamically

eggem01

New Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
I am working on a private Outlook project where I can read all emails from a specific account, starting in a specific folder, looking only in a specific folder, from a specific date etc.

Usually the default start folder is the InBox (in Dutch Postvak IN). To start there I would use the following line of code:
Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox)

I have a drop-down list with all the sub-folders in the InBox. So I would like to change this line of code dynamically and start the search from the chosen sub-folder.
Usually the code would be something like:
Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox).Folders("Main").Folders("Main-Sub") etc.

What I obviously don't know is the level of .Folders in the code. So this has to be dynamic. I could use a Case statement and code for lets say 10 levels, but that is not very dynamically.

I have created a small sub to explain what I mean. In this sub the line of code with "set = ..." is a string. But what I can do with the string I would also like to do with the set statement itself. I changed the sub a bit so it will work here at the forum.

Any suggestions are welcome.

Marcel

Sub Startfolder_Splitten()

Dim arr() As String
' Split de string in een array (i.e. "Hoofdfolder\Subfolder-1\Subfolder 1-1")
'arr = Split(UI_Emails_Inlezen.Range("cel_Te_Gebruiken_Startmap").Value, "\")
arr = Split("Hoofd\Sub\Subsub\SubSubSub\SubSubSubSub\SubSubSubSubSub", "\")

' Even elk item in de array printen naar het Directe venster
Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print i, arr(i)
Next

Dim aantal_niveaus As Long
aantal_niveaus = i
Debug.Print aantal_niveaus

'Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox) geeft standaard als default "Postvak IN of in het Engels InBox"
'0 Hoofdfolder
'1 SubFolder-1
'2
'Moet dus worden
'Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox).Folders("Hoofdfolder").Folders("Subfolder-1")

Dim formule As String
Dim geen_sub_niveau As String
Dim j As Long
formule = "Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox)"
geen_sub_niveau = "- Kies uit onderstaande lijst -"

If UI_Emails_Inlezen.Range("cel_Te_Gebruiken_Startmap").Value <> geen_sub_niveau Then
Debug.Print "Wel sub-niveau"

For j = 1 To i
formule = formule & ".Folders(""" & arr(j - 1) & """)"
Next j
Else
Debug.Print "Geen sub-niveau"
GoTo Einde
End If

Einde:

Debug.Print formule

'Dit werkt, maar hoe krijg ik dit in de VBA-code zelf

End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
The function here should work -

you'll get the full path from the dropdown and pass it to the function like this:
Set olparentfolder = GetFolderPath(foldervariable)
 

eggem01

New Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
Hello Diane,

Thanks for the link. This seems to be what I am looking for. However, problably due to my lack of sufficient skills, I can't seem to get it to work the way it should.
I uploaded my file. Maybe you can have a quick look where I am doing something wrong. The sub "Alle_Emails_Uit_Alle_Folders_En_Subfolders" is in module "A006_Emails_Inlezen_Alle_Sheets" and it calls the function "ProcessFolder".

I would like to use your Function in two possible places. One to determine the starting folder and the other to search only in a specific folder/folders.
Also I found a really nice course on Udemy from Daniel Strong called "Excel VBA & Microsoft Outlook Mastery and Automation". I enlisted and will start this course after in January. So in the future I hope to be able these kind of problems myself.
 

Attachments

  • Outlook.zip
    268.5 KB · Views: 0

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Ah... it (or excel) needs a little tweaking to make it work since the macro is written for Outlook's object model. You need to bind to the outlook object model, either using code or setting it in Tools > References. Even with it bound, Outlook macros may need a little tweaking to run from Excel.

While a function is good for reusing code, it might be easier to work it into the code to run it from Excel.

One to determine the starting folder and the other to search only in a specific folder/folders.
Folder picker might be better for this. You start the macro, pick the folder to use. The getfolderpath function is good for hardcoding the path in the macro.
Code:
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace

Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set olparentfolder = objNS.PickFolder
 

eggem01

New Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
Thanks for your help Diane.

I am already working on the part for the specific folder/folders and the code I came up with is working.
I simply split the FolderPath at "Inbox\" and in the array I use array(1) being the part after "Inbox". I then compare this with the folder I have asked to only look in. And if there is no array(1), which is the case if the script is searching the "Inbox" itself, it skips to the next email.

For the starting folder I guess I have to stick with the Pickfolder method.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Is the folder selected in Outlook before you run the macro? If so, you can pick up the active folder.
 

eggem01

New Member
Outlook version
Outlook 2016 32 bit
Email Account
POP3
Not likely since the macro would open Outlook itself if not already open. I guess the active folder would then be the InBox.

I have been considering a solution other than the PickFolder method but I don't really see a lot of options.
What I could imagine is a sort of combination between the starting folder and the folders I only want to search in.
There are 3 scenario's thinkable. 0, 1 or >1 searchfolders.
In case of 0 there is no need to change the starting folder. That should always be the InBox.
In case of > 1 it will be almost impossible to come up with a reliable starting folder.
In case of 1 I would then this should also be the starting folder.

In this last case I could split the folderpath (sub\subsub\subsubsub) and find the number of levels. And maybe then hardcode say 10 levels using the values in the array. A bit like in my very first example.
Something like:
if level =4 ...
if level =5 then Set olparentfolder = olAccount.DeliveryStore.GetDefaultFolder(olFolderInbox).Folders(arr(1)).Folders(arr(2)).Folders(arr(3)).Folders(arr(4)).Folders(arr(5))
if levell = 6 ...

I have no idea if this could work. Not really dynamic but would surely be good for the performance of the search.
 
Similar threads
Thread starter Title Forum Replies Date
M VBA to change flag status in outlook contact item Outlook VBA and Custom Forms 3
O VBA to Run Font Change on Outlook Startup Outlook VBA and Custom Forms 4
D Change sender name outlook vba 2010 Custom Userform Outlook VBA and Custom Forms 1
K Outlook 2013 - Use VBA to change Due date of Tasks Using Outlook 3
E Outlook 365 Outlook/VBA Outlook VBA and Custom Forms 11
J VBA for outlook to compare and sync between calendar Outlook VBA and Custom Forms 1
S vba outlook search string with special characters Outlook VBA and Custom Forms 1
U Outlook 2019 VBA run-time error 424 Outlook VBA and Custom Forms 2
G VBA to save selected Outlook msg with new name in selected network Windows folder Outlook VBA and Custom Forms 1
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
K Outlook Office 365 VBA download attachment Outlook VBA and Custom Forms 2
V vBA for searching a cell's contents in Outlook and retrieving the subject line Outlook VBA and Custom Forms 1
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
S Excel vba code to manage outlook web app Using Outlook 10
H Custom Outlook Contact Form VBA Outlook VBA and Custom Forms 1
S Problem Checking the available stores in my Inbox (Outlook VBA) Outlook VBA and Custom Forms 0
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
O VBA Outlook Message Attachment - Array Index Out of Bounds Outlook VBA and Custom Forms 0
J Want to learn VBA Macros for Outlook. What book can you recommend? Outlook VBA and Custom Forms 2
M Outlook 2013 reminder email by using Outlook vba Outlook VBA and Custom Forms 2
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
N How can I increase/faster outlook VBA Macro Speed ? Using Outlook 2
N Outlook Email Rule execution through shortcut keys (VBA codes) Using Outlook 1
A VBA Code in Outlook disappears after first use Outlook VBA and Custom Forms 1
dweller Outlook 2010 Rule Ignores VBA Script Outlook VBA and Custom Forms 2
G Outlook VBA and Google Calendar ("Events") Outlook VBA and Custom Forms 1
J VBA Outlook : Subject line : Cut and Paste name to heading , number to very end of the body of Email Outlook VBA and Custom Forms 1
B Advanced Search in MS Outlook by VBA and SQL Outlook VBA and Custom Forms 2
K Outlook Archive to PST Files by Date Range VBA Script? Outlook VBA and Custom Forms 1
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
S Find a cell value in excel using outlook vba Using Outlook 1
J Execute Add-In Button from VBA Outlook 2016 Outlook VBA and Custom Forms 1
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
M Slow VBA macro in Outlook Outlook VBA and Custom Forms 5
T Outlook AntiSpam with VBA Outlook VBA and Custom Forms 1
F "Move to" O365 feature to Outlook client via VBA Outlook VBA and Custom Forms 4
B query outlook using vba Outlook VBA and Custom Forms 13
J VBA to switch Outlook online/offline Outlook VBA and Custom Forms 4
T VBA outlook, detect priority emails Outlook VBA and Custom Forms 5
C Need VBA code to automatically save message outside outlook and add date Outlook VBA and Custom Forms 1
stephen li VBA Outlook send mail automatically by specified outlook mail box Outlook VBA and Custom Forms 1
S Outlook VBA Contacts Notes Outlook VBA and Custom Forms 0
P Outlook 2007 Email Categorization using VBA Outlook VBA and Custom Forms 1
O VBA or other solution for Outlook tasks to OneNote Outlook VBA and Custom Forms 0
S Automatically selecting folders and deleting messages in Outlook VBA Outlook VBA and Custom Forms 7
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1

Similar threads

Top