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

Create a new table-type view from the Define Views | New option, and name the view Meeting Rooms.

In the View Summary dialog box, click Fields and select the field Full Name, and click OK.

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

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.

Create two new Categories for the room bookings, named Internal and External.

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.

Create a new table-type view from the Define Views | New option, and name the view Meeting room calendar.

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

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

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

Open a new appointment form in the Meeting Rooms Calendar folder, and access the design mode from Tools | Forms | Design This Form.

Click the tab (P.2), and first make it visible from Form | Display This Page, then rename it to Print from Form | Rename Page.

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.

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.

From the Control Toolbox, drag a Command Button onto the form.

Right-click on the CommandButton and change the caption to read Print Invoice.

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:

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.

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.

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:

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:

Word Text Form Field

Type

Format

Add Line fields

Regular text

No special formatting; text will appear as entered.

Room Number

Number

Start and End fields

Date

Duration

Number

The standard number format 0.00 is used with the hrs suffix.

Room Rate per hour, Charge, and Amount Due

Number

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:

Assign either the Internal or External category.

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

From the Filter | Appointments and Meetings tab, create the first filter:

Search for the word(s)

in

Underwood

subject field only

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

Field

Condition

Value

Start

Between

1/8/05 and 31/8/05

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:

Categories for each salesperson (the category being the name of the salesperson).

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

Create a new Mail folder named Orders.

Create a new table-type view from the Define Views | New option, and name the view By Customer.

In the View Summary dialog box, click Fields, and select the fields: Received, Subject, and Categories, and click OK.

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.

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:

The Price field tests the Subject field of the email and returns the item price according to the item ordered.

The Cost field multiplies the Cost and Quantity to return the net value of the order.

The Discount field calculates a discount of 5% only when the Cost field exceeds £5000 and defaults to zero.

The Final Cost field subtracts Discount from Cost.

The VAT field calculates the VAT @ 17.5%.

The Total Cost field adds Final Cost and VAT.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.