Time and Expense Billing: Part I

This two-part series presents a Time and Expense Billing application intended for consultants and others who bill for their services. It uses custom Outlook Journal items to store information about hours worked and expenses incurred for a client, and prints Word invoices containing the slip data.

Access is generally more suitable for storing data than Outlook, because Access is a full-featured relational database, while Outlook has a flat-file MAPI database. However, because this application uses only Outlook and Word, it's functional to virtually all Office users (even the most basic edition of Office includes Outlook and Word, while only some editions include Access). The application works with Outlook 98/Word 97 or Outlook 2000/Word 2000.

The first part of this series explains how to install and use the application. In Part II, "Understanding How the Application Works," explains the techniques used in the VBScript and VBA code behind the application. If you aren't interested in coding, you can just skip Part II, although it will provide some extra information and troubleshooting tips.

Using the Application

The Time and Expense application has three components: an Outlook custom Journal form, and two Word templates (available for download; see end of article for details). Additionally, you need to create a custom Outlook folder to store the Time and Expense slips, and several subfolders under the Notes folder to store information on work and expense types, rates, and client projects. Since Microsoft doesn't provide a setup program for installing Outlook applications, you'll need to follow these steps to install the application:

Step 1. The application makes use of an Outlook category called Client. If you don't have a Client category, create this category by clicking the Categories button on an Outlook item, and then clicking the Master Category List button on the Categories dialog box, as shown in FIGURE 1.



FIGURE 1: The Outlook Categories list.

Step 2. Type Client in the New box, and click the Add button. After closing this dialog box by clicking OK, you can now select the Client category in the Master Category List.

Step 3. Designate some of the contacts in your Contacts folder as clients by selecting the category Client for them. (You may wish to make a copy of the existing Contact item, and modify the copy, rather than working with the original, especially if the client's billing address is different from the regular business address.)

Step 4. Make sure that the business address on the Client contact item is the correct billing address for invoices, and enter the company name (or an abbreviation of it) in the File As field. The File As field is used for the name of the folder for saving client invoices, so remove any punctuation marks that can't be used in folder names (such as slashes and periods). For example, if the company is called "Hi/Lo Enterprises Inc." the File As field for this client could be "Hi-Lo Enterprises."

Step 5. Under your Notes folder, create two subfolders of the Notes type, called Expense Types and Work Types. Create as many notes in each folder as you wish, each containing a description of a work or expense type to use for categorizing slips. For example, a work note could be "Access database development," and an expense note could be "Modem phone call." (Don't enter the rates or costs on these notes.)

Step 6. (Optional) If some of your clients have projects that require subtotals on invoices (such as the Contact Letter Templates and Hotel Management projects for the Advanced Enterprises sample client), create a subfolder under the Notes folder for each of these clients, using the client name (or abbreviation) stored in the File As field for that client. Create a note in that folder for each client project. See FIGURE 2 for an illustration of a client subfolder under the Notes folder.




FIGURE 2: A client subfolder under the Notes folder, containing projects and rates.

Step 7. Create a folder of the Notes type called * (just an asterisk) under the Notes folder. Create a note in this folder for each standard hourly rate you bill (each note should just have a number, such as 150.00, without a currency symbol). The reason for calling the folder * is that the asterisk is conventionally used as a symbol for "anything" in search expressions. The hourly rates stored in this folder are the default rates for all clients who don't have their own custom rates.

Step 8. (Optional) If some of your clients have their own hourly rates, create folders for those clients under the Notes folder, and create a note for each hourly rate in each client subfolder.

Step 9. Slips are stored in a folder of the Journal type called Time and Expense Slips. Create this folder directly under the top-level Personal Folders folder, by right-clicking the top-level folder and selecting New Folder from its context menu. FIGURE 3 shows the Create New Folder dialog box, with the name and folder type entered. After creating this folder, you may wish to place a shortcut to it on the Outlook Bar for ease of access. (To create an Outlook Bar shortcut, drag the folder to the Outlook Bar.)



FIGURE 3: Creating the new Time and Expense Slips folder under the Personal Folders top-level folder.

Step 10. Open the ZIP file that accompanies this article (see end of article for details) and extract the Slip.oft Outlook template file to your TemplatesOutlook folder (or another location, if you prefer) and the two Word template *.dot files to your TemplatesInvoices folder (or another location, if you prefer).

Step 11. Double-click on the Slip.oft file to open an instance of the custom Journal form. If you get the macro virus warning message, click the Enable Macros button to continue.

Step 12. Publish the Slip form to the Time and Expense Slips folder by selecting Tools | Forms | Publish Form from the item's menu. Select the Time and Expense Slips folder from the Look In box, and type Slip into the Display box. Click the Publish button.

Step 13. Close the form, and click the No button when asked if you want to save it. (Since you just published the form, there's no need to save this instance of the form.)

Step 14. Right-click the Time and Expense Slips folder and select Properties to open its properties sheet. Select Slip as the form to use when posting to this folder, as shown in FIGURE 4. This will ensure the custom Slip form is used when you create a new Journal item in the Time and Expense Slips folder.



FIGURE 4: Selecting the Slip custom form as the form to use for the Time and Expense Slips folder.

Creating Time and Expense Slips

With the Time and Expense Slips folder open, click the New Journal button on the main Outlook toolbar to create a new slip. The default slip type is Time. The standard rates (from the notes in the * folder) will be entered in the Hourly Rate combobox.

The first step is to select a client from the Client Name combobox, as shown in FIGURE 5. The entries in the drop-down list are the Client items in your default Contacts folder (the contacts with Client selected as their category). After selecting a client, the Billing Address textbox is automatically filled in with the client's business address. If the client has its own rates, they will replace the standard rates in the Hourly Rate combobox.



FIGURE 5: Selecting a client for a new Time slip.

If you made a Notes subfolder for the selected client with several projects, the client projects can be selected from the Client Project combobox.

Select a Work Type from the Work Type drop-down list, and type a description of the work performed on this slip in the Description textbox. Today's date is automatically entered in the Slip Date field; the date can be changed using the arrows to the right of the textbox.

The Calculate Cost Using option group offers three ways to enter time worked on the slip:

1) Timer option (default selection): Click the Timer button or press [Alt]t to start the timer; the caption changes to display the time it was started. Clicking the Timer button again (or pressing [Alt]t) turns the timer off and pops up a confirmation dialog where you can edit the time to add to the Total Time; clicking OK adds the time to the Total Time field.

2) Enter Start and End Times: Select the Enter Start and End Times option to enter the start and end time in the Start Time and End Time textboxes, using AM and PM (or just A and P) to avoid confusion. After you exit the End Time textbox, you will get the same confirmation dialog box as for the Timer option, so you can edit the time to be added to the Total Time. Switching between the Timer and Enter Start and End Times calculation selections preserves the Total Time and Total Cost values, so you can accumulate time on a slip using either of these methods.

3) Flat Rate: Enter the quantity of flat rate items in the Quantity textbox, and the flat rate amount in the Flat Rate textbox. Switching to (or from) this selection clears the cost fields. (You can also clear these fields by clicking the Clear Current Amounts button.)

