+ Reply to Thread
Results 1 to 9 of 9

Copy Text from outlook email to excel workbook

  1. #1
    Registered User
    Join Date
    12-11-2015
    Location
    louisville ky
    MS-Off Ver
    2010
    Posts
    19

    Copy Text from outlook email to excel workbook

    It may not be possible but I get a ton of notification emails that I then have to add to an excel workbook, and the first line of data (or really text within that first line is all that matters) is it possible to have that auto populated? It would obviously end up with duplicates since also sometimes there are middle names sometimes there isn't.

    The email body is text: similar to below

    WORK REQUEST FOR John Q Public(Duty Section: 213186, Crew Number: 001) FROM 04-JAN-19 TO 04-JAN-19 FOR 8 HOURS

    The spreadsheet output is attached and would need to be something I could that would be added to as the emails flowed in or as I edit it.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Copy Text from outlook email to excel workbook

    Is the format of the line exactly the same with the possible exception of a middle name? What I am asking can I determine whether there is a middle name by counting up the spaces?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    12-11-2015
    Location
    louisville ky
    MS-Off Ver
    2010
    Posts
    19

    Re: Copy Text from outlook email to excel workbook

    Yes the only thing that could change is the names

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Copy Text from outlook email to excel workbook

    First of all there are a couple of caveats.

    You need to set up a Main Folder directly under the Inbox for your mailbox. Use filter rules to move the mail to this folder or move them there manually.

    Directly under the Main Folder, create another folder to hold the processed mail. In the example, I called this "Completed." This is the folder to which the mail will be moved after it is read so you don't process the same mail more than once.

    On the control panel sheet, in Cell B1 enter the name of the mailbox. In the example, I am using a shared mailbox called Labor Analysis. For a personal mailbox it would be something like YourName@YourDomain.

    In column I, enter the name of the Main Folder that you created.

    In Column J, enter the name of the Sub Folder where the mail will go after being processed.

    In Cell B3, select the name of the main folder.

    The Control Panel is a bit more complicated than it needs to be, but I modified another piece of code I had that does "batch processing" on a number of different main folders.

    This is all the setup you will need.

    Click the read mail button and all the mail in the main folder will be processed.

    Several words of caution. The code is very "hard coded."

    The line in the body that gets processed is the literal first line in the body. This may or may not be the first line with text. If the literal first line is blank, you'll get bad results.

    The code is expecting EXACTLY the format you provided.

    You may see an error message that highlights this line of code:
    Please Login or Register  to view this content.
    This is not my doing but something that is wrong with Outlook. It happens sometimes when you move messages from folder to folder and then run the utility. The cure is to close the debugger and wait a while for the exchange server to catch up or close Outlook, reopen it and try again.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-11-2015
    Location
    louisville ky
    MS-Off Ver
    2010
    Posts
    19

    Re: Copy Text from outlook email to excel workbook

    so far everything works extremely well, I'm still testing it -- the only problem (that was created by me, by not informing you) is that I do have a few Sr, Jr, or IIIs that immediately follow the last name.

    not sure how easily that could be fixed (or if it would be worth the effort).

    I can't say enough how impressive your skills are

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Copy Text from outlook email to excel workbook

    I think I can accommodate the Sr. Jr. etc. with a couple of extra lines of code. Give me a sample line to take a look at.

  7. #7
    Registered User
    Join Date
    12-11-2015
    Location
    louisville ky
    MS-Off Ver
    2010
    Posts
    19

    Re: Copy Text from outlook email to excel workbook

    WORK REQUEST FOR BILLY L GATON SR(Finance Number: 202346, Pay Location: 006) FROM 31-JAN-19 TO 02-FEB-19 FOR 24 HOURS
    WORK REQUEST FOR JOHN L SMITH III(Finance Number: 202326, Pay Location: 006) FROM 31-JAN-19 TO 02-FEB-19 FOR 8 HOURS
    WORK REQUEST FOR RICK A JONES-SMITH II(Finance Number: 111126, Pay Location: 006) FROM 31-JAN-19 TO 02-FEB-19 FOR 8 HOURS

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Copy Text from outlook email to excel workbook

    Try this version:

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,967

    Re: Copy Text from outlook email to excel workbook

    Try this version.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1