The Microsoft Outlook Ideas Book - Chapter 4: Whole Solutions

Introduction

So far, we have explored the unconventional uses of Outlook's individual components, and we have utilized the business example of "Our Company" to demonstrate:

• Staff records: Recording and evaluating staff leave.
• A vehicle pool: Recording and monitoring vehicle details, servicing, etc., and linking to users.
• Suppliers: Organizing their records into an invaluable business directory.
• Taxi booking records that could be applied to other services.

In this way, we have been developing an Outlook solution for Our Company, but this solution is not quite complete, there is more that we can do for Our Company.

In this chapter, we will complete the solution for Our Company, integrating various Outlook and Office components, and will also explore another 'whole solution', a school‑based example that we haven't yet introduced.

Our Company Solution

We will create a scenario involving both the Outlook and Office components.

Meeting Room Management

Meeting room bookings are commonly managed using a separate calendar, and these reservations may be for external organizations as well as for internal groups. The calendar in which these bookings are recorded will resemble an appointments calendar, except that the items will refer to the rooms in which the meetings are being held rather than the meetings themselves. When more than one meeting is taking place in different rooms and at the same time, concurrent entries are inevitable and acceptable. A simplified and small portion of such a Meeting Rooms Calendar might look something like the following:

This view provides a clear image of what is happening in the meeting rooms over a short period of time. However, a schedule of meeting room activity over a greater period of time and with more detail about who booked the room, the revenue due from external bookings, etc., would be very useful for analysis and planning purposes.

Outlook can produce such a view of a Meeting Rooms Calendar that will:

• Display a schedule of all current and future room booking details.
• Distinguish between internal and external reservations.
• Automatically calculate the cost to the external hirer.

Before we begin to design this view of the Meeting Rooms Calendar, there should be in place the Contacts records for hirers of the rooms, and whether they are colleagues in the same company or customers in external organizations. We also need Contacts records for each of the meeting rooms, and a Contacts folder named Meeting Rooms in which to store them. Although Outlook Contacts folders are normally associated with storing the details of people, there is no reason why Contacts folders cannot hold the records of inanimate objects. By creating and manipulating Contacts records for items, Outlook truly becomes a supreme information manager.

Before building the set of meeting room contacts, we first need to create the new Contacts folder. This is done in the usual way by right-clicking in the Outlook folder list and choosing New Folder and naming the folder Meeting Rooms.

Creating Contacts records for hirers needs no special instruction, and the only field that needs to be completed on the Contact form for the rooms is the Full Name field; the details of the rooms will be recorded in the view and not in the Contact record.

We now have individual Contacts for each of the meeting rooms and Contacts for the hirers, all of which we can link to the bookings in the Meeting Rooms Calendar. We can now create the view of the Meeting Rooms Contacts folder that will display the details of the rooms.

Creating a Meeting Rooms View

1. Create a new table-type view from the Define Views | New option, and name the view Meeting Rooms.
2. In the View Summary dialog box, click Fields and select the field Full Name, and click OK.
3. We will create the following manual fields:

   Name	Type	Format	Purpose
   Capacity	Number	Raw	To enter the number of people the room can accommodate
   Rate per hour	Currency	Two decimal places	To record the cost per hour to hire each room
   Facilities	Text	Text	To enter the equipment etc. available in each room

4. Rename the Full Name field to Meeting Room, from the Format Columns dialog box.

All four fields require you to enter manually the details about the rooms, i.e., the name or number of the room, how many people it can accommodate, the facilities that it has, e.g., projector, flip charts, whiteboard, teleconference facilities, etc., and the hourly rate for external hire.

The Result

The following screenshot shows the Meeting Rooms view:

Creating the Meeting Room Calendar View

The steps to create this view begin with linking the bookings to the Contacts forms of the internal or external hirers and to the Contacts forms of the rooms. This will provide a bookings history by room, and by person or company.

This view can also be adapted to display reservations by room, by date, by occupant, or by internal or external bookings, just by changing the way that the items are grouped. Finally, we will see how the calendar appointment form can be redesigned to integrate with Microsoft Word to print an invoice for the room hire, and how Outlook can produce a monthly statement for the hirer.

