locked
Exporting text from email body to excel by using C# RRS feed

  • Question


  • I am receiving many emails and I want to do a automation task by using C#.net . Each email will have a standard structure. I want to take data from  these emails and export it into excel.

    I want to extract the email date,body,subject line all and have to export it to excel sheet.    

    How can I take these messages as they come in  OUTLOOK, extract this data from them, and export it to Excel?


    I have to do this Automation by using C#.

    If you can help me with this project I'd greatly appreciate it.

    Thanks
    Sagar





    • Edited by sagar jena Friday, September 9, 2011 10:08 AM
    Friday, September 9, 2011 9:35 AM

Answers

  • The following demostrates how to retreive data from items within an Outlook folder (called "MySubFolderName" under the Inbox folder) using .NET. Currently i am displaying it on Screen, later u can store it in Excel using Excel api's.

    First add a reference to the Outlook COM object your project:

    1. In VS.NET right click on References and choose Add Reference.
    2. Select the COM tab
    3. Choose "Microsoft Outlook 11.0 Object Library" (this is for MS Office 2003 - I think 10.0 is for Office XP) and click Select.
    4. Click OK.

    Note that you can access any Outlook/Exchange object types, eg Appointments, Notes, Tasks, Emails etc - just use intellisense to select which one (eg Microsoft.Office.Interop.Outlook. ... - see definition of variable called 'item' below).

    Here's the code:

    Microsoft.Office.Interop.Outlook.Application app = null;
    Microsoft.Office.Interop.Outlook._NameSpace ns = null;
    Microsoft.Office.Interop.Outlook.PostItem item = null;
    Microsoft.Office.Interop.Outlook.MAPIFolder inboxFolder = null;
    Microsoft.Office.Interop.Outlook.MAPIFolder subFolder = null;

    try
    {
      app = new Microsoft.Office.Interop.Outlook.Application();
      ns = app.GetNamespace("MAPI");
      ns.Logon(null,null,false, false);

      inboxFolder = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
      subFolder = inboxFolder.Folders["MySubFolderName"]; //folder.Folders[1]; also works
      Console.WriteLine("Folder Name: {0}, EntryId: {1}", subFolder.Name, subFolder.EntryID);
      Console.WriteLine("Num Items: {0}", subFolder.Items.Count.ToString());

      for(int i=1;i<=subFolder.Items.Count;i++)
      {
        item = (Microsoft.Office.Interop.Outlook.PostItem)subFolder.Items[i];
        Console.WriteLine("Item: {0}", i.ToString());
        Console.WriteLine("Subject: {0}", item.Subject);
        Console.WriteLine("Sent: {0} {1}" item.SentOn.ToLongDateString(), item.SentOn.ToLongTimeString());
        Console.WriteLine("Categories: {0}", item.Categories);
        Console.WriteLine("Body: {0}", item.Body);
        Console.WriteLine("HTMLBody: {0}", item.HTMLBody);
      }
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    {
      ns = null;
      app = null;
      inboxFolder = null;
    }


    Pravin Arote, MCTS - Web Technologies. If reply answers your question, Please mark as Answer :)
    • Proposed as answer by Pravin S Arote Friday, September 9, 2011 10:30 AM
    • Marked as answer by Martin_Xie Monday, September 12, 2011 4:40 AM
    Friday, September 9, 2011 10:30 AM
  • Just change "Microsoft.Office.Interop.Outlook.PostItem" to (Microsoft.Office.Interop.Outlook.MailItem).

    Let me know the result.


    Pravin Arote, MCTS - Web Technologies. If reply answers your question, Please mark as Answer :)
    • Proposed as answer by Pravin S Arote Friday, September 9, 2011 11:20 AM
    • Marked as answer by sagar jena Friday, September 9, 2011 11:28 AM
    Friday, September 9, 2011 11:19 AM

All replies

  • The following demostrates how to retreive data from items within an Outlook folder (called "MySubFolderName" under the Inbox folder) using .NET. Currently i am displaying it on Screen, later u can store it in Excel using Excel api's.

    First add a reference to the Outlook COM object your project:

    1. In VS.NET right click on References and choose Add Reference.
    2. Select the COM tab
    3. Choose "Microsoft Outlook 11.0 Object Library" (this is for MS Office 2003 - I think 10.0 is for Office XP) and click Select.
    4. Click OK.

    Note that you can access any Outlook/Exchange object types, eg Appointments, Notes, Tasks, Emails etc - just use intellisense to select which one (eg Microsoft.Office.Interop.Outlook. ... - see definition of variable called 'item' below).

    Here's the code:

    Microsoft.Office.Interop.Outlook.Application app = null;
    Microsoft.Office.Interop.Outlook._NameSpace ns = null;
    Microsoft.Office.Interop.Outlook.PostItem item = null;
    Microsoft.Office.Interop.Outlook.MAPIFolder inboxFolder = null;
    Microsoft.Office.Interop.Outlook.MAPIFolder subFolder = null;

    try
    {
      app = new Microsoft.Office.Interop.Outlook.Application();
      ns = app.GetNamespace("MAPI");
      ns.Logon(null,null,false, false);

      inboxFolder = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
      subFolder = inboxFolder.Folders["MySubFolderName"]; //folder.Folders[1]; also works
      Console.WriteLine("Folder Name: {0}, EntryId: {1}", subFolder.Name, subFolder.EntryID);
      Console.WriteLine("Num Items: {0}", subFolder.Items.Count.ToString());

      for(int i=1;i<=subFolder.Items.Count;i++)
      {
        item = (Microsoft.Office.Interop.Outlook.PostItem)subFolder.Items[i];
        Console.WriteLine("Item: {0}", i.ToString());
        Console.WriteLine("Subject: {0}", item.Subject);
        Console.WriteLine("Sent: {0} {1}" item.SentOn.ToLongDateString(), item.SentOn.ToLongTimeString());
        Console.WriteLine("Categories: {0}", item.Categories);
        Console.WriteLine("Body: {0}", item.Body);
        Console.WriteLine("HTMLBody: {0}", item.HTMLBody);
      }
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    {
      ns = null;
      app = null;
      inboxFolder = null;
    }


    Pravin Arote, MCTS - Web Technologies. If reply answers your question, Please mark as Answer :)
    • Proposed as answer by Pravin S Arote Friday, September 9, 2011 10:30 AM
    • Marked as answer by Martin_Xie Monday, September 12, 2011 4:40 AM
    Friday, September 9, 2011 10:30 AM
  • This works fine upto retrieving folder name and no. of items from inbox sub folder.

    Then after i am getting an error message:

    Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Outlook.PostItem'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{00063024-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

    Your Help is needed.

    Thanks

    Sagar


    Friday, September 9, 2011 11:07 AM
  • Just change "Microsoft.Office.Interop.Outlook.PostItem" to (Microsoft.Office.Interop.Outlook.MailItem).

    Let me know the result.


    Pravin Arote, MCTS - Web Technologies. If reply answers your question, Please mark as Answer :)
    • Proposed as answer by Pravin S Arote Friday, September 9, 2011 11:20 AM
    • Marked as answer by sagar jena Friday, September 9, 2011 11:28 AM
    Friday, September 9, 2011 11:19 AM
  • Thank u very much  Pravin...Its working...
    Friday, September 9, 2011 11:28 AM
  • Hi sagar,

    We're glad to hear that you got it working.

    Thank you for your active participation in MSDN Forum.

    If you have any difficulty in future programming, you are welcome to post here again.

    Thanks Pravin for your solution and help.

     


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 12, 2011 4:35 AM