This Outlook macro creates subfolders under the currently selected folder.
Use PowerShell to add or delete folders, using a list of folders in a text file: Create new Outlook folders using PowerShell
To use, create an Excel file with the desired folder names in one column with a header row. The folder names will begin with row 2 (cell A2). You can create the file in Notepad and save it with the CSV extension.
Use Set objParentFolder = objNewFolder to create nested folders.
Option Explicit
Public Sub MoveSelectedMessages()
Dim objParentFolder As Outlook.Folder ' parent
Dim newFolderName 'As String
Dim strFilepath
Dim xlApp As Object 'Excel.Application
Dim xlWkb As Object ' As Workbook
Dim xlSht As Object ' As Worksheet
Dim rng As Object 'Range
Set xlApp = CreateObject("Excel.Application")
strFilepath = xlApp.GetOpenFilename
If strFilepath = False Then
xlApp.Quit
Set xlApp = Nothing
Exit Sub
End If
Set xlWkb = xlApp.Workbooks.Open(strFilepath)
Set xlSht = xlWkb.Worksheets(1)
Dim iRow As Integer
iRow = 2
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
While xlSht.Cells(iRow, 1) <> ""
newFolderName = xlSht.Cells(iRow, 1)
On Error Resume Next
Dim objNewFolder As Outlook.Folder
Set objNewFolder = objParentFolder.Folders(newFolderName)
If objNewFolder Is Nothing Then
Set objNewFolder = objParentFolder.Folders.Add(newFolderName)
End If
iRow = iRow + 1
' make new folder the parent
' Set objParentFolder = objNewFolder
Set objNewFolder = Nothing
Wend
xlWkb.Close
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
Set objParentFolder = Nothing
End Sub
Create subfolders at multiple levels
This code snippet uses the folder name in Column 1 to set the parent folder, with the new folder name in Column 2. Note that the parent folder needs to be the last one created (or the Inbox).
However, because the macro checks for the existence of the folder and creates it only if it doesn't exist, you can walk the folders to create deep subfolders. (Note: I never recommend deeply nested subfolders, it's too easy to forget where they are.)
A complete copy of this macro is here
'select starting parent
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
Dim parentname
While xlSht.Cells(iRow, 1) <> ""
parentName = xlSht.Cells(iRow, 1)
newFolderName = xlSht.Cells(iRow, 2)
If parentName = "Inbox" Then
Set objParentFolder = Session.GetDefaultFolder(olFolderInbox)
Else
Set objParentFolder = objParentFolder.Folders(parentName)
End If
On Error Resume Next
Dim objNewFolder As Outlook.Folder
Set objNewFolder = objParentFolder.Folders(newFolderName)
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
More Information
Copy Folder Structure from Explorer into Outlook 2013
Bob R says
Hello Diane -
I'm trying to get this to work in an Archive folder, not in my Inbox. I move project related files from my Inbox to the Archive, organized by Customer > Project Number.
I've read through all the comments below and can't get the multi-level folders to set up properly. I'm using this code. The main folders are numbered [00] Proposal, [01] Project Plan etc up to [05] Deliverables. Each main folder has sub folders. However, when I run the code, all the main folders are nested within the [00] folder. Attached are screenshots of my input file and the results are attached.
Is there something I need to change if I'm doing this in the Archive folder, instead of the inbox?
I am creating the Project folder under the customer, then selecting it as the starting folder before running the macro.
Thanks!
Bob
malcolm larkin says
Hi,
I have managed to get the macro up and running, the problem i have is there are 4 email accounts in my outlook - i would like to run this on inbox3. Any ideas would be greatly apprecited
Diane Poremsky says
This line uses the selected folder -
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
if you want to run it on the entire mailbox, select the root folder - which is usually the email address. to add the folders as subfolders of the inbox, select the inbox.
Laura says
Is this the same for a shared inbox? I can get the code to work perfectly to create a two level folder structure in my own inbox, but I need to direct this to a shared inbox instead.
Diane Poremsky says
Because it uses the current folder - Set objParentFolder = Application.ActiveExplorer.CurrentFolder - it should work find in a shared mailbox. Select the folder where you want them created - if at the same level as the inbox, select the shared mailbox name.
malcolm larkin says
Hi there, the code given works brilliantly, i have however hit a barrier - I have multiple inboxes in my outlook "account1" is my default account, then there are 3 others "account2", "account3" and "account4". I would like to run this on account3 but i can not get it to work.
Any help would be greatly appreciated
Hardip Dabhi says
Hi Developer,
I have tried to create multiple folders in the outlook 365 app but unfortunately getting the below error.
Compile error:
User-defined type not defined
Diane Poremsky says
That error usually means you need to set a reference to another object model - Excel in this case - but the code as written shouldn't require that.
Are you adding the code to Outlook's VB Editor or Excel's ?
Hardip Dabhi says
Hi Diane, I am adding this code in the Excel editor.
Diane Poremsky says
As written, its an outlook macro. Switching it to an Excel macro wouldn't be difficult - mostly just the DIM lines need to be changed and outlook declared instead of Excel.
The Excel macro at Create Appointments Using Spreadsheet Data (slipstick.com) is an example.
Hardip Dabhi says
Diane, I run on outlook macro and its works...
Thanks a lot man.
Karin says
Hi there!
This works brilliantly - I used to have the macro, but forgot to back it up when I had to format my PC.
Unfortunately, the link for the complete macro links to the moving the folders macro and not creating the folders macro.
Andrew says
Hi there!
I'm very much of a rookie to all of this.
I've got my spreadsheet with the folders i want to create and copied and pasted the above macro into the Vba thing.
Do i need to change the parts in green to match my spreadsheet?
Or do i need to import my spreadsheet somehow once i've saved the macro?
Sorry for all the basic questions!
Any help kindly appreciated
Ali says
Hi there! I've spent like 1 hour ro figure out how to get away from the:
"Run-time error '-2147221233 (800401f)':
The attempted operation failed. An object could not be found."
But I really cannot understand, I've tried to see somewhere on Google, but nothing useful
I've 3 emails boxes in the Outlook program, I think that's the problem, but can't figure out how to solve it.
Thank you
Samuele says
Is it possible to create a script based on the same excel file to move all emails containing the subfolder name to the created subfolders? Can you make a post about it please?
jkspecial says
Whenever I try to run this, I get a runtime error:
"Run-time error '-2147221233 (800401f)':
The attempted operation failed. An object could not be found."
Diane Poremsky says
Does It stop on a specific line? The error is because an object doesn't exist - this is most likely a folder, but could be any object.
jkspecial says
Hi Diane,
Thanks for the quick response! Wasn't sure how long that would take...
It highlights this line when I attempt to Debug - Set objParentFolder = objParentFolder.Folders(parentname)
Dan says
Hello, I ran into the same problem, the problem is one extra line of code trying to reference the parent folder before it checks if there is a parent folder and subsequently creates one.
The exact line of code is: ((( 'select starting parent )))
--------------------------------------------------------------
iRow = 2
'select starting parent
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
---------------------------------------
delete ((( 'select starting parent )))
Should fix the error.
If it does not make sure your excel list is saved as CSV as well as trust setting to allow the macro.
Michael says
I solved this error by going a parent folder higher than the Inbox. The first parent folder in my Outlook was actually my emaill address name and then the Inbox. When I had Inbox as the first folder I received the above error. Once I changed the first parent folder to my email address, then Inbox and then my new folders, the macro worked perfectly.
Thanks for sharing!
Anton Kjær says
Hi,
We have a script we use to create a folder structure in explorer whenever we get a new client.
There is a numbered folder and a bunch of subfolders - it works well.
We would like for the same folder structure to appear as subfolders to a folder called clients in outlook.
Would it be possible to use the method described on this page to do that?
I am thinking that whenever the folders in explorer are created - they are somehow also added to the excel sheet that this macro is looking at.
Can it be se to run automatically - at intervals?
I did not built the initial script - but it works.
Any help is greatly appreciated.
Thanks in advance.
Diane Poremsky says
>>
We would like for the same folder structure to appear as subfolders to a folder called clients in outlook
>>
Yes, that is do-able.
>>
Can it be set to run automatically - at intervals?
>>
You would need to trigger it using a task or appointment reminder but yes, it can run at intervals.
Stone says
PLease just ignore the below question.
Found out it should be ran in Outlook.
For more advance function, wondering if possible to adding code of creating rules to move emails with the folder name as keyword? Thanks!!
Diane Poremsky says
Well, you can create rules using macros, so yes, if you have the values in a file, you can use a macro to create it. However... if at all possible, you should use a format that matches the address, display name, or a keyword to the folder name. Then you can use one rule and a script to move the mail. Using the address (alias, domain, or both) and a folder named the same is the most accurate, but display name (folder is named the same) also works.
Stone says
Dear Diane,
i have looking for this code for ages! Thanks for your effort!
However when I copy the code into marco, it pops up an error " Compile error: User-defined type not defined" May i please have your kindly help to solve this issue? Thanks!
Diane Poremsky says
That generally means that you need to set a reference to another object model - in this case, Excel - in Tools . References. It's weird though, as the code s written doesnt need references set.
Kristian says
Sorry the attachments are here
Kristian says
Hi again Diane, I am doing this on a separate pst without an Inbox, but I'm really struggling with getting it to go BACK up the tree to the pseudo-Inbox to create a new set of folders (I tried 3 ways?); it seems going back to Inbox works in the examples but the nesting does not seem to work for me. What am I doing wrong here please? Thanks
Kristian says
Hi Diane, fantastic macro. I have a question though, say I have a series of data files, how would I apply this to a series of new folders below a specific Data file i.e. let's say there is no Inbox but only the Data File name (as root)? Thank you!
Kristian says
I ask as I am getting this error: run-time error '-2147221233 (8004010f)' The attempted operation failed. An object could not be found.
Diane Poremsky says
If you want then as folders in the root (at the same level as the Deleted items folder) - select the top of the data file (which is the data file name).
Jason says
Just wanted to thank you for this. Worked perfectly.
Courtney says
Hello! I have succesfully used this VBA however I have made an error in the naming conventions in my excel sheet and so I want to delete all the folders created and then re-upload - is anyone able to assist?
Diane Poremsky says
If you have the bad sheet, I would try using the same code, but deleting the folder instead of adding it.
Set objNewFolder = objParentFolder.Folders(newFolderName)
objNewFolder.delete
otherwise, if you don't have a lot or they are nested subfolders, drag the top level folders into one folder then delete that one folder. It's still time consuming, but avoids the annoying ' are you sure' dialog.
ROBERT SACHS says
This worked perfectly the very first time. The only thing not clear was the macro security setting, which is phrased differently in Outlook 2016. That was easy to figure out that ("low" security being the same as disabled).
Chris Thomson says
Thank You Worked perfect!! I use macros but am a novice. I do not mean to insult. I am very grateful. Even though I read several I missed these things. Maybe it is my ignorance but hope it helps someone. I used mine in OUTLOOK
2016 Pro
My needs were to Create subfolders at multiple levels.
1. How to run Macro. The macro is run in Outlook not Excel, This was just a concept that I missed. I never wrote a macro in Outlook before (my ignorance), So I referenced this. https://msdn.microsoft.com/en-us/vba/outlook-vba/articles/folders-add-method-outlook. I guess this should be obvious.
2.Code snippet to modify for subfolders at multiple levels. It took me a few to understand where to put the code snippet to modify for subfolders at multiple levels. My change can be viewed in the attachment.
3.Creating the excel file. Although, extremely simple once I understood. I have attached an my sheet for reference, would have save an hour of re-reading. The excel workbook name is irrelevant.
Again, Thank you so much. There is very little information on this topic, so I just wanted add where I was confused. Hope this helps someone.
Diane Poremsky says
As an FYI, it could be converted to an Excel macro - you just need to properly reference the object model and objects used in the other app.
This calls Excel:
Dim xlApp As Object 'Excel.Application
Dim xlWkb As Object ' As Workbook
Dim xlSht As Object ' As Worksheet
Dim rng As Object 'Range
Set xlApp = CreateObject("Excel.Application")
if you ran it from excel, you'd do something similar with the outlook object instead.
Dan says
Hello newbe here so... crazy question... can you provide details on how to complete run this as I am technically incline but have no VB knowledge and need a step by step if possible. if I am asking too much just let me know I will continue my research, but I need to create over 1000 folders so any help is appreciated.
Thanks,
Dan
Diane Poremsky says
You need macro security set to low, then open the VBA editor in Outlook - Ctrl+F11. Right click on Project1 then choose Insert > Module. paste the macro into that module.
You'll need to the folder names in a simple CSV. Select the folder where you want to add the folders as subfolders. To add them at the same level as the inbox, select the top of the data file.
Run the macro, select the CSV file when asked and wait a few minutes for it to finish.
Qadeer says
Awesome! thanks, made my day!
SorenC says
Hey Diana,
I found your solution working perfectly regarding the basic setup with a direct structure of folders, but i am having issues with getting the advanced (subfolder macro) to work.
I simply cannot figure out where the snippet has to go into the macro :(
Is it possible for you to explain it, or perhaps post the complete macro inclusive the multiple sublevel folders?. It would really help alot.
Mistyie says
Diana,
Newbie here. Thanks for posting a solution to this problem. I also would appreciate a complete macro showing where the snippet is added for creating subfolders at multiple levels. My attempts at using the current samples shown are not working.
Diane Poremsky says
the full macro code is in this text file
Iliya says
It seemed that after closing excel it is working perfectly. Strange thing though... Thanks a lot for your help!!!
Iliya says
Hello Diane,
I am having a strange issue with the macro, running it on Outlook 2013, Win7 64bit. I have the Macro settings enabled in trusted center, but whenever I run the macro, the pop up window that prompts for an excel/csv file to be opened, appears behind outlook. What should I do to make it appear on top?
Diane Poremsky says
I'm not sure why the dialog is coming up behind, but I know it generally works better if excel is closed, especially if the file you need is open. The code in this macro uses createibject, which opens excel. getobject would use excel that is already open. replace that line with this and see if it works better:
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted
Kiko says
The value in objParentFolder is the one I have on the top 'Testing'. It is the name of the pst file. Under Testing I want to create one Folder and two subfolders.
Thanks
Diane Poremsky says
Ah, to use the top of the data file, you need to use different code as that folder is not an "outlook.folder" object. It should work without error if you select any folder in your mailbox.
Kiko says
Thanks Diane for the code. I have tested and I got the error 'An object could not be found' on the line:
If parentname = "Inbox" Then
Set objParentFolder = Session.GetDefaultFolder(olFolderInbox)
Else
Set objParentFolder = objParentFolder.Folders(parentname) <--------- Error in here
End If
All the code should go like this?:
Option Explicit
Public Sub MoveSelectedMessages()
Dim objParentFolder As Outlook.Folder ' parent
Dim newFolderName 'As String
Dim strFilepath
Dim xlApp As Object 'Excel.Application
Dim xlWkb As Object ' As Workbook
Dim xlSht As Object ' As Worksheet
Dim rng As Object 'Range
Set xlApp = CreateObject("Excel.Application")
strFilepath = xlApp.GetOpenFilename
If strFilepath = False Then
xlApp.Quit
Set xlApp = Nothing
Exit Sub
End If
Set xlWkb = xlApp.Workbooks.Open(strFilepath)
Set xlSht = xlWkb.Worksheets(1)
Dim iRow As Integer
iRow = 2
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
Dim parentname
While xlSht.Cells(iRow, 1) ""
parentname = xlSht.Cells(iRow, 1)
newFolderName = xlSht.Cells(iRow, 1)
If parentname = "Inbox" Then
Set objParentFolder = Session.GetDefaultFolder(olFolderInbox)
Else
Set objParentFolder = objParentFolder.Folders(parentname)
End If
On Error Resume Next
Dim objNewFolder As Outlook.Folder
Set objNewFolder = objParentFolder.Folders(newFolderName)
If objNewFolder Is Nothing Then
Set objNewFolder = objParentFolder.Folders.Add(newFolderName)
End If
iRow = iRow + 1
' make new folder the parent
' Set objParentFolder = objNewFolder
Set objNewFolder = Nothing
Wend
xlWkb.Close
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
Set objParentFolder = Nothing
End Sub
Diane Poremsky says
Forget the earlier question - after looking at the code again, it grabs the currently selected folder name
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
What folder is selected when you run it?
dorisemenov says
The second macro is not the full set of code and appears to be a code snippet. Where does the code snippet fit into the macro 1 example?
Diane Poremsky says
The section that begins and end with the first and last lines of the snippet is replaced by the snippet.
Set objParentFolder = Application.ActiveExplorer.CurrentFolder
' changed code is here
Set objNewFolder = objParentFolder.Folders(newFolderName)
Anish Malhotra says
Hi Diane, Thanks for posting so many great vba codes. You are really helping beginners to move up the ladder pretty quickly.
I am one of those beginners and unfortunately this time I am stuck as I am not able to make this macro run. I pasted the code, listed the folder and sub folder names and it doesn't work.
Macro prompts to open a file and if I select the excel file which has the names of the folder or sub folders, it spits out excel error 438.
Will it be possible for you to attach the working file which we can use or if you could suggest how to make this code run?
Many thanks in advance for your support. Will wait for your revert
Diane Poremsky says
For the first macro, simple list in notepad saved with the extension .csv (the macros also work with an XLSX file type)
Folder Name
Folder1
Folder2
Folder3
Folder4
when you run the macro, it creates Folder1 - Folder4 as subfolders of the folder that is selected/in focus. The second macro uses 2 columns - the first column has existing folder names which will be the parent folders of the names in the second column, which has the folder names you want to create.
Error 438 is object not supported. Are you running the macro in excel or Outlook? This one runs in Outlook.