First I extracted some data from the outlook inbox with C# to a CSV file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Outlook;
using System.IO;

namespace Common
{
  public class Generator
  {
    const string Splitter = ",";
    const string CsvFIleName = "MailData.csv";
    const string CsvPath = @"C:\BDATA";

    public void Start()
    {
      Application outlook = new Application();

      // Get the inbox folder
      MAPIFolder folder = outlook.GetNamespace("MAPI").GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);

      // Get received mails
      IEnumerable<Microsoft.Office.Interop.Outlook.MailItem> items = folder.Items.OfType<Microsoft.Office.Interop.Outlook.MailItem>();

      // Clear file
      File.WriteAllText(Path.Combine(CsvPath, CsvFIleName), string.Empty);

      foreach (MailItem item in items)
      {
        StringBuilder csvContent = new StringBuilder(string.Empty);

        // Add [Sender] to output
        if (item.Sender != null && !string.IsNullOrEmpty(item.Sender.Name)) { csvContent.Append(item.Sender.Name.Replace(Splitter, string.Empty)); }
        csvContent.Append(Splitter);

        // Add [Subject] to output
        if (!string.IsNullOrEmpty(item.Subject)) { csvContent.Append(item.Subject.Replace(Splitter, string.Empty)); }
        csvContent.Append(Splitter);

        // Add [Year] to output
        csvContent.Append(item.ReceivedTime.Year);
        csvContent.Append(Splitter);

        // Add [Month] to output
        csvContent.Append(item.ReceivedTime.Month);
        csvContent.Append(Splitter);

        // Add [Day] to output
        csvContent.Append(item.ReceivedTime.Day);
        csvContent.Append(Splitter);

        // Add [Hour] to output
        csvContent.Append(item.ReceivedTime.Hour);

        // Per line export, so you will see the filesize grow on filesystem
        using (StreamWriter sw = File.AppendText(Path.Combine(CsvPath, CsvFIleName)))
        {
          sw.WriteLine(csvContent.ToString());
        }
      }
    }
  }
}

  • Open the CSV file with Microsoft Office Excel 2010 and File > Save As “MailData.xlsx”

image

image

  • Click on the ribbon tab [Data], select column A and then click on [Text to Columns]

image

  • Click [Delimited] and click [Next]

image

  • Uncheck [Tab] and check [Comma], then click [Next]

image

  • Click Finish

image

  • Result

image

  • Press Ctrl + L to create a table

image

  • Rename the generated column names (Column1 to Name etc.)

image

  • Click on the excel tab [PowerPivot] and click on [Create Linked Table]

image

  • In the powerpivot screen add column [Count] with formula [=1]

image

  • Click on PivotTable to create a PowerPivot report in Microsoft Excel 2010

image

  • Click on [Chart and Table (Horizontal)

image

  • Click ok
  • Select chart
  • Drag and drop [Count] to [Values]
  • Drag and drop [Name] to [Axis Fields (Categories)]
  • Select chart and right click chart
  • Click [Change Chart Type]

image

  • Select Bar and click [OK]

image

  • Click on [Name] in the chart

image

  • Choose More Sort Options…

image

  • Sort [Ascending (A to Z) by] [Sum of Count ]

image

 

  • Again click on [Name] in the chart and click [Value Filters] [Greater Than]

image

  • The email adresses in the screendumps are partial hidden, but you can see by hovering over the first bar, that [Heide] has send me the most messages [679]

image

  • Select chart and Drag and drop [Hour] to [Slicers Horizontal]

image

  • If you click on 12 in the hour slicer, you can see [Heide] had sent 49 mails on 12 o’clock

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.