Email Management System in Excel

[Keerthiv]

Hi,

I am handling a customer query team and this is my new assignment. We have an email box where we ask for information from customers or reply with solution and if there is no reply from the customer there is a follow-up mechanism which is 2 business days

We manually download the email daily from outlook to excel and allocate emails to each agent to work on and update the comments with action taken. I am actually struggling to manage the email box because one email will go back and forth. So, there will be multiple lines for one email and getting the history of that email is becoming a tedious (manual) job.

Another challenge is we will not get the proper received dates, body, attachments as it is a manual copy paste from outlook to excel

Could you please assist with a mechanism in excel where multiple actions taken for single email is being captured in one row to avoid searching for multiple lines.

Assigning a tracking number for each email will be great idea as well

Am not sure if this is doable however would like to have your collective thoughts, please

My tracker in excel looks like the attached. If you suggest a different tracker altogether that best fits my requirement, I am more than happy to follow that

Your ideas/suggestions will be really helpful

Please let me know if you require any further information

P.S. - Cross Posting Reference - Email Management System in Excel

Best Regards,
Keerthi

 

[Dan_W]

Thank you for making a point of providing the cross-post reference. That is very helpful, and greatly appreciated.

I can see that TMS has provided a suggestion. In the same vein, I would suggest checking the archive of both this forum and Excel Forum (and other Excel/VBA related forums), as you will doubtless find that others will have asked comparable questions in the past. On this forum, what many don't seem to realise is that system will try and identify five threads that are usually similar (if not identical!) to the question being posed in the post. It's a very useful resource, and an easy one too.

More specifically, you may want to look at the blog of Techniclee - he has provided approx 50 different variations of code that automate the exportation/organisation of emails from Outlook to Excel (link). In terms of your question re: tracking numbers, one option is to leverage Outlook's inbuilt tracking system. All emails in Outlook have (at least) two properties relevant to being able to track them:

1. an EntryID - this is a unique ID - even if there are duplicate emails, they will each have a unique EntryID
2. a ConversationID - this is how Outlook arranges emails in threads. If you have an email chain, all email in that email chain will have an identical ConversationID value.

The only problem with the ConversationIdand the EntryID is that they are 32 characters and 140 characters respectively. I suspect in any event that the ConversationID may be what you're after, so one option would be to create a table used ConversationIDs listing each next to a unique tracking number of your choosing (e.g., CLIENT001, CLIENT002, CLIENT003, and so on).

Beyond that, as TMS commented, I think we would need a clearer idea from you of want you need.