none
Automatically Export Incoming Emails to Existing Excel spreadsheet and Insert New Data RRS feed

  • Question

  • Dear MS experts,

    Looking for ways to automatically export incoming email subject and contents to existing Excel spreadsheet and insert new data row for any new incoming emails from same email address.  Not certain where to start...new to VB.  Should this be posted in Outlook or Excel?

    Please see below for an example: 

    1) Copy and paste subject title to Excel

    2) Copy and paste message body into Excel according to layout in Excel spreadsheet.

    3) The spreadsheet is used for collecting info from different incoming emails as well.

    Thank you,

    Newbiesan

    Incoming Email----

    From: xxxx Server [mailto:xxxx@incoming-email.com]

    Subject: File Transfer Notification: File File xxxxxxxxNewFile-12152012.txt Received

    Server Notification Details:

    =======================================================

    Event Name: File Uploaded

    Folder Name: /xxxxxxx/Special_Projects

    File Name: File xxxxxxxxNewFile-12152012.txt

    Byte Count: 12345678

    File Creation Date: 12/15/2012

    File Creation Time: 9:29:56

    Afterward export above info into existing Excel file and keep adding new row data from new incoming email from xxxx@incoming-email.com:

    File Creation Date File Name Email Subject  Record Count (incl.header &   trailer)  Byte Count: Folder Name
    12/15/2012 File   xxxxxxxxNewFile-12152012.txt File   Transfer Notification: File xxxxxxxxNewFile.txt Received Info   extract from another auto-email 438291 /xxxxxxx/Special_Projects
    Saturday, December 15, 2012 6:09 PM

Answers

  • you can create outlook macro and attach it to rule that will trigger when subject start with certain words (File Transfer Notification). You will get mailitem as a parameter for your macro. From there you should grab instance of excel (or create new one), open your spreadsheet, find proper place to insert row and extract data there. To be honest you are not asking a question about specific problem, you have whole project (albeit small one) on your hands with multiple questions and problems. Please do not expect to be given full working code for that.
    Monday, December 17, 2012 8:09 AM
  • all mail body properties are of string type - Body, HTMLBody, RTFBody. Either use standard .net string methods like IndexOf, Substring, etc. or load contents into proper objects and operate on them (for example for HTMLBody Html Agility Pack is excellent choice). As for loading data into excel, there are thousands of examples on the internet, just search a little. I would use EEPlus which is a wrapper over open xml sdk and create/read/update xlsx file
    Wednesday, December 19, 2012 5:05 PM

All replies

  • you can create outlook macro and attach it to rule that will trigger when subject start with certain words (File Transfer Notification). You will get mailitem as a parameter for your macro. From there you should grab instance of excel (or create new one), open your spreadsheet, find proper place to insert row and extract data there. To be honest you are not asking a question about specific problem, you have whole project (albeit small one) on your hands with multiple questions and problems. Please do not expect to be given full working code for that.
    Monday, December 17, 2012 8:09 AM
  • Ok could you provide any examples of parsing the message body into Excel.  Any other incoming mail can be appended into the existing Excel file? 
    Wednesday, December 19, 2012 6:51 AM
  • all mail body properties are of string type - Body, HTMLBody, RTFBody. Either use standard .net string methods like IndexOf, Substring, etc. or load contents into proper objects and operate on them (for example for HTMLBody Html Agility Pack is excellent choice). As for loading data into excel, there are thousands of examples on the internet, just search a little. I would use EEPlus which is a wrapper over open xml sdk and create/read/update xlsx file
    Wednesday, December 19, 2012 5:05 PM