Create one-way sync from Outlook Tasks to Excel Spreadsheet (BlueDevilFan?)

Hi there,

My end goal is to have a task-list in excel which is automatically prioritized (and/or colour-coded) based on the following four criteria:
UACT - Urgency, Authority, Consequence, Time (duration)
Urgent (higher priority) = Due date is within the next five business days
Authority (higher priority) = Task has been assigned by or fulfills promise made to a supervisor(s)
Consequence (higher priority) = Omission of task is not recoverable and would jeopardize outcome achievement
Time (higher priority) = Task can be completed in <1 hr

Urgency is the only criteria that is not fixed (obviously urgency changes on a daily basis as we move closer to deadlines that at one time were more than five days in the future)
Authority, Consequence and Time are all fixed responses that only need to be identified once (A & C are yes/no responses; Time CAN be a yes/no or can be based on a specific duration of time estimated for task completion).

I've attached the excel file that I'm currently working with which does this prioritization for me. I'll use the excel spreadsheet at the start of each day to give me a good idea of what my priorities are for that day/week but will be using Outlook throughout the day as new e-mails/tasks are assigned/updated/completed, etc.

What I would like to have happen, is my Outlook tasks sync with this spreadsheet so that when I open the spreadsheet, it automatically (or at the touch of a button via a macro, if possible) adds any new tasks to the list or marks any completed tasks as completed (but if new tasks in outlook are added and completed before I open the excel sheet, I do not want "completed" tasks to be ADDED to excel -- just mark the ones that are already in excel as completed -- that way, if I delete them, it doesn't re-add them).

Does this make sense?

I have added user-defined fields to outlook to identify Authority, Consequence and Time as TRUE or FALSE (via yes/no set to view as an icon in my task list) so that when these tasks are exported, the data is already there.

I CANNOT add to Outlook the following columns (which I would like to have automatically fill in given the information that comes from Outlook):
"Due in" column which calculates the number of business days left (or overdue) for each task based on today's date: =NETWORKDAYS(TODAY(),[@Deadline])
"UACT Priority" which calculates the number of criteria satisfied (0-4): =SUM(IF([@[Due In:]]<=5,1,0),IF([@Authority]="TRUE",1,0),IF([@Consequence]="TRUE",1,0),IF([@[Time (Duration)]]="TRUE",1,0))

Is there some way to add the Outlook task with the relevant data to this excel sheet that will fill in the other two columns automatically? I would also like to include the Outlook Categories and Assigned-To columns but I wasn't able to export that information to export; I also don't know how to get the user-defined fields to export to excel/csv.

As an additional FYI, I have this list sorting first by the UACT priority (0-4 with 4 at the top) and then by deadline.

Thank you for your help!!
-Shannon
UACT-Prioritization---SHIGSON.xlsx
shannon-hAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
Hi, Shannon.

I think I can help with this.  In order to sync the data between Outlook and Excel I'll need to add a column to the spreadsheet.  That column will store the Outlook EntryID property of each task.  EntryID is a semi-unique identifier assigned to every Outlook item.  I'll use its value to match the items up between the two apps.  The rest is pretty straight-forward.  I will need to understand how you calculate the values for Authority, Consequence, and Time.  They're logical values so I need to know the calculation or  the criteria you're applying to determine True versus False.  You also mention exporting two user-defined fields from Outlook to Excel.  I'll need the names of those user-defined properties in order to handle that.
0
shannon-hAuthor Commented:
Hi BlueDevilFan,

Authority and Consequence will be values that are entered by the user in Outlook -- these are the user-defined fields in Outlook. In Outlook, originally, I set all three (authority, consequence and time) up as "yes/no" fields viewed as icons (check boxes) in my task view.

I believe that a "Yes" is exported to Excel as "TRUE", a no as "FALSE" and if there's no box, it'll just be empty (which will be treated the same as a false given that when I use those values in the "UACT Priority" column, anything that isn't a "TRUE" gets a 0 value) BUT if that's not the case, a "yes" or a "no" would be just fine to work with:
outlook tasks
For Time, however, instead of "yes/no", I would like to use the built-in "total work" field which is where you can identify how long you anticipate a task will take (originally, I wanted this to be as simple as possible to encourage people to actually USE it, but I imagine that if folks don't want to take the time to think through specifically how long a task will take, they can just leave it at 0 hours if it's quick and enter a "2" or something if it's not). This should work well as it exports as a number in minutes (easy for me to work with in excel).

I've re-attached an updated version of the Excel spreadsheet with the columns that I want, in the order that I want them (but this is something that my co-worker can help me to change/fix in VB, should I need to change positioning of columns within the spreadsheet later on).

I think that this answers your questions but please let me know if you need anything else!

THANKYOUTHANKYOUTHANKYOU.

S
UACT-Prioritization---SHIGSON.xlsm
0
David LeeCommented:
Shannon,

I'm going to be unable to work on this until next week.  I'll update you as soon as I have something.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

shannon-hAuthor Commented:
Hi BlueDevilFan,

Just curious as to whether you'll be able to look at this before the end of this week? No pressure!

Thanks,
Shannon
0
David LeeCommented:
Shannon,

I'm going to try to get to it today.
0
David LeeCommented:
Shannon,

First, I apologize for taking so long to get this done and posted.

I've added the code to your spreadsheet.  If I've understood correctly, then it should do what you described above.  I'm not certain about the names you used for the user-defined properties in Outlook, so you may need to edit those.

Please give this a try and let me know if you I got it right.
UACT-Prioritization---SHIGSON.xlsm
0
shannon-hAuthor Commented:
When it opens, I get an error message:
excel-error
(When I click OK, the "Private Sub Workbook_Open" is highlighted in yellow / unrecognized)

EDIT:
I just made a button and inserted the code in the button and it worked BUT I am not seeing anything show up from Outlook (the sheet remains empty).

Thoughts?

Thanks!
0
David LeeCommented:
I made some changes.  Try this version, please.
UACT-Prioritization---SHIGSON.xlsm
0
shannon-hAuthor Commented:
This one definitely works better.

The "Authority" and "Consequence" columns (the user-defined columns in Outlook) are showing up with the Outlook content (hurray!) but there is no Outlook content for the "Total Work" Column (which is a regular Outlook field)

It is also not putting my excel calculations in the "Due In" or "UACT Priority" columns (even though it looks like you've written that part into the code).

Finally, is there some way to avoid pulling completed tasks? (This is not a high priority as I can work with it either way and/or figure that part out).
0
David LeeCommented:
Shannon,

Please try this version.  I've modified the code to simplify the sync.
Shannon3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shannon-hAuthor Commented:
Thank you SO SO SO much! Your help went WAY beyond anything that I could have hoped for. This will be immensely helpful to me and to my team at work.
0
David LeeCommented:
You're welcome, Shannon.  Glad I could help out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.