Friday, 5 July 2002

Newsletter Issue 47, July 2002

Sam Young Newsletter

Issue 47, July 2002
Hi guys,
Here is the second in our series of MS Outlook tip sessions. This time we look at Moving Files from Outlook to the File System below.
For those of you using Office XP (2002 to the uninitiated) who have tried setting up the Paste Special macro to find it didn't work, check out PasteSpecial for Office XP
Don't forget, if you want to be taken off my mailing list, click here to send me a reply e-mail and I will remove your name.

Moving Files from Outlook to the File System

Last time we started to prepare the ground for using Outlook at a tool for CRM, and I talked briefly about securing your Outlook Information.
There is a Microsoft Outlook add on package called pfbackup.exe which is available from as a free download. This works with both Outlook 2000 and XP (there is a 97 version that I have been sent, and if any of you would like it, please drop me a line).
What this package does is to create backup copies of your .PST files at regular intervals, making it easy to keep all of your Outlook folders safely backed up. However, it still leaves all your information locked in one single file. So while I am now doing this, I am also pulling my files out individually as well (I am a "suspenders & belt" person when it comes to my valuable data!).
NB: If you are on a large network, you will probably have a Microsoft Exchange Server mailbox. In that case you don't have a .pst file to back up anyway and your server mailbox folders will be backed up regularly by your server administrator.
I pull all my important information out of Outlook twice each week by using Outlook's "Advanced Find" engine to filter out the information I need, then dragging the files across to the file system. 
So, here's how we do it. We will first look at Contacts (NB: same principles hold true for Calendar and Task items);
  1. Create a folder under My Documents called eg "Outlook Backup". Underneath that folder, create three folders called "My Contacts", "My Calendar" and "My Tasks"
  2. Open Outlook. Right click over the Outlook Shortcut bar, and select "Outlook Bar Shortcut"
  3. A dialogue box will pop up. In the Look In field, select "File System". Then navigate to the folder called "My Contacts"... eg My Documents/Outlook Backup/My Contacts. Click OK. A shortcut to "My Contacts" will now be on your Outlook Shortcut bar (you can move it up or down by click, hold and drag on the shortcut bar list)
  4. Click the Contacts icon on the Outlook Shortcut bar to see all your contacts 
  5. Click on the first contact in the folder, then hold the Shift & Ctrl keys down, and while holding them down, take the slide at the bottom of the page right over to the left with the mouse. Then mouse click on the last file in your Outlook Contacts folder to select all the files
  6. Click, hold and drag all these files to the My Contacts shortcut on the Outlook Shortcut bar. All the files will copy (and this will take a while to copy all the contacts across for the first time. Outlook may appear to lock up. Be patient)
  7. Once the files have copied, go in to the My Contacts folder (either from the Outlook Shortcut bar or through Explorer). You will see a pile of .msg files. These are all your Outlook Contacts.
After you have copied all the files across the first time, you only need to copy across any files that have been updated each week. To do this, we need to use Outlook's Advanced Find tool;
  1. Right click over the Contacts header bar and select Advanced Find from the menu
  2. Click on the Advanced tab. Under Define More Criteria, click the "Field" button and select "All Contact fields". Select "Modified". In the Condition field, select "In the last 7 days". Click the "Add to list" button
  3. Click the "Find Now" button. The search will run
  4. Highlight all the files in the results window and drag them to the My Contacts shortcut on the Outlook Shortcut bar
Before you close the Advanced Find window, click the File Menu, and select "Save Search". We are saving this search so that we can run it each week without having to recreate it each time. Name your search "Contacts Advanced.oss" and save it to the My Contacts folder.
  • To create an Advanced Find for Tasks & Calendar, you need to change the file type in the "Look for" field to "Tasks" or "Appointments"
  • If you save all the Task & Calendar searches into My Contacts folder too then you can run them one after the other without having to change folders on the Outlook bar to first locate your oss file before being able to run them.
You can also filter the Advanced Find by adding multiple criteria. I do a task search that finds all incomplete & recurring tasks with three criteria;
  • Field: Modified, Condition: In the last 7 days
  • Field: Created, Condition: In the last 7 days
  • Field: Recurring, Condition: equals, Value: Yes
Have fun with it all. Now that we have our information backed up, next time we are looking at setting up Outlook AutoReplies to incoming messages.

PasteSpecial for Office XP

