How to import contacts and appointments from Excel into Outlook and vice-versa

Importing contacts and appointments into Microsoft Outlook is a feature that has existed in Outlook for a while. In Outlook 2013 you can find the Import/Export function in the File or Backstage view menu, under Open & Export.

The standard Outlook import/export functionality takes you through a series of forms and even allows you specify field mappings.

Now, the standard functionality is fine, but let’s try and make it even easier for our customers to import and export data either from Excel to Outlook or from Outlook to Excel by writing our own shared Excel and Outlook add-in.

• Creating the Outlook and Excel add-in project
• Exporting Excel data to Outlook
• Importing Outlook data to Excel

Creating the Outlook and Excel add-in project

Start by creating a new COM Add-in project for Outlook and Excel in Visual Studio.

Next, select your programming language of choice (C#, VB.NET or C++.NET) and the minimum version of Office that will be supported by your add-in and finally select both Microsoft Excel and Microsoft Outlook as the supported applications.

Exporting Excel data to Outlook

We’ll add an advanced Excel task pane, which will have our main UI for the add-in. Start by adding an ADXExcelTaskPaneManager component to the AddinModule designer surface.

Next, add an advanced Excel Task Pane to the COM add-in project:

We’ll design the new custom task pane as we would any standard Windows form. The following task pane consists of two radio buttons: (i) a DataGrid view control and a button; as well as (ii) a panel and a number of label controls.

I’ve also added a DataSet to the form, which contains one DataTable with three DataColumns.

We’ll use the DataTable to store the column mappings between the data in the Excel sheet and Outlook’s Contact or Appointment items. Next, add event handlers for both radio buttons’ CheckedChanged events and add the following code to it:

private void rbContactData_CheckedChanged(object sender, EventArgs e)
{
    dtMapping.Clear();
    LoadProperties("contact");
    LoadHeaders();
}
 
private void rbCalendarData_CheckedChanged(object sender, EventArgs e)
{
    dtMapping.Clear();
    LoadProperties("calendar");
    LoadHeaders();
}

The above code, clears the DataTable, adds the Outlook Contact or Appointment items’ properties to the dropdown list of available fields and finally, it populates the headings from the first row of the Excel sheet. The code listing for the LoadProperties and LoadHeaders methods looks as follows:

private void LoadHeaders()
{
    Excel.Application excelApp = null;
    Excel.Worksheet activeSheet = null;
    Excel.Range usedRange = null;
    Excel.Range headerRange = null;
    Excel.Range cellRange = null;
    DataTable dtMappings = null;
 
    try
    {
        excelApp = this.ExcelAppObj as Excel.Application;
        activeSheet = excelApp.ActiveSheet as Excel.Worksheet;
        usedRange = activeSheet.UsedRange;
        headerRange = usedRange.Rows[1] as Excel.Range;
 
        dtMappings = dsMappings.Tables[0];
 
        for (int i = 1; i <= headerRange.Cells.Count; i++)
        {
            DataRow headerRow = dtMappings.NewRow();
            Excel.Range cellRange = ((Excel.Range)headerRange.Cells[1, i]);
            headerRow["Field"] = cellRange.Value.ToString();
            headerRow["MapTo"] = "(Ignore)";
            headerRow["ColNo"] = i;
            dtMapping.Rows.Add(headerRow);
            if (cellRange != null) Marshal.ReleaseComObject(cellRange);
        }
        dgMapping.DataSource = dsMappings.Tables[0];
    }
    finally
    {
        if (headerRange != null) Marshal.ReleaseComObject(headerRange);
        if (usedRange != null) Marshal.ReleaseComObject(usedRange);
        if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
    }
}
 
private void LoadProperties(string type)
{
    string[] contactProperties = new string[] {
        "(Ignore)", "FirstName", "LastName", "Initials", "JobTitle", "CompanyName", 
        "BusinessAddressStreet", "BusinessAddressCity", "BusinessAddressState", 
        "BusinessAddressPostalCode", "BusinessAddressCountry", "Email1Address", 
        "Email2Address", "Email3Address", "BusinessTelephoneNumber", "BusinessFaxNumber", 
        "HomeTelephoneNumber", "HomeFaxNumber" };
    string[] appointmentProperties = new string[] { 
        "(Ignore)", "Start", "End", "Subject", "Body" };
 
    MapTo.Items.Clear();
    if (type == "contact")
    {
        MapTo.Items.AddRange(contactProperties);
    }
    else
    {
        MapTo.Items.AddRange(appointmentProperties);
    }
}

Lastly, double-click on the “Export To Outlook” button to generate an event handler for its Click event and add the following code:

private void btnExport_Click(object sender, EventArgs e)
{
    DataTable dtMappings = null;
    Outlook.Application outlookApp = null;
    Outlook.NameSpace session = null;
    Outlook.Folder targetFolder = null;
    Outlook.Items folderItems = null;
 
    Excel.Application excelApp = null;
    Excel.Worksheet activeSheet = null;
    Excel.Range usedRange = null;
 
    string value = string.Empty;
 
    try
    {
        outlookApp = new Outlook.Application();
        session = outlookApp.GetNamespace("MAPI");
        targetFolder = session.PickFolder() as Outlook.Folder;
        excelApp = this.ExcelAppObj as Excel.Application;
        activeSheet = excelApp.ActiveSheet as Excel.Worksheet;
        usedRange = activeSheet.UsedRange;
        if (targetFolder != null)
        {
            folderItems = targetFolder.Items;
            dtMappings = dsMappings.Tables[0];
            if (rbContactData.Checked)
            {
                for (int r = 2; r <= usedRange.Rows.Count; r++)
                {
                    Outlook._ContactItem contact =
                        folderItems.Add(Outlook.OlItemType.olContactItem) as Outlook._ContactItem;
                    Excel.Range rowRange = usedRange.Rows[r] as Excel.Range;
 
                    for (int c = 1; c <= rowRange.Columns.Count; c++)
                    {
                        Excel.Range cell = rowRange.Columns[c] as Excel.Range;
                        if (cell.Value != null)
                            value = cell.Value.ToString();
 
                        DataRow[] mappings = dtMappings.Select("ColNo=" + c);
                        string mapTo = mappings[0]["MapTo"].ToString();
 
                        if (mapTo != "(Ignore)")
                            contact.GetType().InvokeMember(mapTo,
                                BindingFlags.Public | BindingFlags.SetProperty,
                                Type.DefaultBinder, contact, new[] { value });
                    }
                    contact.Save();
                    Marshal.ReleaseComObject(contact);
                    if (rowRange != null) Marshal.ReleaseComObject(rowRange);
                }
                MessageBox.Show("Done");
            }
            else if (rbCalendarData.Checked)
            {
                for (int r = 2; r <= usedRange.Rows.Count; r++)
                {
                    Outlook._AppointmentItem appointment =
                        folderItems.Add(Outlook.OlItemType.olAppointmentItem) as Outlook._AppointmentItem;
                    Excel.Range rowRange = usedRange.Rows[r] as Excel.Range;
 
                    for (int c = 1; c <= rowRange.Columns.Count; c++)
                    {
                        Excel.Range cell = rowRange.Columns[c] as Excel.Range;
                        if (cell.Value != null)
                            value = cell.Value.ToString();
 
                        DataRow[] mappings = dtMappings.Select("ColNo=" + c);
                        string mapTo = mappings[0]["MapTo"].ToString();
 
                        if (mapTo != "(Ignore)")
                            appointment.GetType().InvokeMember(mapTo,
                                BindingFlags.Public | BindingFlags.SetProperty,
                                Type.DefaultBinder, appointment, new[] { value });
                    }
                    appointment.Save();
                    Marshal.ReleaseComObject(appointment);
                    if (rowRange != null) Marshal.ReleaseComObject(rowRange);
                }
                MessageBox.Show("Done");
            }
        }
    }
    finally
    {
        if (usedRange != null) Marshal.ReleaseComObject(usedRange);
        if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
        if (folderItems != null) Marshal.ReleaseComObject(folderItems);
        if (targetFolder != null) Marshal.ReleaseComObject(targetFolder);
        if (session != null) Marshal.ReleaseComObject(session);
        if (outlookApp != null) Marshal.ReleaseComObject(outlookApp);
    }
}

The above code, shows the built-in Outlook Folder Selection dialog by calling the PickFolder method of the Outlook.NameSpace object. It then checks whether we’re exporting Contact or Appointment data, and then creates either a new Outlook.ContactItem or Outlook.AppointmentItem object and loops through the rows in the sheet. It uses the mappings stored in the DataTable to know which column values to set to which Outlook object properties.

The Outlook ContactItem’s properties are set using reflection:

contact.GetType().InvokeMember(mapTo, BindingFlags.Public | BindingFlags.SetProperty, Type.DefaultBinder, contact, new[] { value });

When running the add-in in Excel it should look like the following image:

Importing Outlook data to Excel

On the reverse side we would like to export Outlook data to Excel. Our UI in Outlook will be far simpler than our Excel UI as we’ll only add an item to the Contact context-menu in the Outlook Explorer. Start by adding new ADXRibbonContextMenu item to the AddinModule design surface.

Select the newly added ADXRibbonContextMenu control and set its ContextMenuNames property to:

• Outlook.Explorer.ContextMenuContactItem; and
• Outlook.Explorer.ContextMenuContactsMoreActions.

This will cause the menu item to be displayed on the context-menu of an Outlook contact item as well as in the “More Actions” list in the Communicate Ribbon group.

Next, we need to add a single button to the context menu control as indicated below:

Double-click the newly added context-menu button to generate an event handler for its OnClick event and add the following code:

private void adxExportContactToExcelRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed)
{
    Outlook.Explorer currExplorer = null;
    Outlook.Selection currSelection = null;
 
    Excel.Application excelApp = null;
    Excel.Workbooks workBooks = null;
    Excel.Workbook workBook = null;
    Excel.Sheets workSheets = null;
    Excel.Worksheet workSheet = null;
    Excel.Range insertRange = null;
    Excel.Range worksheetCells = null;
 
    try
    {
        currExplorer = OutlookApp.ActiveExplorer();
        currSelection = currExplorer.Selection;
 
        diagSaveFile.ShowDialog();
        if (!String.IsNullOrEmpty(diagSaveFile.FileName))
        {
            excelApp = new Excel.Application();
            workBooks = excelApp.Workbooks;
            workBook = workBooks.Add();
            workSheets = workBook.Worksheets;
            workSheet = workSheets[1] as Excel.Worksheet;
            workSheet.Name = "Outlook Contacts";
 
            String[,] dataArray = new string[currSelection.Count + 1, 6];
            dataArray[0, 0] = "First Name";
            dataArray[0, 1] = "Last Name";
            dataArray[0, 2] = "Company Name";
            dataArray[0, 3] = "E-mail Address";
            dataArray[0, 4] = "Business Tel Number";
            dataArray[0, 5] = "Mobile Number";
 
            for (int i = 1; i < currSelection.Count; i++)
            {
                Outlook.ContactItem contact = currSelection[i] as Outlook.ContactItem;
                if (contact != null)
                {
                    dataArray[i, 0] = contact.FirstName;
                    dataArray[i, 1] = contact.LastName;
                    dataArray[i, 2] = contact.CompanyName;
                    dataArray[i, 3] = contact.Email1Address;
                    dataArray[i, 4] = contact.BusinessTelephoneNumber;
                    dataArray[i, 5] = contact.MobileTelephoneNumber;
                    Marshal.ReleaseComObject(contact);
                }
            }
            worksheetCells = workSheet.Cells;
            insertRange = worksheetCells.get_Resize(
                dataArray.GetLength(0), dataArray.GetLength(1));
            insertRange.Value2 = dataArray;
            workBook.SaveAs(diagSaveFile.FileName);                    
            excelApp.Quit();
        }
 
    }
    finally
    {
        if (worksheetCells != null) Marshal.ReleaseComObject(worksheetCells);
        if (insertRange != null) Marshal.ReleaseComObject(insertRange);
        if (workSheet != null) Marshal.ReleaseComObject(workSheet);
        if (workSheets != null) Marshal.ReleaseComObject(workSheets);
        if (workBook != null) Marshal.ReleaseComObject(workBook);
        if (workBooks != null) Marshal.ReleaseComObject(workBooks);
        if (excelApp != null) Marshal.ReleaseComObject(excelApp);
        if (currSelection != null) Marshal.ReleaseComObject(currSelection);
        if (currExplorer != null) Marshal.ReleaseComObject(currExplorer);
    }
}

The code above will prompt the user to select a location to save the export file and then add the selected contacts’ data to the Excel file. To see it in action, run the add-in in Outlook and you will notice a new context-menu item in both the More Action ribbon button as well as in the contact context menu:

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Outlook to Excel Exporter add-in (C#)

You may also be interested in:

• Creating COM add-ins for Excel 2013 – 2007. Customizing Excel Ribbon
• Excel add-in development in Visual Studio