Ideas/hints on automating a manual process using Excel, Outlook and a shared drive

[PaulM100]

Hi,

I am looking to automate a process that should be automated using a specific system, but unfortunately is done manually now, via Outlook.

So, I am trying to track all the tasks that each agent has been working on it. Each case/task comes through a shared Outlook mailbox. Then, the agents will divide the cases between them.

The subject will contain the case details
The email will contain details(I am looking for status especially)
The name of the agent will be in the signature.

My best idea so far is to set up a Excel file that will be kept open for each agent, that will record in a sheet, row 2, the 3 things above(name of the agent, email subject, the work/phrase between "subject: " and char(160)) and save it automatically on a shared network drive, at a time of the day(let's say 4:00PM) with a specific name like "Agent X - Tracker yyyymmdd" and close it. In the next day it will start again.
This files will be used to create a master file at the end of the month.

The agents should not be able to modify the Excel file at all.

Keep in mind that is just an idea for now. So, I am looking on insights, ideas, hints on how I can automate this process in the best way possible.

Many thanks in advance for your time on this,
Paul

[dflak]

This program: https://www.excelforum.com/tips-and-...subfolder.html is designed to read a mailbox, download the attachments to a specific Excel folder and move the email to a processed (read) folder. It can be modified easily to extract the subject and mail body. I'm not so sure about being able to parse the signature. Create a mail rule to move the mails of interest to a specific subfolder directly under the Inbox.

Run this program at a convenient time (4 PM?) and it should parse out the three pieces of information in which you are interested. It then opens (or creates if it does not exist) an excel workbook with the name of the agent to record the information. This workbook resides on the shared folder. No need to keep the file open. Put a password on the file to keep the agents from modifying the workbook (I think you can create code to make the workbook read-only before saving it and undo it on opening).

The program in the link runs from the Excel side of the house. It can be launched from the task scheduler (so you don't have to remember to run it) but the machine running it must be logged in and have a valid, configured Outlook installation.

[PaulM100]

Hi dflak, apologies for my late response.
This is quite a interesting method to start with. Many thanks for it.