1. Create two new Categories for the room bookings, named Internal and External.
2. Enter room bookings into the Calendar in the normal Day/Week/Month view, and assign either the Internal or External category depending on whether it is an internal or external hirer.
3. Create a new table-type view from the Define Views | New option, and name the view Meeting room calendar.
4. In the View Summary dialog box, click Fields, and select the fields: Location, Subject, Contacts, Start, End, and Duration, and click OK.
5. We will create three new manual fields that will be used to build an Address field, and these fields will also be used later in the development of the form and the printing of the invoice:

   Name	Type	Format	Purpose
   Add Line 1	Text	Text	To enter the first address line of the hirer
   Add Line 2	Text	Text	To enter the second address line of the hirer
   Add Line 3	Text	Text	To enter the third address line of the hirer

    

   Name	Type	In the Formula Field window
   Start Time	Formula	Format([Start],"h:mm")
   Room Charge	Formula	IIf([Location]="Room 1",Format(10,"Currency"),IIf([Location]="Room 2",Format(30,"Currency"),IIf([Location]="Room 3",Format(15,"Currency"),"Rate?")))
   Total Fee	Formula	IIf([Categories]="External",Format([Room Chrge]*[Duration]/60,"Currency"),IIf([Categories]= "Internal","Internal"))
   Address	Combination	[Add Line 1] [Add Line 2] [Add Line 3]

6. To restrict the bookings in the view to current and future, add a filtering criterion. From the Filter | Advanced tab, create the following filter:

   Field	Condition	Value
   Start	On or after	today

7. To distinguish internal from external room bookings, apply the following Automatic Formatting rule:

   Formatting Rule Name	Field	Condition	Value	Font Format
   Internal	Categories	Is (exactly)	Internal	Blue

8. Exit the View Summary, and from the Format Columns dialog box, modify the following fields as shown:

   Field name	New name	Format
   Subject	Booking For:
   Start	Date	Date only
   End	End	Time only

What Just Happened?

We have created the basic view as described, and the fields will operate as follows:

• The Start Time field repeats the Start field, but formats the contents to show only the hours and minutes, ("h:mm"). Outlook does not allow you to place a field in the header area more than once, but you achieve this by placing the field that you wish to repeat within a custom field.
• The Room Charge field will automatically return the room fee depending on the room number entered in the Location field. The Format function ensures that the result is formatted as currency; so £10.00 for Room 1, £30.00 for Room 2, and £15.00 for Room 3. If no room number is entered in the Location field, this field will return "Rate?", indicating that a rate cannot be determined unless a room number is entered.
• The formula in the Total Fee field is dependent upon the creation of the two new categories, Internal and External, and meeting room bookings in the calendar being assigned accordingly. The formula says that if the item is an external room booking (i.e., the calendar item has been assigned the External category), the charge should be calculated as the Room Charge multiplied by the Duration, divided by 60. It is necessary to divide the Duration by 60 because the Duration field actually holds time values in minutes even though it may display it in hours. This part of the calculation is enclosed with the Format(………….."Currency") function so that the result will be in currency format. If the calendar item is assigned to the Internal category, the field returns the word Internal. This assumes that no charges are levied for internal bookings.
• The three manual address line fields have been concatenated into the single Address field to save space in the view, and can be removed from the view by dragging them off the field header bar. However, three separate address line fields are required so that they can appear on separate lines in the merge to the Word invoice template described later.

The Result

With all the fields in place, you can enter bookings into the Meeting Rooms Calendar in the usual Day/Week/Month view and then switch to the Meeting room calendar view to obtain a condensed view of all current and future meeting room bookings. The external reservations have the charge already calculated, and the internal bookings are in blue font with no charge.

This view does not group the bookings in any way, but other views can be created based upon this view that show the bookings grouped in various ways, or grouping can be achieved by dragging the appropriate field to the Group By area. Useful booking schedules can then be printed.

The following screenshots display groupings with respect to Location, Booking For, Date, and Categories, respectively:

Invoicing Meeting Room Bookings

We are now going to add a page to the appointments form (meeting room booking) in the Meeting Rooms Calendar folder. The added page will contain extra details of the room booking, and a Print button that will run code to activate an invoice template. Fields from the Outlook calendar item will merge with the Word invoice template to produce an invoice for the room hire. The invoice can then be saved and printed.

