Create Outlook Folders from Excel List (Step-by-Step Guide)

Get FREE Advanced Excel Exercises with Solutions!

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.

Opening Visual Basic Window

  • Select Insert >> Module in the macro editor.

Inserting VBA Module

  • 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.

VBA Module


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.

Dataset


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.

Selecting names from Excel list

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 to create Outlook folders from Excel list

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.

Creating Outlook folders from 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.

Excel list of names to create Windows folders from

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

Code to create Windows folders

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.

Creating Windows folders from Excel list

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.


Conclusion

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.


Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo