.NET - Querying Outlook and OneNote with LINQ

I am one of these users who works intensively with Outlook and OneNote. They are great tools to manage emails, contacts, appointments, notebooks, ... but sometimes they lack querying features. That is why I developed two wrapper classes for Outlook 2007 and OneNote 2007. These 2 classes can be used to execute LINQ to Objects (.NET 3.5) queries on emails, appointments, tasks, notes, contacts, notebooks, sections and pages. So it is a kind of LINQ to Outlook and LINQ to OneNote.

In the first part of this article I will describe how I developed these wrapper classes. In the second part I will show some examples of how to use the OutlookProvider and OneNoteProvider classes in LINQ to Objects queries.

• Wrapper class for Outlook 2007
• Wrapper class OneNote 2007
• Querying Outlook with LINQ
• Querying OneNote with LINQ
• Execute queries on Office data with LINQPad (additional article)
  
  
• Sources of classes and console demo application

Wrapper class for Outlook 2007

Accessing data of Outlook is quite easy. You first have to add a COM reference to the Microsoft Outlook 12.0 Object Library. Two namespace references will be added. Microsoft.Office.Core and Microsoft.Office.Interop.Outlook.

Create an instance of the Outlook Application object :

Microsoft.Office.Interop.Outlook.Application outlook;
outlook = new Microsoft.Office.Interop.Outlook.Application();

MAPIFolder represents an Outlook folder. There is a whole list of constants to access one of the available folders : olFolderCalendar, olFolderContacts, olFolderDeletedItems, olFolderDrafts, olFolderInbox, olFolderJournal, olFolderNotes, olFolderOutbox, olFolderSentMail, olFolderTasks, olPublicFoldersAllPublicFolders, and olFolderJunk. The GetDefaultFolder method of the Session object will return a collection of items.

I have created an OutlookProvider class which will create an instance of the Outlook Application in the constructor. Afterwards I have added several IEnumerable properties which will return a collection of items of the most interesting folders of Outlook. e.g. The ContactItems property :

public IEnumerable<Microsoft.Office.Interop.Outlook.ContactItem> ContactItems
{ 
  get 
  {
    Microsoft.Office.Interop.Outlook.MAPIFolder folder =
      outlook.GetNamespace("MAPI").GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderContacts);
    return folder.Items.OfType<Microsoft.Office.Interop.Outlook.ContactItem>();      
  }
}

The class diagram shows the relation between my OutlookProvider class and the interfaces of the most important Outlook items (AppointmentItem, ContactItem, MailItem, NoteItem, TaskItem). As you can see the list of available properties is quite long and you can access all information which is available in Outlook.

Class diagram

Wrapper class for OneNote 2007

I thought that accessing the data of OneNote 2007 would be similar as with Outlook. That was wrong; retrieving data in OneNote is more complex because everything is stored as XML. The notebooks, sections and pages can be retrieved as a XML document which describes the hierarchy of OneNote. Once you have the page ID's you can also access the XML content of a page.

First add a reference to Microsoft OneNote 12.0 Object Library (Microsoft.Office.Interop.OneNote namespace) and create an instance of the OneNote Application object.

Microsoft.Office.Interop.OneNote.Application oneNote;
oneNote = new Microsoft.Office.Interop.OneNote.Application();

The OneNote Application object has a GetHierarchy method which will retrieve the notebook node hierarchy structure with notebooks, sections and pages. The method will return a string in OneNote 2007 XML format and this XML string will provide properties like ID, name, last modified time, ... for each node.

oneNote.GetHierarchy(null, Microsoft.Office.Interop.OneNote.HierarchyScope.hsPages, out oneNoteXMLHierarchy);

This XML document will look like :

Information about the Office XML Schemas can be downloaded from the website of Microsoft : 2007 Office System: XML Schema Reference

In .NET 3.5 we have the disposal of LINQ to XML which is the best technique to query this XML document. You first have to convert the XML string into an instance of the XElement class. This can be done by calling the Parse method.

XElement oneNoteHierarchy = XElement.Parse(oneNoteXMLHierarchy);

oneNoteHierarchy.Elements() will contain all child elements from the root. As you can see in the XML data example, there are 2 kinds of children from the Notebooks root; Notebook and UnUnfiledNotes elements. If you only want to query the notebooks, you have to specify this element name. e.g. oneNoteHierarchy.Elements("Notebook") or oneNoteHierarchy.Elements("one:Notebook"). Both options won't work. "one" is a prefix of the namespace, so you have to add the full name of the namespace. To get the full name of the prefix "one" you have to call the GetNamespaceOfPrefix method of the XElement class.

