![]() |
General Reading »
Book Chapters »
General
Intermediate
The Microsoft Outlook Ideas Book - Chapter 4: Whole SolutionsBy Mohan RaphelA sample solution for Our Company, integrating various Outlook and Office components, and another 'whole solution', a School‑based example that haven't yet been introduced. |
VBScript, Windows, Visual Studio, Dev
|
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
|
|
|
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:
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.
We will create a scenario involving both the Outlook and Office components.
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:
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.
|
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 |
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 following screenshot shows the Meeting Rooms 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.
|
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 |
|
|
Room Charge |
Formula |
|
|
Total Fee |
Formula |
|
|
Address |
Combination |
[Add Line 1] [Add Line 2] [Add Line 3] |
|
Field |
Condition |
Value |
|
Start |
On or after |
today |
|
Formatting Rule Name |
Field |
Condition |
Value |
Font Format |
|
Internal |
Categories |
Is (exactly) |
Internal |
Blue |
|
Field name |
New name |
Format |
|
Subject |
Booking For: |
|
|
Start |
Date |
Date only |
|
End |
End |
Time only |
We have created the basic view as described, and the fields will operate as follows:
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.
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.
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:




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:

|
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.
CommandButton and change the caption to read Print Invoice.
CommandButton, click the View Code button on the Form Design toolbar, 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
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:
|
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 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:

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.
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:
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:

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.
|
Search for the word(s) |
in |
|
Underwood |
subject field only |
|
Field |
Condition |
Value |
|
Start |
Between |
1/8/05 and 31/8/05 |
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 following is the Print Preview of the Statement of Bookings done in the month of August 2005:

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:
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|
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. |
|
Name |
In the Formula Field window |
|
Price |
|
|
Cost |
|
|
Discount |
|
|
Final Cost |
|
|
VAT |
|
|
Cost inc. VAT |
|
|
Running Sum per � |
|
|
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.
|
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 |
|
Field name |
New name |
|
Categories |
Salesperson |
|
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 |
|
Name |
In the Formula Field window |
|
Total Sales |
[2004 1st �]+[2004 2nd �]+[2004 3rd �]+[2004 4th �] |
|
Commission |
|
|
Bonus |
|
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:
Cost.
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.
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
General
News
Question
Answer
Joke
Rant
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 5 Apr 2006 Editor: Smitha Vijayan |
Copyright 2006 by Mohan Raphel Everything else Copyright © CodeProject, 1999-2009 Web21 | Advertise on the Code Project |