To produce the Word template, we will require a basic template for an invoice and the insertion in the appropriate places of a Word text form field for each of the Outlook fields.

Text form fields are inserted into a Word document by clicking the Text Form Field button on the Forms toolbar in Word. By right-clicking on each inserted text form field and choosing Properties, you can make a note of the Bookmark name (Text1, Text2, etc.) of each field for use in the following code and set the formatting of the data that will appear in the field. The formatting for the fields in this example is detailed further on in this text.

Once we have added the extra page to the meeting room booking form, the form will have an extra tab named Print, and the Print page of the Appointment Form will look something like the screenshot shown here:

Creating the Appointment Form

1. Open a new appointment form in the Meeting Rooms Calendar folder, and access the design mode from Tools | Forms | Design This Form.
2. Click the tab (P.2), and first make it visible from Form | Display This Page, then rename it to Print from Form | Rename Page.
3. From the Field Chooser dialog box, drag the Duration field onto the form and, under User-defined fields in folder, drag and place on the form the five fields we created: Room Charge, Total Fee, Add Line 1, Add Line 2, and Add Line 3.
4. Create the following new field:

   Name	In the Formula Field window
   Meeting Duration	[Duration]/60

   Do not drag this field on to the form. We are using the Outlook Duration field on the form because it will display the time span in hours. We cannot use the Outlook Duration field for the Word template because it will revert to its underlying format of minutes. The Meeting Duration field we have just created will remain as a 'user-defined field in this folder', and will be picked up by the following code and used in the eighth bookmark. This field will be used just for the merge into the Word template, and will ensure that the duration of the meeting will be shown as hours in the final invoice.

5. From the Control Toolbox, drag a Command Button onto the form.
6. Right-click on the CommandButton and change the caption to read Print Invoice.
7. To add the print code behind the CommandButton, click the View Code button on the Form Design toolbar, , and add the following code in the Script Editor:
   VB

   Sub cmdPrint_Click()
      Set oWordApp = CreateObject("Word.Application")
      If oWordApp Is Nothing Then
         MsgBox "Couldn't start Word."
      Else
         Dim oWordApp
         Dim oWordDoc
         Dim bolPrintBackground
   
         ' Open new document
          Set oDoc = oWordApp.Documents.Add("C:\Documents and " & _ 
                     "Settings\UserName\Application Data\" & _ 
                     "Microsoft\Templates\Room Hire Invoice.dot")
   
         ' Set 1st bookmark to contact's Name
         oDoc.FormFields("Text1").Result = CStr(Item.Subject)
   
         ' Set 2nd bookmark to Address Line 1
         strMyField = Item.UserProperties.Find("Add Line 1")
         oDoc.FormFields("Text2").Result = strMyField
   
         ' Set 3rd bookmark to Address Line 2
         strMyField1 = Item.UserProperties.Find("Add Line 2")
         oDoc.FormFields("Text3").Result = strMyField1
   
         ' Set 4th bookmark to Address Line 3
         strMyField2 = Item.UserProperties.Find("Add Line 3")
         oDoc.FormFields("Text4").Result = strMyField2
   
         ' Set 5th bookmark to the Room
         oDoc.FormFields("Text5").Result = CStr(Item.Location)
   
         ' Set 6th bookmark to the booking's start date & time
         oDoc.FormFields("Text6").Result = CStr(Item.Start)
   
         ' Set 7th bookmark to the booking's end date & time
         oDoc.FormFields("Text7").Result = CStr(Item.End)
   
         ' Set 8th bookmark to the booking duration
         strMyField3 = Item.UserProperties.Find("Meeting Duration")
         oDoc.FormFields("Text8").Result = strMyField3
   
         ' Set 9th bookmark to the Room Rate per hour
         strMyField4 = Item.UserProperties.Find("Room Chrge")
         oDoc.FormFields("Text9").Result = strMyField4
   
         ' Set 10th bookmark to the Total Charge
   
         strMyField5 = Item.UserProperties.Find("Total fee")
         oDoc.FormFields("Text10").Result = strMyField5
   
         ' Set 11th bookmark to the Amount Due
         strMyField5 = Item.UserProperties.Find("Total fee")
         oDoc.FormFields("Text11").Result = strMyField5
   
         ' Set 12th bookmark to the Invoice Date
         oDoc.FormFields("Text12").Result = CStr(Item.End)
   
         oWordApp.Visible=True
          
         Set oDoc = Nothing
         Set oWordApp = Nothing
      End If
   End Sub