So the full source code to retrieve the OneNote notebooks looks like :

Microsoft.Office.Interop.OneNote.Application oneNote;
oneNote = new Microsoft.Office.Interop.OneNote.Application();
 
oneNote.GetHierarchy(null, Microsoft.Office.Interop.OneNote.HierarchyScope.hsPages, out oneNoteXMLHierarchy);
 
XElement oneNoteHierarchy = XElement.Parse(oneNoteXMLHierarchy);
 
XNamespace one = oneNoteHierarchy.GetNamespaceOfPrefix("one");
 
var notebooks = from o in oneNoteHierarchy.Elements(one + "Notebook")
                select new { Name = o.Attribute("name").Value };

Ok, this works fine but I don't like the untyped LINQ to XML queries. If you have a lot of these LINQ queries it will make your code error prone. So I have created my own classes which will be used to transform the XML document into my own hierarchy.

public class OneNoteNotebook
{
  public string ID { get; set; }
  public string Name { get; set; }
  public string NickName { get; set; }
  public string Path { get; set; }
  public Color Color { get; set; }
}
 
public class OneNoteSection
{
  public string ID { get; set; }
  public string Name { get; set; }
  public string Path { get; set; }
  public bool Encrypted { get; set; }
  public Color Color { get; set; }
}
 
public class OneNotePage
{
  public string ID { get; set; }
  public string Name { get; set; }
  public DateTime DateTime { get; set;}
  public DateTime LastModified { get; set; }
}
 
public class OneNoteExtNotebook : OneNoteNotebook
{
  public IEnumerable<OneNoteExtSection> Sections { get; set; }
}
 
public class OneNoteExtSection : OneNoteSection
{
  public IEnumerable<OneNotePage> Pages { get; set; }
}
 
public class OneNoteExtPage : OneNotePage
{
  public OneNoteSection Section { get; set; }
  public OneNoteNotebook Notebook { get; set; }
}

The derived classes are needed to include a collection of child classes to make a hierarchy. In my wrapper class OneNoteProvider I created two properties which are called NotebookItems and PageItems. Both will return an IEnumerable.

Both properties will execute a different LINQ to XML query. NotebookItems will return a hierarchy of notebooks which can be used to access the notebooks, their sections and their pages.

public IEnumerable<OneNoteExtNotebook> NotebookItems
{
  get
  {
    XElement oneNoteHierarchy = XElement.Parse(oneNoteXMLHierarchy);
    XNamespace one = oneNoteHierarchy.GetNamespaceOfPrefix("one");
 
    IEnumerable<OneNoteExtNotebook> oneNoteNotebookItems = 
      from n in oneNoteHierarchy.Elements(one + "Notebook")
      select new OneNoteExtNotebook()
      {
        ID = n.Attribute("ID").Value,
        Name = n.Attribute("name").Value,
        NickName = n.Attribute("nickname").Value,
        Path = n.Attribute("path").Value,        
        Color = ColorTranslator.FromHtml(n.Attribute("color").Value),
        Sections = n.Elements().Select(s => new OneNoteExtSection()
        {
          ID = s.Attribute("ID").Value,
          Name = s.Attribute("ID").Value,
          Path = s.Attribute("path").Value,
          Color = ColorTranslator.FromHtml(s.Attribute("color").Value),
          Encrypted = ((s.Attribute("encrypted") != null) && (s.Attribute("encrypted").Value == "true")),
          Pages = s.Elements().Select(p => new OneNotePage()
          {
            ID = p.Attribute("ID").Value,
            Name = p.Attribute("name").Value,
            DateTime = XmlConvert.ToDateTime(p.Attribute("dateTime").ToString(), XmlDateTimeSerializationMode.Utc),
            LastModified = XmlConvert.ToDateTime(p.Attribute("lastModifiedTime").ToString(), XmlDateTimeSerializationMode.Utc),
          })
        })
      };      
 
    return oneNoteNotebookItems;
  }
}

  Sometimes it is handy to just have a simple list of all pages . So therefore I have added the PageItems property. This will return a collection of pages which includes info about section and notebook.

