By default, you can only see the member names and email addresses in a contact group. If you wish to get the members’ other details, you can use the way introduced in this article.
When you double click a contact group to open it, in its window, you can only see the group members’ names and email addresses. You are unable to get members’ other details, like company, job title, phone number or business address, etc. In general, to get these details, you have to find the contacts corresponding to the members in your Contacts folder manually. It’s too troublesome. Here we will show you a piece of VBA code, which can quickly export these details to an Excel workbook.
Export the Details of All Members in a Contact Group to Excel
- To start with, launch your Outlook application.
- Then you should press “Alt + F11” key shortcuts in the main Outlook window.
- Next in the new “Microsoft Visual Basic for Applications” window, you need to open a not-in-use module or directly insert a new module.
- Subsequently, copy and paste the following VBA code into this module.
Sub ExportMemberContactDetailsToExcel() Dim objContactGroup As Outlook.DistListItem Dim objGroupMember As Outlook.recipient Dim objContacts As Outlook.Items Dim objFoundContact As Outlook.ContactItem Dim objExcelApp As Excel.Application Dim objExcelWorkBook As Excel.Workbook Dim objExcelWorkSheet As Excel.Worksheet Dim i As Integer Dim nRow As Integer Dim strFilename As String Set objContactGroup = Application.ActiveExplorer.Selection(1) 'Create a new Excel workbook Set objExcelApp = CreateObject("Excel.Application") Set objExcelWorkBook = objExcelApp.Workbooks.Add Set objExcelWorkSheet = objExcelWorkBook.Worksheets(1) 'Set the seven column headers With objExcelWorkSheet .Cells(1, 1) = "Name" .Cells(1, 2) = "Email Address" .Cells(1, 3) = "Company" .Cells(1, 4) = "Job Title" .Cells(1, 5) = "Phone Number" .Cells(1, 6) = "Mailing Address" .Cells(1, 7) = "Birthday" End With Set objContacts = Application.Session.GetDefaultFolder(olFolderContacts).Items.Restrict("[Email1Address]>''") nRow = 2 For i = 1 To objContactGroup.MemberCount Set objGroupMember = objContactGroup.GetMember(i) strFilter = "[Email1Address] = '" & objGroupMember.Address & "'" Set objFoundContact = objContacts.Find(strFilter) If Not (objFoundContact Is Nothing) Then 'Input the Contact Details With objExcelWorkSheet .Cells(nRow, 1) = objFoundContact.FullName .Cells(nRow, 2) = objFoundContact.Email1Address .Cells(nRow, 3) = objFoundContact.CompanyName .Cells(nRow, 4) = objFoundContact.JobTitle .Cells(nRow, 5) = objFoundContact.BusinessTelephoneNumber .Cells(nRow, 6) = objFoundContact.MailingAddress If objFoundContact.Birthday = #1/1/4501# Then .Cells(nRow, 7) = "" Else .Cells(nRow, 7) = objFoundContact.Birthday End If End With Else With objExcelWorkSheet .Cells(nRow, 1) = objGroupMember.Name .Cells(nRow, 2) = objGroupMember.Address End With End If nRow = nRow + 1 Next 'Fit the columns objExcelWorkSheet.Columns("A:G").AutoFit 'Change the Path as per where you want to save the new Excel file strFilename = "E:\" & objContactGroup.DLName & " Member Contact Details.xlsx" 'Save the Excel workbook objExcelWorkBook.Close True, strFilename MsgBox ("Complete!") End Sub
- After that, for convenient check, you can add the new VBA project to Quick Access Toolbar or ribbon.
- Later set your Outlook macro security level to low.
- Ultimately, you can have a try.
- First, select a contact group.
- Then press the macro button in Quick Access Toolbar or ribbon.
- Immediately, the macro will run.
- When it finishes, you will get a prompt of “Complete!”
- After that, you can go to the predefined destination local folder, in which you can find a new Excel file.
- Open it, you’ll see the group members’’ contact details like the following screenshot:
Don’t Panic in Face of Outlook Issues
No one is willing to accept any Outlook troubles, no matter popup error message or corrupt Outlook data file. Therefore, users will tend to get panic when coming across Outlook issues. If you want to keep calm in that case, you should have two necessities in hand. One is current Outlook data backups. The other one is potent Outlook fix software, like DataNumen Outlook Repair.
Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and outlook repair software products. For more information visit www.datanumen.com