What it does
Uses a settings sheet (Bulk_Edit_Contacts) to add, delete, update, or export a list of contacts in bulk, integrating Excel and Outlook.

When to use it
When you need to add multiple contacts in Outlook or backup a list of them to copy and paste on another computer, this is an easy and controlled way to do it.  About to leave a job?  Make sure to export a list of contacts you want to keep up with!  Exporting a list of contacts can also help with tasks such as sending out Christmas cards.  You can also share your exported contact list with family, friends, or colleagues.

Why to use it
It’s an extremely quick way to import or export contacts between Outlook and Excel to share, back up, or copy/paste to another computer.

Default shortcut
None

Other Details

• Category: MS Office / Outlook
• Difficulty: 4/5
• Usage/frequency: 1/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 8/15/2019
• Tags: Outlook, bulk, contacts

Related Macros
None

Other Articles
None

Bulk Edit Contacts Example File

Prerequisites
Ensure you have an email/contacts account set up in Microsoft Outlook.

Instructions
Run the Bulk Edit Contacts macro.  It will prompt you to either export a list of contacts (see below) or create new ones.  If creating new contacts, it will create a sheet called Bulk_Edit_Contacts.  This is where you’ll configure all the contacts to create.  These are the columns within the Bulk_Edit_Contacts sheet you’ll want to fill in:

• Column A – First Name (optional but at least one of either first name, last name, or company name is required): enter the contact’s first name.
• Column B – Last Name (optional but at least one of either first name, last name, or company name is required): enter the contact’s last name.
• Column C – Company Name (optional but at least one of either first name, last name, or company name is required): enter the contact’s company name.
• Column D – Title (optional): enter the contact’s job title.
• Column E – Email Address (recommended): enter the contact’s email address.
• Column F – Mobile Number (recommended): enter the contact’s mobile phone number.
• Column G – Home Number (optional): enter contact’s home phone number.
• Column H – Work Number (optional): enter the contact’s work phone number.
• Column I – Home Address (optional): enter the contact’s full home address.
• Column J – Work Address (optional): enter contact’s full work address.
• Column K – Categories (optional): enter one or more Outlook categories to assign to the contact.  Separate multiple categories with a comma.  To see a list of available categories, view them in Outlook or use the Bulk Edit Categories macro.
• Column L – Notes (optional): enter any notes to add to the contact as cell comments.  Note that cell comments are used to keep the sheet from becoming cluttered and to overcome cell character limits.
• Column N – Add/Delete (required): enter [NEW] to import new contacts.  Enter [DELETE] to delete existing contacts.  Enter [UPDATE] to update existing contacts.  Note that to update or delete, the contact ID must be in column M.  This is usually retrieved by first exporting contacts and filtering to the contacts you want to update or delete.

Once you fill in the contact details, run the Bulk Edit Contacts macro again and confirm at the prompt to process.  If the data is valid, the contacts will be imported to Outlook and the ID for each contact will be listed.

To update or delete bulk contacts, first export a list of contacts.  Run the Bulk Edit Contacts macro and click the Export button.  Any existing contacts will be listed in a new sheet called Bulk_Edit_Contacts.  If that sheet already exists, you will be prompted whether to process – add/update/delete – the contents.  To delete contacts, enter [DELETE] in column N.  To update contacts, enter [UPDATE] in column N.  Filter to and delete rows for any contacts you don’t want to update or delete.  When ready, run the Bulk Edit Contacts macro again to process any contacts to update or delete.