8. Close the Script Editor, open the Tools menu in the form, still in Design mode, and select Forms | Publish Form As, and name and publish the form to the Meeting Rooms Calendar folder.
9. In the General tab of the Properties of the Meeting Rooms Calendar folder, open the When posting to this folder, use: drop-down box, and select the Meeting Room form. This will tell Outlook to use this form when creating new items in this folder.
10. You will need to alter the tenth line of the previous code to the path of the Word invoice template on your system and print the invoice on the company headed paper.

What Just Happened?

The shaded fields in the Word template are all the Form Text Fields that were inserted into the invoice template, and these have been assigned the Word bookmark names, e.g., Text1 etc. The code behind the Outlook form is locating those bookmarks, e.g., oDoc.FormFields("Text1"), and is inserting the Outlook fields, e.g., CStr(Item.Subject).

For the custom, user-defined fields, the following syntax is used to find those fields and to transfer the field contents (e.g., Add Line 1) to the Word bookmarks (e.g., Text2) on the form:

strMyField = Item.UserProperties.Find("Add Line 1")
oDoc.FormFields("Text2").Result = strMyField

You will notice that the End Date field [CStr(Item.End)] has been used twice in the code, first as the date of the invoice (formatted as date only) and second as the end date for the room booking (formatted as date and time).

The remaining formats of the Word text form's fields are as follows:

The Result

The Word form template that we have created, when printed on the letter headed paper of our fictitious company "Our Company Ltd.", would produce the invoice as shown below:

Meeting Room Maintenance

We can also include in this solution the means to track the repair and updating of meeting room facilities, by creating Tasks for maintenance items.

For example, a task can be created for an air conditioning fault that is reported for meeting Room 1.

To enable updating by the maintenance crew, the task is created in the default Tasks folder, and it is also linked via the Contacts button to Room 1 in the Meeting Rooms Contacts folder.

The Meeting Rooms Contacts folder is linked to the default Tasks folder so that the Activities tab of the Room 1 contact record will display a history of maintenance items for that room.

We can now monitor, for example, how many times the air conditioning in Room 1 has been repaired, and the Owner field will tell us who is responsible for this. We can track the progress of maintenance items and determine if the room is ready to be used.

Using the Meeting Room Solution

Open a new form in the Meeting Rooms Calendar. This will be a blank, custom meeting room form that we created specifically for the meeting room calendar. Complete the fields on the Appointment page, and on the Print page of the form; if it is an external booking, manually enter the postal address of the client in the Add Line fields 1, 2, and 3. If it is an internal booking, complete just the first address line with the name of the department in your company that is booking the room. Outlook will pick up the dates from the dates of the booking, will calculate the cost of the room hire, and display the details in the corresponding fields on the Print page.

For all this to work properly, it is important that you remember to:

1. Assign either the Internal or External category.
2. Link the booking to the relevant room in the Meetings Rooms' Contacts folder and the contacts record of either the company that is hiring the room (external booking) or, for an internal booking, the relevant person in your company.

By linking the room booking to the external client company or to the internal staff member, double-clicking on the link within the calendar item will give you access to the details of the hirer should you need to contact them about the booking.

You will be able to view the full bookings record and income history for individual rooms, from the Activities tab of the room contact record:

The following screenshot shows the external bookings history of the hirer:

The following screenshot shows the internal bookings history of the hirer:

Invoicing Room Bookings

To invoice an external room booking, open the booking appointment in the Meeting Rooms Calendar, click the Print tab, and click the Print Invoice button. All the details of the booking will be transferred to the Word invoice template, and the invoice document will open with all the fields complete. You are then able to print and save the invoice like any other Word document.