To change a new slip to an Expense slip, click the Expense option in the Slip Type option group. A filled-in Expense slip is shown in FIGURE 6. The timer is disabled, and there's no option group for selecting a cost calculation method on an Expense slip; just enter the Item Quantity and Cost, and the Total Cost will be calculated automatically.



FIGURE 6: A filled-in Expense slip.

The Copy Data to New Slip button makes a copy of the current slip, with the same data in the upper portion (above the horizontal incised line); the new slip will have today's date, and blank time and expense fields.

Generating a Custom View

I've found the custom view in FIGURE 7 to be useful for the Time and Expense Slips folder (the BillingInformation field is the Client Name; I use this built-in field to create a Restrict clause in the code).



FIGURE 7: The Time and Expense Slips folder with a custom view.

To create a custom view, start by selecting the Entry List View in the Current View drop-down menu, and (with the Time and Expense Slips folder open) select View | Current View | Customize Current View from the main Outlook menu to open the View Summary dialog box. Then click the Fields button to open the Show Fields dialog box, where you can select the fields for the view.

Use the other buttons on the View Summary dialog box to make other customizations to the folder view. Alternatively, you can right-click a column heading in the view itself, and select fields from the Field Chooser.

Creating Invoices

After you've created a number of slips for a client, you can create an invoice. There are two invoice templates included in this article's ZIP file: the Outlook Invoice template prints on plain paper, while the Outlook Perfed Invoice prints on paper that is perforated 3.5 inches from the bottom (the bottom portion of the invoice is printed as a "Please return with payment" coupon with the invoice number, balance due, and client name and address). This paper is available from Xerox as #3R4900. Other than the layout of the first page, these templates are functionally identical, and you can use whichever you prefer. You may find it convenient to add the template you wish to use to the Office Shortcut Bar for easy access.

Double-click a template to create a new invoice, or click its button on the Office toolbar. A UserForm opens, where you can select the client for the invoice. Clients are selected from the same list as on the slips (Contacts with the Client category selected), but they're filtered to only include clients with outstanding slips. After you select a client from the Clients combobox, slips for the selected client are listed in the Time Slips or Expense Slips listboxes, as shown in FIGURE 8.



FIGURE 8: Time and Expense slips for a client on a Word UserForm.

