WWindoIn this article, we will discuss how to create Outlook folders from an Excel list. We use Outlook primarily for managing email messages, scheduling appointments, organizing tasks, and storing contact information. We can create customized folders for our contacts in Outlook. In order to synchronize our Excel dataset with Outlook, we can create folders having the same names as an Excel list. Then, we can store information about that contact from the Excel list in Outlook. Here, we will use a VBA code to create Outlook folders from an Excel list.
Download Practice Workbook
You can download the practice book here.
How to Launch VBA Macro Editor in Excel
In order to run any VBA code, we first need to write or edit the code in the VBA Macro Editor.
Before that, we need to enable the Developer tab, which remains hidden by default. The Developer tab allows users to access the VBA Macro Editor. Do the following tasks.
- Go to Developer Tab >> Visual Basic.
- This will open the Visual Basic window.
- Select Insert >> Module in the macro editor.
- As a result, an empty module will appear on the screen where you can write the code. However, use the Run icon or F5 key to run the code.
Create Outlook Folders from Excel List with 2 Easy Steps
In this article, we will use a VBA code to create Outlook folders from an Excel list. Here, we have a dataset of some employees containing their names, IDs, and departments. We will run a VBA code that will create folders inside the Outlook application with the folders’ names being the names of the employees.
Step 1: Creating Excel List
In order to create folders in Outlook, first we need to select the names of the employees for whom we want to generate the folders. Here, we will select the names in the B5:B12 range from the given dataset.
Read More: How to Make a List within a Cell in Excel (3 Quick Methods)
Step 2: Executing VBA Code to Create Outlook Folders
After that, we will run the following VBA code to create the folders in Outlook.
Sub OutlookFolders() 'declaring variables and constants Const olFolderInbox As Long = 6 Dim OutlookApp As Object Dim Arr(), xValue Dim IsCreated As Boolean Dim ws As Worksheet 'setting the value for ws variable Set ws = Worksheets("OutlookFolders") 'adding the values from the to the Arr array With ws If .FilterMode Then .ShowAllData Set Rng = Selection Arr = Rng.Value If Not IsArray(Arr) Then xValue = Arr ReDim Arr(1 To 1) Arr(1) = xValue End If End With 'opening Outlook object On Error Resume Next Set OutlookApp = GetObject(, "Outlook.Application") If Err Then Set OutlookApp = CreateObject("Outlook.Application") IsCreated = True End If 'adding values from the list as the folder of Outlook application With OutlookApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) For Each xValue In Arr .Folders.Add xValue Next xValue End With 'exting Outlook If IsCreated Then OutlookApp.Quit Set OutlookApp = Nothing End Sub
VBA Code Breakdown
- First, we will declare the variables and constants required for this code. Here, in the “Const olFolderInbox As Long = 6” line, we declare a constant called “olFolderInbox” and assign its value to 6. Because we will add the folders in the Inbox section of Outlook and 6 is the identifier number for the Inbox section in Outlook. We also declare” Arr()”, an Array variable, and “xValue”, a Variant variable.
- We will write the next section of the VBA code snippet within a With statement. The purpose of this code is to extract the values from a selected range of cells and store them in an array variable “Arr”.
- The first line checks if the FilterMode property of the worksheet is turned on. If it is, the code calls the .ShowAllData method to show all the data.
- The Selection object refers to the currently selected range of cells on the worksheet. The code assigns this range to the “Rng” object variable using the Set keyword.
- Then, we initialize the “Arr” variable is as an array using the values of the Rng object. The Value property of the “Rng” object returns a 2D array of values from the selected range.
- The “If Not IsArray(Arr)” condition checks if “Arr” is not already an array. If “Arr” is not an array, the code assigns the single value stored in “Arr” to a new variable “xValue”. Then, Arr is re-initialized as an array with one element using the ReDim statement. The single value stored in “xValue” is assigned to the first element of the new “Arr” array.
- The next block of code selects the Outlook object with the GetObject method and if the application is not opened it opens the application with the CreateObject method.
- The first line of the code initializes the With block with the expression “OutlookApp.GetNamespace(“MAPI”).GetDefaultFolder(olFolderInbox)”. This expression returns a reference to the default inbox folder of the currently logged-in Outlook account, using the GetNamespace method and the GetDefaultFolder method.
- Then, the For Each loop adds each element from the “Arr” object as a folder under the Inbox section of the Outlook application.
- Finally, we exit the Outlook application.
After running the code, the Inbox section of Outlook will contain the newly created folders from the Excel list.
Read More: How to Create Multiple Folders at Once from Excel
How to Create Windows Folders from Excel List
In this instance, we will create folders in our Windows systems that will have names the same as the names in the “Employee Name” column in the Excel Workbook using a VBA code. The folders will be created in the same directory as the Excel file containing the VBA code.
The code below will create the folders in the Windows system.
Sub WindowsFolders() 'declaring variables Dim Rng As Range Dim nRows, nCols, xr, xc As Integer Dim ws As Worksheet 'setting values to ws and Rng variables Set ws = Worksheets("OutlookFolders") Set Rng = ws.Range("B5", ws.Cells(ws.Rows.Count, "B").End(xlUp)) nRows = Rng.Rows.Count nCols = Rng.Columns.Count 'running for loop to create folders from the Rng values For xc = 1 To nCols xr = 1 'running do while loop to make directories for the folders Do While xr <= nRows If Len(Dir(ActiveWorkbook.Path & "\" & Rng(xr, xc), vbDirectory)) = 0 Then MkDir (ActiveWorkbook.Path & "\" & Rng(xr, xc)) On Error Resume Next End If xr = xr + 1 Loop Next xc End Sub
VBA Code Breakdown
- Here, first, we declare the variables with their types. Then, “Set Rng = ws.Range(“B5”, ws.Cells(ws.Rows.Count, “B”).End(xlUp))” line assigns the values from the B5 cell to the last non-empty cell as the value for the “Rng” variable.
- We will use the next VBA code block to create directories for folders based on the values stored in a range of cells (Rng). The code runs a loop through the columns and rows of the range and uses the MkDir function to create directories for the folder names found in each cell.
- The For loop starts with the variable xc and runs from 1 to nCols (the number of columns in the range).
- Inside the loop, the code initializes variable xr to 1. This variable is used to keep track of the current row being processed
- The Do While loop starts and runs as long as the value of xr is less than or equal to nRows (the number of rows in the range).
- Inside the Do While loop, the If statement checks if a directory with the name of the folder already exists in the active workbook’s path. This is done using the Dir function, which returns the file name or folder name that matches a specified pattern.
- If the Dir function returns a zero-length string, indicating that the folder does not exist, then the MkDir function is called to create the folder with the name specified by the Rng(xr,xc) cell.
As the image below suggests, Windows has created the folders having the names from the Excel list in the same directory as the Excel file itself.
Read More: How to Create Folders from Excel List (3 Easy Methods)
Frequently Asked Questions
1. Can I specify the location where I want to create the Outlook folders using an Excel list?
Answer: Yes, you can do that. You can also specify the parent folder name and the subfolder name to create subfolders within the specified location.
2. What version of Outlook do I need to use to create folders from an Excel list?
Answer: You can use any version of Outlook, as long as it supports VBA macros. However, the specific syntax and commands used in the macro may vary slightly depending on the version of Outlook you are using.
3. Can I create Outlook folders using an Excel list without using VBA macros?
Answer: You can use a third-party add-in or a PowerShell script to create the folders. But, VBA macros are the most commonly used method.
4. Can I modify the macro to add other properties to the Outlook folders, such as color coding or reminders?
Answer: Yes, you can modify the macro to add other properties to the Outlook folders, such as color coding or reminders. You will need to use the appropriate Outlook object model commands to add these properties to the folders.
In this article, we have talked about how to create Outlook folders from an Excel list. This will allow users to automatically generate folders with names from the list just by running a VBA code. After reading this article, you can synchronize Excel dataset with Outlook by creating folders from Outlook from the Excel dataset. As a result, they can perform tasks related to Excel and Outlook more efficiently. If you want to see more Excel content like this, please visit our website, Exceldemy. Also, unlock a great resource for Excel-related content.
- How to Make Alphabetical List in Excel (3 Ways)
- Make a To Do List in Excel (3 Easy Methods)
- Creating a Mailing List in Excel (2 Methods)
- How to Make a Numbered List in Excel (8 Methods)
- Make a Price List in Excel (Step by Step Guidelines)
- How to Generate List Based on Criteria in Excel (5 Methods)
- Create a Unique List in Excel Based on Criteria (9 Methods)