Unfortunately, Outlook does not allow you to print a list or schedule of the items appearing on the Activities tab of a contacts record to create a statement to present to clients.

However, you can create individual views of the Meeting Rooms' Calendar folder that will filter by client and by current month, to produce a statement of room bookings per client.

For example, the Underwood Machinery company has made several meeting room bookings as the Activities tab of its contact record shows in the screenshot, and they will have been invoiced for each separate booking.

If, at the end of August, we wanted to send the client a statement of all the bookings and invoices for August, we would need to create the following view of the Meeting room calendar that filtered meetings for the Underwood Machinery company in August.

Creating the Calendar View for Bookings and Invoices

1. From the Define Views | Copy option, copy the Meeting room calendar view, and name the copy view (in this instance) Underwood Mach Statement.
2. From the Filter | Appointments and Meetings tab, create the first filter:

   Search for the word(s)	in
   Underwood	subject field only

3. From the Filter | Advanced tab, create the second filter:

   Field	Condition	Value
   Start	Between	1/8/05 and 31/8/05

4. Set up the Print Preview of this view with a suitable header, and print the statement on the company headed paper.

What Just Happened?

We have worked through a way of using Outlook to manage meeting rooms and their bookings. This method could be applied to other forms of room hire, for example, booking rooms in a small hotel or guesthouse; the rate would be per day and not per hour, and there would be other charges to be included, but the principle would be the same.

The Result

The following is the Print Preview of the Statement of Bookings done in the month of August 2005:

Sales

We can also create a method of managing email orders for Our Company that will automatically group and sort the orders and provide a quick and easy start to the order process.

Our Company manufactures various types of nuts: Topnuts, Hipnuts, Bobnuts, and Dropnuts, and the company has instructed its customers to send orders for the various types of nuts by email, stipulating that the email orders must mention the word Order in the subject line and then the variety of nuts being ordered, e.g., Order: Topnut. The body of the email can then go into greater details about the order, including the number of items required. The emails are sent to the Order Clerk at Our Company.

The company has a team of salespeople who are responsible for specific customers, and they earn commission and bonuses annually on the total values of the orders placed.

The email orders are received in the Order Clerk's Inbox, and he/she has created a mail folder specifically for email orders and a custom view of the folder that presents the orders as shown in the next image:

Before constructing this folder and view, the following should be created:

1. Categories for each salesperson (the category being the name of the salesperson).
2. InBox rules for the incoming email orders for each customer. The rules will move emails from specified customers that have Order in the subject line to the Orders folder. The rules will also forward the emails to the appropriate account salesperson, and assign the emails to the salesperson's category. For example:

   Apply this rule after the message arrives:

   From Betterfit & Sons 
   and with Order in the subject 
   Assign it to the Davolio Nancy category
   and forward it to Davolio Nancy
   and move it to the Orders folder

Creating the Orders Mail Folder

1. Create a new Mail folder named Orders.
2. Create a new table-type view from the Define Views | New option, and name the view By Customer.
3. In the View Summary dialog box, click Fields, and select the fields: Received, Subject, and Categories, and click OK.
4. We now create the following two new manual fields:

   Name	Type	Format	Purpose
   Quantity	Number	Raw	To enter manually the quantity of nuts being ordered
   Cost B/Down	Currency	Two digits	To bring down manually the previous Final Cost value to create a running sum.

5. And the following five formula fields:

   Name	In the Formula Field window
   Price	Format(IIf([Subject]="Order: Bobnut",0.25,IIf([Subject]="Order: Dropnut",0.6,IIf([Subject]="Order: Hipnut",0.75,IIf([Subject]="Order: Topnut",1, " ")))),"currency")
   Cost	Format(([Price]*[Quantity]),"currency")
   Discount	IIf([Cost]>5000,Format(([Cost]*0.05),"currency"),"0")
   Final Cost	Format([Cost]-[Discount],"currency")
   VAT	Format(([Final Cost]*0.175),"currency")
   Cost inc. VAT	Format([Final Cost]*1.175,"currency")
   Running Sum per ¼	Format([Cost B/Down]+[Final Cost],"currency")

