Export Email Text Into Worksheet

  • I would like to expert certain text strings from my e-mail to a worksheet, the mails are generated by a webform from my website, the mails contain text strings as f.i.: FirstName: Ed, how can i export the name Ed to a worksheet with the column name FirstName (every row is a new email)


    I`m from Holland and am not very experienced with VB, I use Outlook and Excel 2007 in a Dutch version.


    I saw this question and answer, bu the code exports only the subject and e-mail adres, i need something to export body text.
    http://www.ozgrid.com/forum/showthread.php?t=94894


    Regards, Ed

  • Re: Export Email Text Into Worksheet


    Fortunately enough, there are quite few differences, if any, between local and english versions of VB. Here is a macro i wrote a few weeks ago for a similar case. You'll find the result workbook as an attached file. Before running it, you'll have to display the VBE window, click on Tools / Reference and check "Microsoft Outlook xx Object Library". Select the messages and execute the macro :

  • Re: Export Email Text Into Worksheet


    I have a similar situation as Ed but the only difference is my message is just a bunch of raw number and text. I would like a macro to export the email message to an excel file and if possible put each string separated by comma in different columns. The following example: is a sample of the message. The message in the " " is just FYI and is not a part of the email message. So basically each row data should have 3 columns. Also the "AND" part of the email message should be automatically removed from the final excel file.


    **************
    Sample message:



    12344568,


    0830,


    AND,


    ,


    1/12/2001 10:05:52 PM,


    ,


    12344568, ("2nd row of the excel file should start from here until the dates")


    0830,


    AND,


    ,


    1/12/2001 10:05:52 PM,


    ,


    12344568, ("3rd row starts from here")


    0830,


    AND,


    ,


    1/12/2001 10:05:52 PM,


    ,



    **********************


    Any help would be greatly appreciated!!!

  • Re: Export Email Text Into Worksheet


    Correction with the previous questions.
    ------
    email sample:


    Actually: My email message looks kind of like the following:


    12455675890,


    00012,


    US,


    ,


    8/25/2001 10:50:52 PM,


    ,



    --------


    I get emails everyday which I put in a separate folder which contains message which looks like the above sample. I like the email messages to be exported to an excel file where each string seperated by comma is put in a different column. Similary the next email will be in the same order but in the 2nd row.


    Right now, I use the manual function (IMport/Export - selecting an excel file to export) which exports all the email message to excel where I manually use the (TEXT to Column) to divide the column and then delete the comma.


    Any help would be greatly appreciated.


    Thanks!

  • Re: Export Email Text Into Worksheet


    Give a try at the below macro. I begin parsing messages counting from row 2 on "Sheet1" sheet, letting place for headings on row 1. Also, if there are two consecutive commas, there will be an empty cell in the corresponding column.

  • Re: Export Email Text Into Worksheet


    Daniel:


    Sorry for the late response. I tried using the macro but i get an error highlighting sheets (Sub/function not defined). Is there anything I should be doing with the excel file as well? For ex: I'll have an excel file named sample.xls and whenever I use the macro .. I'd like the excel file to be updated with the new email messages without deleting any of the old records.


    I hope i'm not making it too complicated.


    :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!