OK. Remember in the last newsletter we looked at a PasteSpecial macro for Office 2000 & 97? Well that macro won't work for Office 2002 (or XP, as it is more commonly known). So here is the deal for this time;
  1. Start Word (if Word is open, close Word down & restart)
  2. Go to the Tools menu. Select Macro | Macros. This bring up the Visual Basic for Applications dialogue box
  3. Type a good macro name in the box - say, PasteSpecial - and click Create (remember, NO SPACES in a macro name. Use an underscore if you really want a gap)
  4. Unless your "normal" template is locked away, VBA comes to life, and writes a little program for you that looks something like this: 
Sub PasteSpecial() 
' PasteSpecial Macro 
' Macro created 10/18/2001 by Sam Young 
' End Sub 
  1. Immediately before the "End Sub" line, you need to key one VBA code line "Selection.PasteSpecial DataType:=wdPasteText" and then hard return (enter). When you're done, the macro should look like this
Sub PasteSpecial() 
' PasteSpecial Macro 
' Macro created 10/18/2001 by Sam Young
' Selection.PasteSpecial DataType:=wdPasteText 
End Sub 
That's the programming part of the exercise complete (pretty difficult, eh?!)
  1. In VBA, click File | Close and return to Word
  2. To tell Word to run your brand new macro every time you press Ctrl+Space, go to the Tools menu and select Customise;
    • Make sure that shows in the "Save in" field
    • Click the Keyboard button
    • In the Categories box, pick Macros
    • In the Macros box, pick PasteSpecial
    • Click inside the Press New Shortcut Key box and hit Ctrl+Space
    • Click Assign
You're done. From now on, every time you press Ctrl+Space, Word will paste the contents of the clipboard, and ignore any formatting.

Useful Excel Date Formula

If you are like me and have to work out with a pen & paper what day of the week your next birthday is, you may find this Excel formula pretty useful.
Weekday function
  • Select your cell
  • Key =WEEKDAY("mm/dd/yyyy")
  • Replace "mm/dd/yyyy" with the date you want to check
  • Key [Enter]
Excel will then display a value between 1 and 7, inclusive. The number represents the day of the week for the date you entered. Days are numbered 1 for Sunday, 2 for Monday, 3 for Tuesday, and so on. To know the day of the week that New Year 2003 will fall on, enter =WEEKDAY("1/1/2003"). 
But what if you want to calculate how many days there are between today and a future date? You can let Excel calculate the number of days between the two by subtracting the earlier date from the later date;
  • Key the future date into a cell (eg, create a new sheet and type the future date into A1
  • In another cell, type =TODAY() (eg B1)
  • In another cell, enter the expression =Cell location 1 minus cell location 2 (eg in C1, enter "A1-B1")
The result is the number of days between the current date and the future date you entered
NB: If Excel formats the result as a date, select C1 and go to the Format menu | Cells | Number and choose "General" or "Number" format

TLAs for SMEs

Here are this newsletter's TLAs for you;
  • WYSIWYG, What You See Is What You Get. This is the Windows rendering system for graphics which is SUPPOSED to mean that what you see on the screen is what prints out. Hah!
  • BASIC, Beginner's All-purpose Symbolic Instruction Code. Developed by John Kemeney and Thomas Kurtz in 1963 at Dartmouth College, USA, BASIC is one of the earliest and simplest high-level programming languages

Please feel free to email me with any TLAs that you want to get the bottom (meaning!) of.

Short+Hot Keys... and now tips
All the Function keys for you - this time it's all you can do with Alt, Ctrl & F11;
  • Access "To switch between the Visual Basic Editor and the previous active window" ALT & F11 
  • Access "To switch between the Microsoft Script Editor and the previous active window" ALT & SHIFT & F11 
  • Excel "Display the Visual Basic Editor " ALT & F11 
  • Excel "Create a chart that uses the current range " F11 or ALT & F1 
  • Excel "Insert a new worksheet " SHIFT & F11 or ALT & SHIFT & F1 
  • FrontPage "Display the Microsoft Script Editor" SHIFT & ALT & F11 
  • PowerPoint "Display Visual Basic code" ALT & F11 
  • Word "VBCode" ALT & F11 
  • Word "Microsoft Script Editor" ALT & SHIFT & F11
  • Access "To toggle between a custom menu bar and a built-in menu bar" CTRL & F11 
  • Excel "Insert a Microsoft Excel 4.0 macro sheet " CTRL & F11 
  • Word "Lock Fields" CTRL & F11 
  • Word "Unlock Fields" CTRL & SHIFT & F11
Hot Linx
Want to eat without wheat? It's harder than you think, but there are a couple of webistes out there with wheatless recipes at, and 
Want to check your flights online? Armed only with your surname and reservation code number, go & check out the appropriate site for your booking;
Are Heracles and Hercules are the same person? Who were Zeus' lovers? Don't know? Well, here's your chance to check at 

                                Catch you again soon!! E-mail your suggestions to me here