6. Set Group By to From, Then by to Categories.
7. Set Sort items by to Received.
8. In Other Settings, activate the Show Preview Pane (Preview all items in Outlook 2003).
9. From the Filter | Advanced tab, create the following filter:

   Field	Condition	Value
   Received	Between	1/1/04 and 31/12/04

   Separate views can be created to filter different time spans, e.g., the current year for daily active use or, as in this case, a previous year to view a full year's sales.

10. From the Automatic Formatting option, click Add, and create the following formatting rules:

    Formatting Rule Name	Field	Condition	Value	Font Format
    First ¼	Received	Between	1/1/04 and 31/3/04	Green
    Second ¼	Received	Between	1/4/04 and 30/6/04	Blue
    Third ¼	Received	Between	1/7/04 and 30/9/04	Purple
    Fourth ¼	Received	Between	1/10/04 and 31/12/04	Olive

11. Exit the View Summary, and from the Format Columns dialog box, rename the following field as shown:

    Field name	New name
    Categories	Salesperson

12. Remove from the view the fields From and Salesperson (Categories), by dragging them from the field header area.
13. Create a link between the Contacts folder that contains the customers' contact records to the Orders mail folder.
14. Create a Contacts folder for each year of sales, for example in this case, Sales 2004.
15. Create or copy the contact records for the salespeople into these folders.
16. Create a new view for these Contacts folders that can be used on all Contacts folders, from the Define Views | New option, and name the view Sales and Commission.
17. In the View Summary dialog box, click Fields, and select the field: File As, and click OK.
18. We will now create four new manual fields as follows:

    Name	Type	Format	Purpose
    2004 First ¼	Currency	Two digits	To enter manually the sales figures for the first quarter
    2004 Second ¼	Currency	Two digits	To enter manually the sales figures for the second quarter
    2004 Third ¼	Currency	Two digits	To enter manually the sales figures for the third quarter
    2004 fourth ¼	Currency	Two digits	To enter manually the sales figures for the fourth quarter

19. And the following three formula fields:

    Name	In the Formula Field window
    Total Sales	[2004 1st ¼]+[2004 2nd ¼]+[2004 3rd ¼]+[2004 4th ¼]
    Commission	Format([Total Sales]*0.01,"Currency")
    Bonus	IIf([Total Sales]>300000,"£5,000.00","")

What Just Happened?

We have created a solution for managing email orders that firstly forwards a copy of the order request to the relevant salesperson and secondly moves the email to an Orders folder. The Outlook incoming mail rules and the setup of the view of the Orders folder ensure that the emails are grouped first by the customer and then by the salesperson in this folder, and the email orders are colored differently according to the quarter of the year in which they are received.

The Orders Clerk obtains the quantity being ordered from the preview pane of the emails, and enters that figure into the Quantity field.

The formula fields operate as follows:

1. The Price field tests the Subject field of the email and returns the item price according to the item ordered.
2. The Cost field multiplies the Cost and Quantity to return the net value of the order.
3. The Discount field calculates a discount of 5% only when the Cost field exceeds £5000 and defaults to zero.
4. The Final Cost field subtracts Discount from Cost.
5. The VAT field calculates the VAT @ 17.5%.
6. The Total Cost field adds Final Cost and VAT.
7. The Running Sum per ¼ is calculated when the Running Sum per ¼ figure is brought down to the next line.

The Result

Here is the finished view of the Orders mail folder:

The linking of this folder with the customers Contacts folder enables the Orders Clerk to view the orders on the Activities tab of the individual customers' contact records as shown:

Creating the Contacts folder, Sales 2004, enables the calculation and recording of sales commission per salesperson. The figures for the fields 2004 first ¼ to 2004 fourth ¼ are taken from the Running Sum per ¼ field in the Orders mail folder.

Transcribing these figures from one set of fields to another is made easier by the color-coding of the different quarters of the year and with the Orders folder open in the background and the Sales 2004 folder open in a new window in the foreground as shown in the screenshot:

The Total Sales field adds all the sales from the four quarters, the Commission field calculates a commission for the sales team of 1% of Total Sales, and the Bonus is calculated where sales exceed £300,000.

That completes the solution for Our Company and, although it may not fit your company, we are sure that the examples will generate ideas that will produce beneficial solutions.