2 Methods to Create a Contact Group from a List of Contacts in an Excel File

If you would like to create an Outlook contact group from the list of contacts in an Excel file, you can use the 2 methods introduced in this article.

Some users are seeking a way to quickly create an Outlook contact group from an Excel file which contains a list of contacts. Of course, you can create such contact group manually. However, it is a bit troublesome. Hence, apart from the manual means, here we’ll additionally introduce another quick method which uses VBA.

Create a Contact Group from a List of Contacts in an Excel File

Method 1: Create Manually

  1. At the very outset, open the Excel file.
  2. Then select the columns containing the email addresses. Optionally, you can also include the columns for names too.
  3. Next press “Ctrl + C” to all the selected cells.Copy Names and Addresses Manually
  4. After that, start your Outlook program.
  5. Then switch to Contacts pane and hit “New Contact Group” button.
  6. Subsequently, click “Add Members” -> “From Outlook Contacts”
  7. Later click into the “Members ->” field and press “Ctrl + V” keys to paste the previously copied contacts.Paste the Copied Contacts
  8. Hit “OK” button.
  9. Then assign a name for the new contact group.
  10. Lastly click “Save & Close” to save this group.

Method 2: Create via Outlook VBA

  1. In the first place, press “Alt + F11” key buttons in Outlook.
  2. Then open a new module.
  3. Subsequently, copy and paste the following codes into it.
Sub CreateContactGroupfromExcel()
    Dim objContactsFolder As Outlook.Folder
    Dim objContact As Outlook.ContactItem
    Dim objContactGroup As Outlook.DistListItem
    Dim objExcelApp As New Excel.Application
    Dim objExcelWorkbook As Excel.Workbook
    Dim objExcelWorksheet As Excel.Worksheet
    Dim nLastRow As Integer
    Dim nCurrentRow As Integer
    Dim objNameCell As Excel.Range
    Dim objEmailCell As Excel.Range
    Dim strName As String
    Dim strEmail As String
    Dim objTempMail As Outlook.MailItem
    Dim objRecipients As Outlook.Recipients

    Set objContactsFolder = Outlook.Application.Session.GetDefaultFolder(olFolderContacts)
    Set objContactGroup = Outlook.Application.CreateItem(olDistributionListItem)
    'You can change the contact group name
    objContactGroup.DLName = "Group Name"
    Set objExcelApp = CreateObject("Excel.Application")
    'You should change the path to your own Excel file
    Set objExcelWorkbook = objExcelApp.Workbooks.Open("E:\Contacts.xlsx")
    Set objExcelWorksheet = objExcelWorkbook.Sheets(1)
    nLastRow = objExcelWorksheet.UsedRange.Rows.Count
    'The "A2" varies with the first contact's name cell in your own Excel file
    Set objNameCell = objExcelApp.Range("A2")
    While nCurrentRow <= nLastRow
          nCurrentRow = objNameCell.Row
          strName = objNameCell.Value
          If strName = "" Then
             GoTo NextRow
          End If
          Set objEmailCell = objExcelApp.ActiveCell.Offset(0, 1)
          strEmail = objEmailCell.Value
          Set objContact = objContactsFolder.Items.Find("[FullName] = '" & strName & "'")
          'If there is no such a contact, create it.
          If objContact Is Nothing Then
             Set objContact = Outlook.Application.CreateItem(olContactItem)
             With objContact
                  .FullName = strName
                  .Email1Address = strEmail
             End With
          End If
          'Add the contacts to the new contact group
          Set objTempMail = Application.CreateItem(olMailItem)
          objTempMail.Recipients.Add (strName)
          Set objRecipients = objTempMail.Recipients
          objContactGroup.AddMembers objRecipients

          Set objNameCell = objExcelApp.ActiveCell.Offset(1, 0)
    'Use "objContactGroup.Save" to straightly save it
    objTempMail.Close olDiscard
End Sub

VBA Codes - Create a Contact Group from a List of Contacts in an Excel File

  1. After that, hit the “Run” icon in the toolbar.
  2. At once, a new contact group will display, like the following screenshot:New Contact Group

Handle Vexing Outlook Troubles Skillfully

If you frequently encounter various errors in Outlook, it is highly suggested to keep a preeminent and reliable Outlook recovery tool in vicinity, like DataNumen Outlook Repair. In this case, you can use it to deal with the annoying issues via it in time.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover sql and outlook repair software products. For more information visit www.datanumen.com

2 responses to “2 Methods to Create a Contact Group from a List of Contacts in an Excel File”

Leave a Reply

Your email address will not be published. Required fields are marked *