Avatar of shannon-h
shannon-h asked on

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
Visual Basic.NETMicrosoft ExcelOutlook

Avatar of undefined
Last Comment
David Lee

8/22/2022 - Mon
David Lee

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.
ASKER
shannon-h

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
David Lee

Shannon,

I'm going to be unable to work on this until next week.  I'll update you as soon as I have something.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
shannon-h

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
David Lee

Shannon,

I'm going to try to get to it today.
David Lee

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
shannon-h

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!
SOLUTION
David Lee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
shannon-h

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).
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
shannon-h

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
David Lee

You're welcome, Shannon.  Glad I could help out.