SharePoint @ Big Scholar

This was fairly complex... until I figured it out. What I wanted to achieve here is the ability to read emails from the Inbox in Microsoft Outlook, extract the email, subject, body, attachments and importance and place all that information into SQL Server. What I am going to discuss will only work if you are using Microsoft Exchange as your mail server and Active Directory. There is probably a way to get it to work without Exchange, but I never got the chance to try.

Make sure you also have Outlook installed on the server you are developing and testing on. For our tests, we used Outlook 2003. I'm not sure if there is any difference to the code if working with Outlook 2007 and since we aren't using Outlook 2007 (yet), I haven't been able to experiment with that. I think the code should be pretty much similar even with Outlook 2007 anyway.

Microsoft.Office.Interop.Outlook

First, let's talk about the standard approach of using the Interop.Outlook class provided by Microsoft.

    using Outlook = Microsoft.Office.Interop.Outlook;

    Outlook.Application oApp = new Outlook.Application();
    Outlook.NameSpace mapiNameSpace = oApp.GetNamespace("MAPI");
    Outlook.MAPIFolder oInbox = mapiNameSpace.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);

By this stage, you have connected to Outlook and set the object oInbox to contain the contents of your Inbox. Now we need to loop through every email in the Inbox and extract all the contents into variables for input into SQL Server.

    for (int i = 1; i <= oInbox.Items.Count; i++)
    {
        string strSubject = ((Outlook.MailItem)oInbox.Items[i]).Subject;
        string strBody = ((Outlook.MailItem)oInbox.Items[i]).Body;
        string strSenderUsername = ((Outlook.MailItem)oInbox.Items[i]).Sender.Alias;
        Outlook.OlImportance olImportance = ((Outlook.MailItem)oInbox.Items[i]).Importance;

        SortedList attachmentList = new SortedList(); // here's where I stored the attachment filenames in case there was more than 1 attachment in the email

        if (((Outlook.MailItem)oInbox.Items[i]).Attachments.Count > 0)

        for (int attachmentCount = 1; attachmentCount <= ((Outlook.MailItem)oInbox.Items[i]).Attachments.Count; attachmentCount++)
        {
            // FYI: you might like to give each attachment saved a unique ID in case there are multiple emails in the inbox with the same named attachment in each email
            // Save the attachment to a temporary folder (I used C:Temp)
            ((Outlook.MailItem)oInbox.Items[i]).Attachments[attachmentCount].SaveAsFile("c:\Temp\" + ((Outlook.MailItem)oInbox.Items[i]).Attachments[attachmentCount].FileName);

            attachmentList.Add(attachmentCount, "c:\Temp\" + ((Outlook.MailItem)oInbox.Items[i]).Attachments[attachmentCount].FileName);
        }

        // just so you don't process the same emails again next time, let's move the emails to a new folder in your Inbox
        Outlook.MAPIFolder ProcessedEmailsFolder;
        ProcessedEmailsFolder = oInbox.Folders["ProcessedEmailsFolder"];
        ((Outlook.MailItem)oInbox.Items[i]).Move(ProcessedEmailsFolder);
    }

And there you have it, you have just read the sender's username, subject, body, importance and saved all attachments to the C:\Temp directory while maintaining a sorted list of attachments in the attachmentList SortedList. Just make sure that the "ProcessedEmailsFolder" folder exists in the Inbox.

Now that you have all those details, you can simply insert all of that into the SQL Server database. I have previously posted about how to upload files to the database in a web part, but what we are doing here is slightly different. Instead of having the user browse for a file to upload, we are selecing the file from C:Temp to upload. There are slight differences and I have gone through the code to do that here.

So let's try building and running the code. For testing purposes, I recommend placing all that code in a Console App so you can easily debug before porting it to another location that makes it a little harder to debug (eg, as a Windows Service). Windows Services are a pain to debug. Anyway, moving on...

When you run the code, you might get a message from Outlook that reads a little like this:

A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No."

Click Yes to continue. You might have to click more than once (depending on the number of emails in your Inbox), so it might be worth your while to allow access for a minute or so.

As you see, this will work, but when running as a windows service every minute or so, that message will stall the program and the emails will not be processed. There is no setting in Outlook to disable that message, I've looked everywhere, searched the net, tried various applications like ClickYES from Express-Soft, Advanced Security for Outlook from MapiLab (who recommended purchasing Security Manager 2007 for Outlook for $149!) and I even tried hacking the registry. Nothing worked... wasted hours looking for a solution. Until I finally found it...

Outlook Redemption

Outlook Redemption is created by Dmitry Streblechenko back in 2001 and has been updated many times since then. The latest version at the time of this post is v 4.7

If you plan on running a timer job of any kind, this is a must as it bypasses the Outlook Security message and runs the code unaffected by the Security Patch. Not to mention, it has a whole lot of features that are not available in the Microsoft Interop assemblies. Also note that at time of this posting, it will only run as 32 bit server so there are a few things that need to be done to make it work on a 64 bit server. I will go into that a little later.

Firstly, download Outlook Redemption.

Follow the instructions to install it. It's pretty much extract and double click on Install. If you don't install Redemption (or register the Redemption.dll), then the code I post below to extract the username, subject, body, importance and attachments won't work.

Once you have instlled Redemption.dll, go to Visual Studio (doesn't matter if it's 2005 or 2008) and add a Reference to the Redemption.dll file. If you're not sure how, right click on References and select Add Reference, then click on the Browse tab and navigate to the location of Redemption.dll, select Redemption.dll and click OK. Now to the code... It took me a while to figure this out and I was over moon when I finally got it.

    using Redemption;

    rdoDefaultFolders olFolderInbox = rdoDefaultFolders.olFolderInbox;
    RDOSession session = new RDOSession();
    RDOFolder rdoFolder;

    try
    {
        session.LogonExchangeMailbox("userprofilename", "mailservername");
    }
    catch (Exception ex)
    {
        // Catch the exception... usually this means that the login failed. If you are running this as a console app
        // then you need to remember to be logged in as the right user (ie. userprofilename in this example)
        // make sure you also set the right mail server name.
        // If the script can't automatically log you in, then Outlook will open prompting you to enter the right details
        // Note that if anything pops up requiring user input, it means that the timer service will only run to that point
    }

    try
    {
        rdoFolder = session.GetDefaultFolder(olFolderInbox);
        object oMailItems = rdoFolder.Items;

        for (int i = 1; i <= rdoFolder.Items.Count; i++)
        {
            RDOMail oMailItem = rdoFolder.Items[i];

            string strSubject = oMailItem.Subject;
            string strBody = oMailItem.Body;
            string strSenderUsername = oMailItem.Sender.Alias;
            int intImportance = oMailItem.Importance; // 0 = Low, 1 = Normal, 2 = High

            SortedList attachmentList = new SortedList();
            // first, check if there are any attachments...

            if (oMailItem.Attachments.Count > 0)
            {
                for (int attachmentCount = 1; attachmentCount <= oMailItem.Attachments.Count; attachmentCount++)
                {
                    // FYI: you might like to give each attachment saved a unique ID in case there are multiple emails in the inbox with the same named attachment in each email
                    // Save the attachment to a temporary folder (I used C:Temp)
                    oMailItem.Attachments[attachmentCount].SaveAsFile("c:\Temp\" + oMailItem.Attachments[attachmentCount].FileName);

                    attachmentList.Add(attachmentCount, "c:\Temp\" + oMailItem.Attachments[attachmentCount].FileName);
                }
            }
            // just so you don't process the same emails again next time, let's move the emails to a new folder in your Inbox
            RDOFolder ProcessedEmailsFolder = rdoFolder.Folders["ProcessedEmailsFolder"];
            oMailItem.Move(ProcessedEmailsFolder);

            ProcessedEmailsFolder = null; // Clean up code
        }
        oMailItem = null;
        rdoFolder = null;
        session.Logoff();
        session = null;
    }
    catch (Exception ex)
    {
        throw ex;
    }

Now, when you run that code, it will run without any Outlook security prompts. Again, make sure that the "ProcessedEmailsFolder" folder exists in the Inbox.

Porting to a 64-bit server

If you are running the code on a 32 bit server, then you don't have to read this section, but I had to port the timer service over from a 32 bit server to a 64 bit server.

I was getting this error in the event log when executing the code:

System.Runtime.InteropServices.COMException (0x80040154_: Retrieving the COM class factory for component with CLSID {29AB7A12-B531-450E-8F7A-EA94C2F3C05F} failed due to the following error: 80040154

In short, that error basically means that Redemption.dll is not registered. But I did register Redemption, I used the provided Install to install the DLL. So where was my problem?

I tried unregistering it and registering it again using %SYSTEMROOT%SySWOW64regsvr32.exe (as described in the readme since it was a 64 bit server). I also tried placing the Redemption.dll file in the path and registering it there, but nothing worked. So I went back to the code to try to see if it was something in the code. After various attempts, I discovered that there is a setting in Visual Studio that you can set to force compiled code to be run as 32 bit. This needs to happen with Redemption since it doesn't run in 64 bit (note you can register it as 64 bit, but the compiled code must be in 32 bit).

Open the code up in Visual Studio, click on Build and then Configuration Manager. For your particular project, click the Platform drop down. By default it shows Any CPU. This means that when you run the code on a 64 bit server, it will run as 64 bit (which is not what we want). To keep it running as 32 bit on a 64 bit server (so the code works), click on <New...> and select x86.

Re-compile and now the exe works on the 64 bit server.

Beautiful! I set up the windows service and now it works a charm, pulling out what I need from the email, placing it in SQL Server and then moving the email to a processed folder so it doesn't get processed again.

Permissions

One more thing... if you're running the script as a user, you need to ensure this user has modify permissions on the C:\Temp directory since all attachments are downloaded there. Just right click on the Temp folder and click on Sharing and Security...