public IEnumerable<OneNoteExtPage> PageItems
 {
   get
   {
     XElement oneNoteHierarchy = XElement.Parse(oneNoteXMLHierarchy);
     XNamespace one = oneNoteHierarchy.GetNamespaceOfPrefix("one");
 
     IEnumerable<OneNoteExtPage> oneNotePageItems = 
       from o in oneNoteHierarchy.Elements(one + "Notebook").Elements().Elements()
       select new OneNoteExtPage()
       {
         ID = o.Attribute("ID").Value,
         Name = o.Attribute("name").Value, 
         DateTime = XmlConvert.ToDateTime(o.Attribute("dateTime").Value, XmlDateTimeSerializationMode.Utc),
         LastModified = XmlConvert.ToDateTime(o.Attribute("lastModifiedTime").Value, XmlDateTimeSerializationMode.Utc),
         Section = new OneNoteSection() 
         {
           ID = o.Parent.Attribute("ID").Value,
           Name = o.Parent.Attribute("name").Value,
           Path = o.Parent.Attribute("path").Value,
           Color = ColorTranslator.FromHtml(o.Parent.Attribute("color").Value),
           Encrypted = ((o.Parent.Attribute("encrypted") != null) && (o.Parent.Attribute("encrypted").Value == "true"))
         },
         Notebook = new OneNoteNotebook()
         {
           ID = o.Parent.Parent.Attribute("ID").Value,
           Name = o.Parent.Parent.Attribute("name").Value,
           NickName = o.Parent.Parent.Attribute("nickname").Value,
           Path = o.Parent.Parent.Attribute("path").Value,
           Color = ColorTranslator.FromHtml(o.Parent.Parent.Attribute("color").Value)
         }
       };
 
     return oneNotePageItems;
   }
 }

Class diagram

Querying Outlook with LINQ

Once you have these wrapper classes querying Outlook becomes very easy. When you execute a query you will get a dialog box of Outlook to ask permission to access the data. Also be aware that queries on large amounts of data can become very slow.

The items of these collections have a direct link with the data in Outlook. So it is also possible to modify the items and submit the changes to Outlook. Therefore call the Save method of an item (MailItem, ContactItem, TaskItem, ...). My OutlookProvider class has also a public reference to the Outlook Application object which can be used to add or delete items.

Some examples :

CONTACTS : List of all contacts which do not have an email address

OutlookProvider outlookProvider = new OutlookProvider();
 
// All contacts which do not have an email address
var queryContacts = from contact in outlookProvider.ContactItems
                    where contact.Email1Address == null
                    select contact;
 
foreach (var item in queryContacts)
  Console.WriteLine("{0} {1} {2}", item.FirstName, item.LastName, item.HomeAddressCity);

OutlookProvider outlookProvider = new OutlookProvider();
 
// How many mails did I sent each day of last week
var queryMails = from mail in outlookProvider.SentMailItems
                 where mail.SentOn > DateTime.Now.AddDays(-7)
                 orderby mail.SentOn descending
                 group mail by mail.SentOn.Date into g
                 select new { SentOn = g.First().SentOn.Date, Count = g.Count() };
 
foreach (var item in queryMails)
  Console.WriteLine("{0} {1}", item.SentOn, item.Count);

APPOINTMENTS (CALENDAR) : List of all my sport activities for next month

OutlookProvider outlookProvider = new OutlookProvider();
 
// All my sport activities for next month
var queryAppointments = from app in outlookProvider.CalendarItems
                        where app.Start > DateTime.Now
                          && app.Start < DateTime.Now.AddMonths(1)
                          && app.Categories != null
                          && app.Categories.Contains("Sport")
                        select app;
 
foreach (var item in queryAppointments)
  Console.WriteLine("{0} {1} {2}", item.Start, item.End, item.Subject);

TASKS : List of all tasks which are in progress and have a percentage complete lower then 50

OutlookProvider outlookProvider = new OutlookProvider();
 
// All tasks which are in progress and have a percentage complete lower then 50
var queryTasks = from task in outlookProvider.TaskItems
                 where task.Status == Microsoft.Office.Interop.Outlook.OlTaskStatus.olTaskInProgress
                   && task.PercentComplete < 50
                 select task;
 
foreach (var item in queryTasks)
  Console.WriteLine("{0} {1} {2} {3}", item.Subject, item.PercentComplete, item.StartDate, item.Categories);

OutlookProvider outlookProvider = new OutlookProvider();
 
// Set flag complete of all sent mails of last week which have at least one attachment
var queryAttMails = from mail in outlookProvider.SentMailItems
                    where mail.SentOn > DateTime.Now.AddDays(-7)
                    && mail.Attachments.Count > 0
                    select mail;
 
