none
Copy a Table from outlook msg body to Excel on receiving mail RRS feed

  • Question

  • Hi,

    I want to copy a table from an email in outlook to excel automatically whenever I get a mail with subject "Tasks - <currentdate-month>" using VBA/macros

    I get at least 10 mails each day throughout the month with the same subject. The email body contains a table in the following table format

    Associate Name Task Onshore Estimated hrs

    A <task> osA 8

    B <Task2> osA 8

    C <Tsk3> osA 8

    D <Task4> osB 8

    Each onshore lead will assign a task to their respective offshore associate. So each mail contains only few cells which are filled. For ex, onshore A will only assign task to associate A, B, and C in the above table. He fills associate name,task,onshore and estimated hrs and sends me back. Same way, Onshore B will assign task to few associates with task and estimated hrs.

    So I need a macro code which triggers itself upon the mail arrrival with subject "Tasks - <Current Date - Month>" and writes only the columns A, B,C, D which are filled. The second mail trigger should write from the next column in the excel [appending the same excel sheet]

    It would be of great help if someone can provide me the code as I have a target set for me by my manager[jan4].

    thank u in advance

    Wednesday, December 30, 2015 3:31 PM

Answers

  • Hello Bunny,

    You need to handle the NewMailEx event of the Application class which is fired when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. 

    This event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItemMeetingItem, or SharingItem. The EntryIDsCollection string contains the Entry ID that corresponds to that item.

    In the event handler you can check out the Subject property and do whatever you need. 

    The Outlook object model provides three main ways for working with item bodies:

    1. Body - a string representing the clear-text body of the Outlook item. 
    2. HTMLBody - a string representing the HTML body of the specified item.
    3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

    You can read more about all these ways in the Chapter 17: Working with Item Bodies. It us up to you which way is to choose to extract all the required data from the message body.

    Finally, you may find the Getting Started with VBA in Outlook 2010 article helpful. 

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:55 AM
    Wednesday, December 30, 2015 8:42 PM

All replies

  • Hello Bunny,

    You need to handle the NewMailEx event of the Application class which is fired when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. 

    This event fires once for every received item that is processed by Microsoft Outlook. The item can be one of several different item types, for example, MailItemMeetingItem, or SharingItem. The EntryIDsCollection string contains the Entry ID that corresponds to that item.

    In the event handler you can check out the Subject property and do whatever you need. 

    The Outlook object model provides three main ways for working with item bodies:

    1. Body - a string representing the clear-text body of the Outlook item. 
    2. HTMLBody - a string representing the HTML body of the specified item.
    3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

    You can read more about all these ways in the Chapter 17: Working with Item Bodies. It us up to you which way is to choose to extract all the required data from the message body.

    Finally, you may find the Getting Started with VBA in Outlook 2010 article helpful. 

    • Marked as answer by David_JunFeng Thursday, January 7, 2016 8:55 AM
    Wednesday, December 30, 2015 8:42 PM
  • thank you Astafiev. Let me have a loot at the links
    Thursday, December 31, 2015 12:08 AM