I’ve been doing a lot of documentation lately, which is brain numbing at best, and time consuming at the same time. So when a boss asked if I’d need more time for a status report I naturally said, “No I should be good…”.
Now I can do that (and look really good) because of a little trade secret that almost no one I’ve met uses… the power of the outlook template. I’ll go one further and say, almost no one uses templates except those of us that seem to be in some hidden fraternity of Program Managers who share these little secrets… like templates.
I keep a template around that does all kinds of fun Project Summary things – and I’m including it here. Here’s a list of things that are built in:
- Has all the people who need to be on the report – already in the "CC” line for me.
- Has a current spreadsheet of the issue items (my more advanced version actually pulls the values from a Issue Tracker – which you can look into doing sometime but this has the drop downs and auto-formating for status.
- Drop downs (see Sheet2 when you double click on the table – you can edit the values there)
So all I have to do – is basically load it, click on the fields I want to update and hit send. Major time saver and everyone thinks you’re like… the coolest most on top of things guy ever.
Let’s cover how this magic happens – first you need to know how to make a template from Outlook. Which… is surprisingly really easy.
All you do – is literally create an email just the way you want it In this case, I’ve put in all the elements that I want in the email I want to send out. I’ve done a couple other things as well so that it will make it easier to customize things later – like special headings.
When I need to swap my status from Green to Yellow and so on all I need to do is select the text and click on the “Format Text” tab and select the style I want.
I also want to have my CC and To fields preset with the names of everyone who will need to receive my status report. The thing to keep in mind is that this is not a “one time” message. It’s going to have certain things in it that I’ll need to do over and over. So I can create all my headings, any graphics that I’ll want to reuse, and any tables that will need to be updated. Now – if I’m going to just update some fields then I’ll probably use the Table feature of 
Outlook and embed a table.
If the table is going to have a lot of data that I’ll be typing in often (like names or email) then I’ll use the table feature to embed an excel spreadsheet, and on the Sheet2 page I’ll create a series of terms and names. Here I’ve put in terms for my Status types, I’ve created a range of values for percentages of completion, email addresses of those who I’ll use in my report. These ranges – I’ll use by going back to the Main Sheet – the one I want to display and use them as Drop Down Lists. The drop down list is a little used feature, but very handy for tricks like this one.
You create it by first creating a range of values somewhere either in the workbook or on the same sheet. You then select 
where you want to create the drop down box, and then on the Data tab (which you’ll note that you now have one in Outlook once you are in the Spreadsheet, just like you do in Excel). 
Clicking on this will open the Data Validation Dialog Box. Once you have this you’ll want to select “List” from the drop down list. This will bring up the options for drop down lists from the dialog box.
In the Source section – you’ll want to put in the range you created over on Sheet2. You can do this either by having named the range, or by putting in the actual range “=Sheet2!$B$3:$B$7” in the 
Source. Either will work, although using a named range is somewhat easier, especially if you have a lot of ranges to deal with. The end result will create a cell that has a drop down list on it. You can copy this cell as you would any other and that makes automation a bit easer.
Another thing you can do with this cell is… use Conditional Formatting Options so that if the values are within a range or if the cell contains certain text – we want it to appear a certain way (background color red – text white) or even contain specific icons or color bars. Really it’s pretty amazing how in-depth you can get with reporting features doing this. And with all of it – you just click on a value, and spreadsheet updates it on the fly. Which leaves you more time for whatever details or other information you need to add and still look like a rock star. 
Now – when you’ve finished creating your email artwork of status reporting – or whatever it is you need to template out… you just… click Save As. The Save As dialog box will appear and you save it as an Outlook Template by selecting that option off the Save As Type drop down.
Now, it will save this by default in your profile. So if you want to save it for sending it for someone else to use, use Save As and select another location that’s easier to find.
If you’ve done all of this properly – you’ll now have a spiffy template all ready to use over and over again, and not have to fill in all those fields and do all that work all over. All you’ll need to do is load the template and use it.
Step Step Two – Load the Template….
To load the template – just click on “New” in Outlook and scroll down to “Choose Form…” which, 
ironically enough will allow you to load a form. Now there are all kinds of forms that you can pick from – and if you’ve never played 
with this do so – you can get really creative with forms.
But for today, we just want the form we’ve created – which is in the “User Templates in File System”. And you locat that by clicking on the drop down box at the top of the dialog, and then picking the “User Templates in File System” category, which if you’ve done 
this right… will bring up all the files you have saved that are templates. If you do it wrong…. it will cause an error and ninja assassins will be dispatched to your home from their secret lair beneath the Google headquarters. (Okay – I made that up… their lair is actually just south of Portland near a winery that they use as a cover.)
Anyway… you want to pick out the form you’ve created. If you’re using the one I have up as a sample 
here the name will be “Project Status and Summary”. So – if that’s what you’re using – click on it so it can load and you can begin being a genius.
Now when the template loads – you’ll want to fill in all the categories and in one of the screen shots above I’ve given you some clues – but you should modify this to your own liking and add the kinds of things YOU need.
The one I’m providing is just a starting point for you – in the words of the beloved Ms. Frizzle, “Get Messy! Make Mistakes! Learn!”. And one of the cool things you can learn how to do – is use embedded excel spreadsheets.
The table on the form – looks like a table until you click on it … so click on it. Because it’s really an excel spreadsheet with fun stuff you can update on the fly. So click away. But you should know that when when you click on it – it’s going to say something like this:
so ignore that and click “Yes” because what’s the point of doing all this… if we just click “No” and end it all now? We know the item that we’re loading and we also know it’s got an excel spreadsheet built into it so we know why they gave us that warning. We WANT to be able to do cool things with it, so there’s no security issue. Which should, give you some idea of the real power of a good template – since you can actually do stuff with them that requires an occasional security warning. 
Now – if you play with this you’re going to note that there are drop down boxes – and there is conditional formatting on those drop down boxes. So when you enter a task name – and click on the boxes to the right of it…. you can change the values without having to type them in every time. Just grab one and click it, move on to the next… wash, rinse, repeat until done.
Here are some examples… of the stuff in them… the first set of drop downs allow you to select from various project status types – 
Completed, In-Progress and so on … and IF you pick one of them – it will automatically reformat it to the color scheme that corresponds to that type of status. The next is the degree of completion – which goes from 0%-100% and you get these very cool traffic light colors when you pick those.
Basically, this allows you to focus on filling in the information instead of filling in all kinds of cool formatting, setting up things every time. As time savers go – this one is definitely a keeper for anyone who has to do reports, news letters or any process that requires you fill in a lot of repeated information, terms, titles and so on.
There’s tons more you can do – this is just the tip of the iceberg, like most of the stuff I do here it’s a starting point for you. You can find out more about – and lay your hands on literally hundreds of great templates you can customize and make your own at Microsoft Office’s Template Library. Free templates for almost every Office Product are available here.
You can … of course also snag the template I used as an example from my Skydrive here:
Direct Link:
http://cid-e46e5f5a0e3b5854.skydrive.live.com/self.aspx/Public/Office%20Templates/Project%20Status%20and%20Summary.zip
Until later… have fun… be productive… and someone find out when the next new Venture Brothers Episode is for me?
