Downloading email report where only 1 or 2 of 3 user properties exists

Question

I have total 3 custom user properties but not all emails have all three properties. Sometimes email can only have 2 of 3 user properties. Now the problem is when I try to download all emails into Excel, I get following error because some of the emails are missing these user properties.

Object reference not set to an instance of an object.

How to can bypass this error and have cell empty in Excel where user property are missing.

Here is my code.

private void Export_Click(object sender, RibbonControlEventArgs e)
        {

            Outlook.UserProperties MailUserProperties = null;
            Outlook.UserProperty MailUserProperty = null;

            Excel.Application oApp = null;
            Excel.Workbook oWB = null;
            Excel.Worksheet oSheet = null;

            oApp = new Excel.Application();
            oWB = oApp.Workbooks.Add();
            oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);


            try
            {
                for (int i = 2; i <= selectedFolder.Items.Count; i++)
                {
                    Outlook.MailItem mail = (Outlook.MailItem)selectedFolder.Items[i];

                    MailUserProperties = mail.UserProperties;

                    oSheet.Cells[i, 1] = i.ToString();
                    oSheet.Cells[i, 2] = mail.Sender;
                    oSheet.Cells[i, 3] = mail.Subject;
                    oSheet.Cells[i, 4] = mail.ReceivedTime.ToLongDateString();

                    for (int j = 1; j <= MailUserProperties.Count; j++)
                    {
                        MailUserProperty = MailUserProperties[j];
                        if (MailUserProperty != null)
                        {
                            try
                            {
                                oSheet.Cells[i, 5] = mail.UserProperties["Ownership"].Value;
                                oSheet.Cells[i, 6] = mail.UserProperties["CompletedTime"].Value;
                                oSheet.Cells[i, 7] = mail.UserProperties["TimeSpent"].Value;
                            }
                            catch(Exception ex)
                            {
                                MessageBox.Show("The following error occured." + ex.Message);
                            }
                        }
                    }
                }
                oSheet.UsedRange.Columns.AutoFit();
            }

            catch (System.Runtime.InteropServices.COMException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                    // Code to save in Excel
            }
        }

Thank you.

Answer 1

I would create a try/catch section per object.

Then when one of the properties is missing you can insert a zero string.

---

private void Export_Click(object sender, RibbonControlEventArgs e)
{
    Outlook.UserProperties MailUserProperties = null;
    Outlook.UserProperty MailUserProperty = null;

    Excel.Application oApp = new Excel.Application();
    Excel.Workbook oWB = oApp.Workbooks.Add();
    Excel.Worksheet oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);

    try
    {
        for (int i = 2; i <= selectedFolder.Items.Count; i++)
        {
            Outlook.MailItem mail = (Outlook.MailItem)selectedFolder.Items[i];

            MailUserProperties = mail.UserProperties;

            oSheet.Cells[i, 1] = i.ToString();
            oSheet.Cells[i, 2] = mail.Sender;
            oSheet.Cells[i, 3] = mail.Subject;
            oSheet.Cells[i, 4] = mail.ReceivedTime.ToLongDateString();

            for (int j = 1; j <= MailUserProperties.Count; j++)
            {
                MailUserProperty = MailUserProperties[j];
                if (MailUserProperty != null)
                {
                    var ownership = string.Empty;
                    var completedTime = string.Empty;
                    var timeSpent = string.Empty;

                    try
                    {
                        ownership = mail.UserProperties["Ownership"].Value;
                    }
                    catch (Exception)
                    {
                        ownership = string.Empty; //or you can pass a string like <MISSING>
                    }
                    finally
                    {
                        oSheet.Cells[i, 5] = ownership;
                    }

                    try
                    {
                        completedTime = mail.UserProperties["CompletedTime"].Value;
                    }
                    catch (Exception)
                    {
                        completedTime = string.Empty;
                    }
                    finally
                    {
                        oSheet.Cells[i, 6] = completedTime;
                    }

                    try
                    {
                        timeSpent = mail.UserProperties["TimeSpent"].Value;
                    }
                    catch (Exception)
                    {
                        timeSpent = string.Empty;
                    }
                    finally
                    {
                        oSheet.Cells[i, 7] = timeSpent;
                    }

                }
            }
        }
        oSheet.UsedRange.Columns.AutoFit();
    }

    catch (System.Runtime.InteropServices.COMException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    finally
    {
        // Code to save in Excel
    }
}