Copy Text from outlook email to excel workbook

[terence17]

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.

[dflak]

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?

[terence17]

Yes the only thing that could change is the names

[dflak]

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.

[terence17]

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

[dflak]

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.

[terence17]

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

[dflak]

Try this version:

[dflak]

Try this version.