VBA Outlook Create Contact Group from Excel

Yes. In classic Outlook for Windows, an Excel VBA macro can read email addresses from a worksheet, create an Outlook contact group (also called a distribution list), add the addresses as members, and save the group in the profile’s default Contacts folder. The method uses Outlook’s desktop object model, so it does not work with the new Outlook for Windows, Outlook on the web, or Excel for the web.

What the macro creates—and what it does not

An Outlook contact group is a saved personal contact item that lets you address an email to several people through one group name. It is useful for a recurring project team, club, or mailing list when you manage the membership in your own Outlook contacts. It is not the same thing as a Microsoft 365 Group, a shared mailbox, or an organization-wide distribution group managed by an administrator. The macro below creates a personal contact group; it does not provision a shared service or grant other people access to the list.

Excel supplies the member addresses, while Outlook creates and saves the group. The example assumes a worksheet named Contacts, a display-name or notes column in A, email addresses in B beginning at row 2, and the desired group name in D1. The code uses the email column; it does not use the text in column A to set member display names. Outlook may show a resolved name from its address book after it recognizes an address.

Prepare the Excel list

Clean the source before running automation. Put one email address per row, use a clear heading in row 1, and remove addresses that should not be included. Blank cells in column B are skipped. The macro checks that a nonblank entry contains an at sign, but that simple check is not a full email-address validation. A typo such as an incorrect domain can still pass that check.

Use D1 for a meaningful group name, such as Quarterly Review Team.
Keep the email address in column B, starting at B2. A header in B1 is fine.
Remove duplicate addresses yourself. The sample does not deduplicate, so a repeated row can create a repeated member.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA in the file.

If your data is on another sheet or in another column, change the worksheet name and the references in the code to match. Do not include a header row among the addresses. If the workbook has several sheets named similarly, confirm that the macro points to the intended one before running it.

VBA macro to build the contact group

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code below. It uses late binding, so you do not need to add an Outlook reference in Tools > References. The number 7 is Outlook’s item type value for a distribution-list item.

Option Explicit

Public Sub CreateContactGroupFromExcel()
    Dim ws As Worksheet
    Dim olApp As Object
    Dim distList As Object
    Dim members As Object
    Dim groupName As String
    Dim email As String
    Dim lastRow As Long
    Dim r As Long
    Dim added As Long

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("Contacts")
    groupName = Trim$(CStr(ws.Range("D1").Value))
    If Len(groupName) = 0 Then
        MsgBox "Enter the group name in D1.", vbExclamation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "No email addresses were found in column B.", vbExclamation
        Exit Sub
    End If

    Set olApp = CreateObject("Outlook.Application")
    Set distList = olApp.CreateItem(7)
    distList.DLName = groupName
    Set members = distList.Members

    For r = 2 To lastRow
        email = Trim$(CStr(ws.Cells(r, "B").Value))
        If Len(email) > 0 Then
            If InStr(1, email, "@", vbTextCompare) = 0 Then
                MsgBox "Check the email address in B" & r & ".", vbExclamation
                Exit Sub
            End If
            members.Add email
            added = added + 1
        End If
    Next r

    If added = 0 Then
        MsgBox "No nonblank email addresses were found.", vbExclamation
        Exit Sub
    End If

    If Not members.ResolveAll Then
        MsgBox "Outlook could not resolve every member. Check the addresses and your address books, then try again.", vbExclamation
        Exit Sub
    End If

    distList.Save
    MsgBox "Created '" & groupName & "' with " & added & " member(s).", vbInformation
    Exit Sub

ErrHandler:
    MsgBox "Outlook could not create the group: " & Err.Description, vbCritical
End Sub

The tags in the displayed code encode the less-than and ampersand characters for HTML. When you copy it from a rendered page, confirm those operators appear as VBA operators, not as visible HTML entities. The checks stop the macro when the group name or list is missing, and they stop at the first address that lacks an at sign. Outlook’s ResolveAll call asks it to resolve each member against the available address books before saving. If any member cannot be resolved, the example leaves the new group unsaved so you can correct the source.

Run it and confirm the result

Before you run any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. Make sure classic Outlook is installed and configured with the profile and Contacts folder where you want the group. In Excel, return to the workbook, press Alt+F8, select CreateContactGroupFromExcel, and choose Run. You can also run it from the Visual Basic Editor with the cursor inside the procedure by pressing F5.

After the success message, check Outlook’s Contacts or People area in the same profile. The item is created using Outlook’s default location for a new distribution list; this code does not ask you to choose a different Contacts folder. Open the group and inspect its members before using it. If you rerun the macro, it creates another group rather than updating or replacing the previous one. Give each run a distinct name or remove the earlier test item manually if appropriate.