The listboxes are multi-select, and initially all the slips are selected (checked). To deselect a slip, [Ctrl]-click the checkbox in front of the slip. Only selected slips will be included on the invoice.

The textbox to the right of the Docs Path button shows the document's path from the template's DocsPath custom document property, or the Windows registry, if the path stored in the doc property is invalid. This is your main top-level Documents folder, usually C:My Documents. Specific folders for invoices will be created in an Invoices subfolder located under this folder. If the path is all right as is, you don't need to do anything; if it's wrong, click the Docs Path button to open a dialog box, where you can edit the document's path. The edited path will be saved in the invoice template.

Click the OK button (or press [Alt][O]) to fill in the invoice with data from the selected slips. The first page of the filled-in invoice with client project subtotals is shown in FIGURE 9. The Time slips are written to the Professional Services table in the invoice, and the Expense slips are written to the Additional Charges table (if there are no slips in one category, that table is deleted). The slips in each table are summed for a subtotal, and the subtotals are added to create a Balance Due amount. If there are other client projects, there' s another level of subtotals under each project.



FIGURE 9: An invoice filled with data from Time slips.

The UserForm's code creates a save name for the invoice composed of the client abbreviation (from the File As field in the client's contact item), the word "Invoice," and today's date, and assigns it a path to the folder matching the File As field, in the Invoices folder, under your Documents folder.

Table gridlines are turned on so you can see the table structure; they don't print on the invoice. Text with a gray background is text from Word custom document properties displayed in DocProperty fields.

If there's no Invoices folder under the specified Documents folder, or no folder for the client under the Invoices folder, the folder will be created, at least if you have the Scripting Runtime library installed on your computer (the folder creation is done using objects in this library). If you don't have a reference set to the Scripting Runtime library, you can avoid an error by creating the folders in advance. (See the "Notes" section at the end of the article for more information on the Scripting Runtime library.)

If all the slips on the invoice are flat rate slips, the Professional Services table portion of the invoice will be reformatted. In addition, the templates include a Copy macro that adds a WordArt "Copy" watermark to the invoice. This graphic may not print on some printers.

After printing an invoice, be sure to close it before creating another invoice. The Finalize Invoice dialog box pops up when the invoice is closed; clicking Yes increments the saved invoice number, marks the slips on the invoice as billed, enters the invoice date and number on each billed slip, and then closes the invoice. Clicking No simply closes the invoice. Creating an invoice, and then closing it without finalizing, is useful if you just want to check how much a client owes you without actually generating an invoice.

If you don't close and finalize each invoice before you create the next invoice, the same invoice number may be used on the next invoice. If you realize later that you shouldn't have finalized an invoice, you can manually reset the invoice number in the template (be sure to open the .dot file, rather than creating a new document from it). To change the stored invoice number, open the template's properties sheet (File | Properties), click the Custom tab, select the InvoiceNumber property, and change the number to match the number of the last invoice you prepared; the next invoice will be the next higher number. FIGURE 10 shows the Outlook Invoice template's properties sheet, open to the Custom tab.



FIGURE 10: Changing the saved invoice number in the template's properties sheet.

You'll also need to mark the slips belonging to that invoice as not billed, by unchecking the Billed checkbox on each slip (no need to modify the invoice date and number, since they will become invisible the next time the slip is opened).

The name and address dummy information in the Invoice header (see FIGURE 11) is hard-coded. You can open the template and modify this information as desired, so that the new invoices you create will have your information in their headers.



FIGURE 11: Invoice header information.

Conclusion

In Part II, we'll delve into the code that makes the application work: the VBScript used behind the Outlook custom slip form, and the Visual Basic for Applications (VBA) used in Word. We'll also take an in-depth look at the Outlook custom form used for recording time and expense data. See you then.

The application referenced in this article is available for download.

Helen Feddema is an independent developer specializing in Microsoft Office applications, concentrating on Access, Word, and Outlook. She has written or co-authored several books on Access. Her most recent book is Data Access Objects: The Definitive Guide [O'Reilly, 2000]. Helen's currently writing Microsoft Access Inside Out [Microsoft Press], and an Outlook book for O'Reilly. She has also contributed chapters to several multi-author Office books, including Special Edition: Outlook 2000 [QUE, 2000]. Helen has been a regular contributor to Pinnacle's Smart Access and Office Developer journals and Woody's Underground Office newsletter, and is currently the editor of the biweekly ezine, Woody's Access Watch, for which she writes the Access Archon column. Her Web site (http://www.helenfeddema.com) features pages with Office code samples and past Access Archon columns. She can be contacted at mailto:[email protected].

Copyright 2001 Informant Communications Group. All Rights Reserved. - Site Use Agreement - Send feedback to the Webmaster - Important information about privacy