foreach (var item in queryAttMails)
{
  Console.WriteLine("{0} {1} {2}", item.SentOn, item.To, item.Subject);
  item.FlagStatus = Microsoft.Office.Interop.Outlook.OlFlagStatus.olFlagComplete;
  item.Save();
}

SENT MAILS : Show total size of attachments of sent mails

OutlookProvider outlookProvider = new OutlookProvider();
 
// Query mail attachtments and calculated total sum of size in Kb
var query = from mail in outlookProvider.SentMailItems
            let m = (mail as MailItem)
            let a = m.Attachments.OfType<Attachment>()
            where m.SentOn > DateTime.Now.AddDays(-31)
            && m.Attachments.Count > 0
            //  && a.Count() > 0
            select new
            {
              m.SentOn,
              m.To,
              m.Subject,
              AttachmentsCount = m.Attachments.Count,
              AttachmentsSize = a.Sum(s => s.Size) / 1024
            };

CONTACTS & TASKS : Create a new task containing all contact names which do not have a postalcode

// All contacts which do not have a postalcode
var queryContactsWithoutPostalCode = from contact in outlookProvider.ContactItems
                                     where contact.HomeAddressPostalCode == null
                                     && contact.HomeAddressCity != null
                                     select contact;
 
string contacts = "";
foreach (var item in queryContactsWithoutPostalCode)
  contacts += item.FirstName + " " + item.LastName + "(" + item.HomeAddressCity + "), ";
 
// Create a new task containing all contact names which do not have a postalcode
Microsoft.Office.Interop.Outlook.TaskItem ti = 
  (Microsoft.Office.Interop.Outlook.TaskItem)outlookProvider.Outlook.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olTaskItem);
ti.Subject = "Find missing postalcode for contacts";
ti.Status = Microsoft.Office.Interop.Outlook.OlTaskStatus.olTaskNotStarted;
ti.StartDate = DateTime.Now;
ti.Body = contacts;
ti.Save();

Querying OneNote with LINQ

Querying the collections of the OneNoteProvider class is quite simple because you can create strong typed LINQ to Objects queries. These collections are read-only so they can not be used to modify the data of OneNote. But the OneNote Application class has methods to update pages (UpdatePageContent) and add pages (CreateNewPage) by passing an ID and the XML content.

Depending the situation you can choose between the PageItems collection or NotebookItems hierarchy when defining a LINQ query. Some examples :

PAGES (PageItems) : List of all pages which have been modified last month

OneNoteProvider oneNoteProvider = new OneNoteProvider();
 
// All pages which have been modified last month
var queryPages = from page in oneNoteProvider.PageItems
                 where page.LastModified > DateTime.Now.AddMonths(-1)
                 orderby page.LastModified descending
                 select page;
 
foreach (var item in queryPages)
  Console.WriteLine("{0} {1} {2} {3} {4}", item.LastModified, item.Notebook.Name, item.Section.Name, item.Name, item.DateTime);

OneNoteProvider oneNoteProvider = new OneNoteProvider();
 
// All sections which are encrypted
var queryEncryptedSections = from nb in oneNoteProvider.NotebookItems
                             from s in nb.Sections
                             where s.Encrypted == true
                             select new { NotebookName = nb.Name, SectionName = s.Name };
 
foreach (var item in queryEncryptedSections)
  Console.WriteLine("{0} {1}", item.NotebookName, item.SectionName);

OneNoteProvider oneNoteProvider = new OneNoteProvider();
 
// All notebooks and the number of sections they have
var queryNotebooks = (from nb in oneNoteProvider.NotebookItems
                     select new { Notebook = nb.Name, SectionCount = nb.Sections.Count() })
                     .OrderByDescending(n => n.SectionCount);
 
foreach (var item in queryNotebooks)
  Console.WriteLine("{0} {1}", item.Notebook, item.SectionCount);

PAGES (PageItems) : Show XML content of pages which have been changed yesterday

// Show XML content of pages which have been changed yesterday
var queryChangedPages = from page in oneNoteProvider.PageItems
                        where page.LastModified > DateTime.Now.AddDays(-1)
                        select page;
 
foreach (var item in queryChangedPages)
{
  string pageXMLContent = "";
  Console.WriteLine("{0} {1} {2} {3} {4}", item.LastModified, item.Notebook.Name, item.Section.Name, item.Name, item.DateTime);
  oneNoteProvider.OneNote.GetPageContent(item.ID, out pageXMLContent, Microsoft.Office.Interop.OneNote.PageInfo.piBasic);
  Console.WriteLine("{0}", pageXMLContent);
}