Exporting Email Body Text to excel (using outlook 2010) VBA

  • Hi,


    I am wanting to extract some text form the body of emails that come in daily,


    The emails are always in the same format which is:


    Name
    Address 1
    Address 2
    Address 3
    Postcode

    These fields will need to be extracted to an Excel document under the relevant field headers,


    I have done some programming before but not had any experience with VBA,


    Thanks

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    What email program are you using? Duh! You already mentioned that. What version of Excel are you using? Both programs will allow you to import and export data between them. That maybe easier than writing code.

    [SIGPIC][/SIGPIC]Streub


    "Zero to Hero and Back in 6 Seconds."

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Hi, first of all many thanks for your reply, i am using excel 2010, ideally this will need to be done by a script that is run when the email is recieved but how do i export data between them?

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    If you're feeling adventurous, you can use the following in Outlook (you will need to set the security level first and then enable macros each time Outlook starts - but 2010 might have some improvements over 2007 in that respect)...



    Paste that into the ThisOutLook session module in Outlook - you will need to restart Outlook afterwards.


    Then, when a mail is received, Outlook will start an instance of Excel, load a workbook (hardcoded at the moment) and write some details from the email to the workbook. Some general details only in the sample - you'll need to code for that as you did not give precise details as to the structure in your post.


    That code requires more detailed error handling to be added, especially considering the Excel workbook may be in use.


    It can also be easily 'turned around' to run from Excel. There are pros and cons for each approach, I just took the simpler approach.

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Hi Cytop,


    First of all many thanks for your reply. i have been away on holiday so only just got chance to look at this.


    The full details of what i require are:


    Emails will always be moved by Outlook into a folder named British Gas


    the text i require to be outputted will always be in the same format:


    There will be 4 lines of text to be exported.

    Name
    Address 1
    Address 2
    Postcode

    these will ideally need to be exported to a .CSV file with comma seperated fields so that our printer can run them through label matrix


    Again many thanks for your help

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Quote from cytop;682270

    If there are only those 4 lines of text, and all the text is to be exported, then fine. If there's other text, you need to upload a sample of the email body.


    Hi cytop,


    Yes there are only those 4 lines of text to be exported. no other text in the emails at all.

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    That makes it simpler - No Excel at all.



    Same comments as before regarding where the code goes.


    I have made an assumption that the BG folder is a sub-folder of the normal INBOX folder. If you don't specify I can only assume. Also, you need to change the export file name to suit.


    Again, as it was unspecified, I'm assuming there are no commas in the address... If that can't be guaranteed then you need to edit as follows:

    Code
    1. '// ORIGINAL
    2. Print #iFile, Replace(Msg.Body, vbCrLf, ",")
    3. '// REPLACEMENT
    4. Print #iFile, Replace(Replace(Msg.Body, ",", vbNullString), vbCrLf, ",")


    The

    Code
    1. DEBUG.ASSERT False

    lines were used to give this a quick test. Remove them when you're happy it works as expected.

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Many Thanks cytop,


    I have added the code into the ThisOutLook session module and saved it.


    I have renamed and created the appropriate CSV file in the correct directory,


    so i take it this script will run when an email is recieved in the british gas subfolder?

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Should do - why don't you try it?


    Create a draft message. Save it and then drag from the Drafts folder to the BG folder.


    Don't forget you have to close Outlook after editing the code and restart it. You will be prompted to enable macros, same as in Excel.

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Quote from cytop;682280

    Should do - why don't you try it?


    Create a draft message. Save it and then drag from the Drafts folder to the BG folder.


    Don't forget you have to close Outlook after editing the code and restart it. You will be prompted to enable macros, same as in Excel.


    I can confirm that the text is outputting to a CSV file ok.


    the only thing i have noticed is that every time an email is received the original data exported is overwritten and not put on a seperate line and saved to the same CSV.


    Is there any way to do it so that:


    Name,Address 1, Address 2, Postcode
    Name,Address 1, Address 2, Postcode
    Name,Address 1, Address 2, Postcode
    Name,Address 1, Address 2, Postcode



    Thanks again for all your help

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Mea Culpa - that's what I get for typing code freehand and only testing with one message.


    Change

    Code
    1. Open "C:\Temp\OUTLOOK_EXPORT.CSV" For Output As #iFile

    to

    Code
    1. Open "C:\Temp\OUTLOOK_EXPORT.CSV" For Append As #iFile
  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Thanks,


    This now works and data is all stacking up nicely :)


    From looking through the data i have noticed that sometimes there are 5 lines of text instead of 4 so for example:


    Name, Address 1, Address 2, Address 3, Postcode
    Name, Address 1, Address 2, Postcode
    Name, Address 1, Address 2, Address 3, Postcode
    Name, Address 1, Address 2, Postcode


    As the data will always sit under the headings i need to tell the code to parse address 3 if it is present otherwise print a blank space and still put postcode in the 5th box

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Sorry disregard my last as it will just put the postcode were address line 3 would be :)


    All should work fine and i shall run a test with our printer tomorrow.


    Thanks

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Many thanks for the offer but i do not believe this is needed at this stage. Also each email has an attachment.


    is there anyway to tell Outlook to auto-download the attachment when the email is received and place it in a corresponding folder?

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    You need to check the items.attachments.count property (just to make sure there is one or more) then use the item.attachments.saveas (I think) method to save it, or them.


    Still on the phone so if you want to try then go ahead. If it's still an issue later, I'll post back this evening.

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Still a lot of assumptions...

  • Re: Exporting Email Body Text to excel (using outlook 2010) VBA


    Hi cytop,


    Is the above code to be pasted in the same Thisoutlooksession?, above or below the existing sub for the data export?


    the attachments will all go into one folder. and list by date modified so that the data exported and attachments will match up to each other in order


    Thanks