Why member resolution matters

Adding a string to the group and resolving it are separate steps. An address may be mistyped, unavailable to the active Outlook profile, or not recognized by the address book. A failed resolution is a useful warning, not proof that the person’s mailbox is invalid. Check spelling, the selected Outlook profile, and whether the address is available to Outlook. For external addresses, use the complete SMTP address and verify the final member list in Outlook.

The macro deliberately treats unresolved members as a reason to stop instead of silently saving a group that may not behave as expected. In some organizations, address-book policies or account configuration affect what can be resolved. Avoid weakening that check just to make the success message appear: first determine which addresses Outlook cannot resolve, and confirm the intended outcome with your organization’s support team if necessary.

Common problems and practical fixes

“ActiveX component can’t create object” or a similar automation error: The macro cannot start classic Outlook through its COM automation interface. Confirm that classic Outlook is installed and configured. New Outlook does not provide the COM interface this code requires.
The worksheet or range is not found: Change Contacts, D1, or column B in the macro to match the actual workbook. Worksheet names must match, including spaces.
The macro stops at ResolveAll: Inspect the address in the relevant row and confirm Outlook is using the right profile and address books. A valid-looking string is not necessarily resolvable in that profile.
A group appears more than once: Each run makes a new item. The sample does not search for an existing group, append members, or replace a previous list.
A security warning appears: Follow your organization’s policy. Outlook’s programmatic-access protections can depend on security configuration and administrator settings; do not disable protections or use an untrusted workaround just to suppress a warning.

For easier troubleshooting, test first with two or three addresses that you control or can verify. Once the group appears and its membership is correct, run the process on the full list. That small test can distinguish a source-data issue from a problem with the Outlook profile or automation setup.

Compatibility, permissions, and safe use

This example targets Excel for Microsoft 365 on Windows and classic Outlook for Windows. Excel for the web cannot run VBA macros. The new Outlook for Windows does not support this Outlook COM automation approach, even if Excel itself is the Windows desktop version. Outlook for Mac does not expose the same Windows COM automation model, so CreateObject("Outlook.Application") is not a cross-platform solution. A Mac user should use Outlook’s manual contact features or an approved alternative suited to that environment.

Macros can create or change Outlook data, so only run code you understand and trust. Keep the workbook in a trusted, controlled location and follow your organization’s macro policy. This sample saves a group in the configured Outlook profile; it does not send an email. Creating the list also does not notify its members or automatically share the list with colleagues. If the addresses are personal or sensitive, handle the workbook and resulting contact data according to applicable privacy and retention rules.

When a different approach is better

For a one-time list, creating the group manually in Outlook may be simpler and lets you inspect each member as you add it. For a recurring import, VBA is convenient when the source remains a clean worksheet and the target is classic Outlook on Windows. If colleagues need a centrally managed list, ask an administrator about an organization distribution group or Microsoft 365 Group instead; those have different ownership, membership, and sharing behavior.

Another option is to export or transform data for a supported contact-import workflow, but importing contacts and creating a contact group are different tasks. An imported set of individual contacts does not automatically become one group. Choose the approach based on whether you need a personal reusable list, individual contact records, or a shared, centrally administered collaboration resource.

Frequently Asked Questions

Can Excel create an Outlook contact group without VBA?
Yes, but not by using a worksheet formula alone. You can create the group directly in classic Outlook, or use another supported import or automation process. For an automated Excel-to-Outlook group on Windows, VBA is one option when classic Outlook is available.

Does this macro work with the new Outlook?
No. The code depends on classic Outlook’s Windows COM object model. New Outlook, Outlook on the web, and Outlook for Mac do not provide that same automation interface.

Will running the macro update an existing group?
No. Each run creates a new contact-group item. The sample neither finds an existing group by name nor adds members to one, so check Outlook before rerunning it to avoid duplicate groups.

Can I use names as well as email addresses?
The example reads email addresses from column B. Column A can help you identify each row, but the macro does not assign that text as the member’s display name. Outlook may show a name when it resolves the email through an address book.

Where does Outlook save the new group?
The code saves the item in the default Contacts location used by the active Outlook profile for a new distribution list. It does not select a folder or account explicitly, so check the profile and Contacts area if you have multiple accounts or data stores.

Can Excel for Mac run this exact code?
No. The code’s Outlook automation uses Windows COM, which is not the same on Mac. Use Outlook’s available manual features or a platform-appropriate, organization-approved alternative instead.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.