VBScript and Custom Forms in Outlook

1. Background:


I've recently been given a task to develop some custom task forms in Microsoft Outlook. These forms needed to have some additional fields (specialized text entries with information on the "last action taken", "outstanding actions" and "comments" in addition to standard task fields and categories. More challengingly, they also needed to have some other behaviours such as "automatically emailing people of changes" when they're updated.


If you've ever worked with Microsoft Outlook, you'll know that doing this from scratch isn't as easy as you otherwise might expect. It sounded like the obvious thing to build on Outlook "Tasks" to just enable this extra functionality, but it turns out Outlook can be very restrictive when you get to something it isn't used to doing.

I've done a reasonable amount of VBA coding in Excel and Powerpoint before, and was expecting this to be relatively similar (and straightforward). As it turns out, there were a number of new things to learn before getting this new custom form to work.

This post outlines some of the things I had to work out in order to get this particular script to work. Not all of these "lessons" are profound - some were very quick to find solutions to. Others posed a more significant problem.


2. Outlook Forms - Basics
(or how to start if you've never worked with Outlook forms before)


The actual starting process of creating a new form for entering task information is actually very easy. Just go to Tools->Forms->Design A Form and choose a "base form" from the "Standard Forms Library". In my case, "Task". This will give you a standard-looking "Task" form that you can start modifying in a fairly typical way. However, let me at least note some aspects that surprised me at first:

• You can't actually modify the first Tab (Task) tab on the form. It is fixed. You can't add new buttons or features.
• It is probably not very profitable to try to "get around" this limitation by hiding this first tab (which you can do) and putting all of these features onto a user-modifyable tab. The main "tasks" tab is actually different from the user-definable ones, and if you do attempt to put all of the tasks features on your own user-tab, you will find you've lost some pretty important abilities (like having a "calendar" pop-up allowing users to choose dates/times for these fields.) The overall look and feel also looks different and not as "professional". The issue with Calendar etc can be overcome, but it is going to take some effort!
• Don't get confused by the difference between an outlook Form created in this manner and the Forms you get in the Visual Basic Editor (Tools->Macros->Visual Basic Editor). If you create a Form in the Visual Basic Editor it is a nice, plain, blank form with no specific limitations, but it isn't going to be very useful for most Outlook things you want to do - like generating an Outlook Task, Email or Calendar entry.

Noting this distinction between going into Visual Basic Editor, and the process for creating the form, let us pause for a moment to discuss VBA and Outlook (especially for those people, who like me, might assume it will be similar to Excel, Word or Powerpoint in the Office suite)

3. Aside - Visual Basic For Applications and Outlook (If you're used to Excel/Word/Powerpoint):

When you are using Outlook, you might notice that (like other Office applications) there is the usual Tools->Macros->Visual Basic Editor menu options. You might expect that, like with other Office applications you will be able to record VBA macros and then modify them to work with Outlook. However, you'll note that Outlook does NOT have a Macro recorder.

You have to write your macros from scratch. This is because (see http://support.microsoft.com/kb/q146636/) "Outloook does not support a full object model, such as those in Word or Microsoft Excel", so not everything you may want to do may be possible in VBA within Outlook.

The key to using VBA with outlook is understanding this Outlook Object model, which defines what objects, properties, methods and events are available.

Outlook Object Model: http://msdn.microsoft.com/en-us/library/aa221870(office.11).aspx

4. Outlook Scripting - VBA and VBScript:

Having discussed VBA briefly, above, let's now return to the task at hand - namely creating a custom task form. In this particular case, we'll find that VBA Macros actually have very limited utility, in any case.

To quote http://www.outlookcode.com/article.aspx?ID=40:

"Visual Basic for Applications (VBA) is actually only one of the two programming languages available for writing code in Outlook. The other is VBScript. VBScript is used by Microsoft outlook custom forms."

So, if we're going to have our custom task form work for us, we're going to be working in the special Outlook forms (not a VBA form), with VBScript (not VBA) doing the scripting work behind it.

Luckily, however, VBScript is very similar to VBA as a language, and isn't very hard at all to pick up (even if you aren't familiar with VBA). The following references may help, however:

• VBScript Language Reference: http://msdn.microsoft.com/en-us/library/d1wf56tt.aspx
• VBA Features NOT in VBScript: http://msdn.microsoft.com/en-us/library/30593abb.aspx
• VBScript in a nutshell: http://www.intranetjournal.com/articles/200009/vb10.html

5. Developing the custom task form

In (2), above, we discussed how to open the standard outlook task form and some limitations on modifying these. Useful things you'll need when creating the custom form include:

• Control Toolbox - A list of controls (like "listbox", "radiobutton", "textbox") you may like to put on your form.
• Field Chooser - A list of "fields" (such as "notes", "owner", "Bcc", "Complete", "Actual Work" etc) that individual items have and you may like to display.

These two items can both be obtained by right-clicking on the form and checking the "Control Toolbox" or "Field Chooser" item, respectively, in the menu.

Most basic functionality is very simple to obtain. For instance to create a new text field that is linked to a user-defined field either drag that field onto the form-palete, or drag the control you wish to use onto the form-palete. If you have dragged a particular control (eg a "textbox" or "label") onto the form, and then decide later you wish to link it to a particular field (say "owner" to display the outlook owner of the item), right-click on the control and select "properties". On the "Value" tab, choose the field (in this case, "owner") you wish to link to. For basic properties, everything happens pretty-much automatically.

You can use the "Properties" and "Advanced Properties" options on any control to set its look and feel or modify this "data binding".

For simple forms, that's probably all you need to know. Plenty of information is also available at:

http://www.outlookcode.com/article.aspx?ID=35

However, in my case there was some more advanced behaviour required. This included a list-box allowing multiple people who were "responsible" for a task to be selected and then behaviour with a series of radio buttons that would email those responsible when the new task was created. The default values of these radio buttons needed to modify based on whether it was a "new task" or an update on an old task - and whether the task had actually been modified since being open, in the latter case. This required use of vbScript.

6. Adding VbScript behind Outlook Forms:

Starting with the simple things first, to get to the VBScript, just go to the Form->View Code option on the menu. You'll get a basic script editor.

Here's some important, but basic, standard-named methods in vbScript to get you started:

• Create a method "Function Item_Open()" that will run when a new item is opened. Use this to perform a range of initialization steps.
• Create a method "Function Item_Close()" that will run immediately before closing an item (task). In my case, this function could be used to send an email to those who were made "reseponsible" in the task if the appropriate options were chosen.
• Create a method "Function Item_Write()" that will be called prior to saving.
• Create a method "Sub Item_CustomPropertyChange(ByVal Name)". This method will be called whenever any custom property (one of your custom fields, mentioned above) is changed by the user of the form. You need to test the "Name" attribute to handle a particular item being changed.
• Create a method "Sub Item_PropertyChange(ByVal Name)". This method will be called whenever a standard (non-custom) property is changed.

Further, here are some of the most common method calls from the VBScript object model that I used:

• Item.UserProperties("myUserFieldProperty") - is a function that returns the value of a particular user property/field on the form. Can also be used to set that property.
• Set controlPage = Item.GetInspector.ModifiedFormPages("myPageOnForm") - is a function that returns an object that represents your custom page (tab) on the form.
• Set myControl = controlPage.Controls("myControlName") - is a function that uses a page of your form (obtained as above) and gets an object that represents a particular control on the form. You can then usee this control to obtain information about it. For instance, if it is a label use "myControl.Caption" to get its current caption.
• Use the vbScript "Split" function to break a comma separated string (for instance, obtained from a control that represents a multiple selection of items) into an array that can be looked through.
• Use the vbScript "For Each ... Next" method to loop through each item in the array that is created.
• Use Application.getNameSpace("MAPI").CurrentUser to get the current username of the person logged in.

7. Some Problems I encountered:

All of this looks relatively simple (and, indeed, is). However, I did encounter a number of specific problems in coding up my solution to this problem that took a while to sort out and are probably worth discussing briefly.

(A) How to put a carriage return into MsgBox in VBScript:

I was wondering how you put a carriage return into a MsgBox in VBScript. It turns out one way is to do the following:

MsgBox "This is Line 1. " & vbNewLine & "This is Line 2." & vbNewLine & "etc..."

(B) Creating an email in VBScript:

REF: http://www.tek-tips.com/viewthread.cfm?qid=1374311&page=1

One thing you may wish to do is create an email using VBScript. There are a number of places on the internet that suggest something like the following code:

Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
With MyItem
.To = a@b.c
.Subject = "Subject"
.ReadReceiptRequested = False
.HTMLBody = "Message
End With
MyItem.Display

However, I was finding that the line "set olApp=CreateObject("Outlook.Application") was throwing an error.

To fix this, I used set "objMail = Application.CreateItem(olMailItem)" without the need to create the olApp object.

(C) Calling VBA methods from VBScript:

I had a problem of wanting to store some of the data (such as a list of usernames the form would display) locally for each user. I didn't want to use a database, but just store these somewhere in the "Outlook Session" of the user. I was able to do this by placing a VBA function that returned an array into the "Outlook Session" module of the user and calling this function directly from the form's VBScript. To do this, I use:

On Error Resume Next
Set olApp = CreateObject("Outlook.Application")
userInformation = olApp.methodCallOnSessionObject()
If (Err.Number <> 0) Then
' the method wasn't found in VBA on the users session
userInformation = defaultVbScriptMethodCallToFillDefaultData()
End If
On Error Goto 0

It is worth noting here that olApp.methodCallOnSessionObject() is required to call the VBA method. For me it did NOT work to use "Application.methodCallOnSessionObject()" though this was recommended elsewhere on the internet.

It is also worth noting that you may get "Type Mismatch" errors when trying to pass array data between VBA and VBScript. Outlook VBA uses a different variation of the array data type than that supported by VBScript, and therefore a "Type mismatch" error will occur if you attempt to "Type" your VBA output. In the example above, for instance, I receive an error if I attempt to "Dim" the variable "userInformation".

(D) How to get a bound, multi-select Listbox to work?

I mentioned, above, how easy it is to bind a control (say a label on your form) to a field (say, a custom-defined user comments field) and have the control and the bound value stay nicely in synch.

The only exception to this for me was in getting a Listbox to work. It was all very easy when I only had a single-select list-box. However, as soon as I wanted to allow the user to select multiple values in the listbox, I had problems. The most obvious way to create a multi-select listbox is to go into the "AdvancedProperties" of the Listbox and set the "multiple select" attribute to "1-Multi". However, while this did correctly show multiple selections on the form, this seemed to "break" the link to the underlying field on the form when saved. I couldn't get it to work by linking to a standard user-defined field.

My fix: In the end, the only way I was able to make this work and still have the selection show on the form when re-opened was to use a "Keywords" field. Most of the fields I was using were "Text" fields. If you create a user-defined field, say "responsibleUser" and make it a keywords field type, you're on your way to success with a listBox. Then just bind the listbox control to the "responsibleUser" and make it "multi" and it will automatically show a set of checkboxes for multiple selection that are nicely bound to the appropriate field as a comma-separated list.

There is, however, one problem with this approach. You're now likely to get "Type Mismatch" errors.
To quote from http://support.microsoft.com/kb/q201099/:

"It is not possible to directly modify the contents of a user-defined keyword field using VBScript. Outlook uses a different variation of array data type than that supported by VBScript, and therefore a "Type mismatch" error message will appear if you try to display the text of the field in a message box, assign the field to an array variable, or perform any string-related function on it. The simplest way to work around this limitation is by accessing the text via a control or by using a standard keywords field."

Not too hard to do... once you know :-) To see how you can get access to the control to do this, see my "useful vbscript functions" section above!

One final thing, before I leave listboxes. I spent ages trying to work out how to set the selected values on a listbox. I just couldn't find the property or way of doing this anywhere... Finally the lightbulb lit... It's easy - just set the bound value (of the field the listbox is bound to) properly. ie : Item.UserProperties("Responsibility") = myResponsibilityString. Really obvious in retrospect, but seeing as it stumped me for hours, I'll just mention it here for next time!


(E) Issues with "one-off" forms:

Another problem that stumped me for a while (at least, until I started reading about "one-off" forms) was that every time I saved my (now quite complex) forms, they would show up as having the wrong "MessageClass".

Now, a diversion here is probably useful. Every item in Outlook has a MessageClass. The base MessageClass values are things like IPM.Task (for tasks) or IPM.Post (for mail), IPM.Appointment (for calendar entries) etc. When you start extending these base forms, you'll get items with message classes such as "IPM.Task.mySpecialTaskType". When you double-click on these it'll know exactly what form to use to display the information.

However, I was finding that my MessageClass, rather than being IPM.Task.mySpecialTaskType was always showing up as IPM.Task. This was causing me some problems.

It turns out that this is a feature! Basically, there are two different types of (in this case) tasks possible. What I'd created was what's known as a "one-off" form. For information, look at:

http://support.microsoft.com/?kbid=290657

http://outlookcode.com/article.aspx?id=34

To quote: "If code on a received message or a saved item doesn't run, it probably means that the form is 'one-offed.' This is a condition in which the form definition is saved with the item. Such an item has lost the connection with its published form. Other symptoms for one-off form items are an increase in the size of the item, loss of the custom icon for the item, and reversion of the MessageClass value to the default for that type of item."

It turns out that one of the most common ways of creating a one-off form is if you change the form-design in code. In my case, this meant populating the list-box at run-time with a list of users (as discussed above). This automatically caused the form to become a "one-off" form and caused some problems for this.

In my case, the simplest way around the symptoms I was getting from this being a one-off form was to reset the MessageClass to the correct value for my form. That is, set "Item.MessageClass = IPM.Task.mySpecialTaskType". It isn't perfect (as the increased task-file size issue remains, even after doing this as the form definition remains with the object, but now unattached - however, Outlook documentation suggests there's no good way to fix this without recreating a new task from scratch - and recommends the MessageClass attribute as a valid way to get around the symptoms of the one-off form).

Another way of changing the messageclass of a form is discussed below:
http://support.microsoft.com/default.aspx?scid=kb;[ln];q201089

(F) "This function cannot be performed because the message has been changed"

Another problem that became increasingly frustrating was that, in some instances after editing (or creating) a task, if you try and save that task an "This function cannot be performed because the message has been changed" error would occur. Alternatively, if instead of saving in VBScript, I saved the task manually, it would throw an error stating ""The item could not be saved because it has been changed by another user or in another window."

Both of these errors were "intermittent" in that they go away if one clicks off the task and then back onto it. This error is caused by the form being changed onOpen, and seems only to occur when the task that is being opened does not have focus prior to the actual double-click event that opens it. Presumably the vbscript that modifies the form is for some reason being interpreted as being undertaken by a different user.

To date, I haven't been able to "fix" this problem. However, the problem can be found as soon as the form opens. As such, I have an "OnError" code in the "OnOpen" method that runs whenever this error occurs. In my current code, if this error occurs it just shuts down the new task form. This, gives focus to the appropriate task, meaning that the next time someone opens it, the form will display properly and everything works. To the user, it just looks like the form isn't opening when they double-click. However, in the background it is starting to open, finding a problem and closing itself as a result. This is a pretty ugly solution, I know, but it does seem to work in the absence of more information. A post about this problem (experienced by others as well) is available at:

http://www.outlookcode.com/threads.aspx?forumid=3&messageid=25975

8. Conclusions:

Overall, this just summarizes some of my experiences in developing custom forms in Outlook. Apart from some problems that took the bulk of my time to fix, it was actually quite easy to do, even with no prior knowledge of Outlook or VBScript.

Hibernate Query Language

I've recently become interested in the Hibernate Query Language (HQL) and some of its capabilities. This post summarizes some of my initial experiences for my future reference.


Background:


Until recently, my Data Access Objects (DAOs) would pretty much just use the most basic HQL queries to load an object from my database by its ID:


Query q = session.createQuery("from Clazz c where c.id = 1");
List result = q.list();


or alternatively, load a complete list of all database objects for a particular table:


List result = session.createQuery("from Clazz").list();


If I needed to retrieve a list of objects using a more complex criteria, I could always load the full list, as above, and then return the appropriate objects programmatically, but this is terribly inefficient.


In the past I have used SQL queries (rather than HQL) to more efficiently retrieve just the data I was after. Eg:


session.createSQLQuery("select {c.*} from Clazz {c} where NAME like 'searchName'","c",Clazz.class);


For simple mappings this works fine and uses plain old SQL. However, once you begin to use the more powerful abilities of Hibernate (such as mapping Polymorphic associations), the SQL you need to write becomes more and more difficult and convoluted.


That's when it's finally time to bite the bullet and find out that HQL is actually surprisingly easy to use and very powerful.


HQL Statements - structure:

The structure of a general HQL statement takes the form:

[select ...] from ... [where ...] [group by ... [having ...]] [order by...]


Seeing as the only required clause is from, the simplest HQL query (to retrieve a list of all the items in the table mapped by the class clazz) is:


from clazz


To build on this to create much more complex queries (even when you have Polymorphic associations) is very easy. To describe the queries I've been working with lately, I'm only going to use a very small subset of HQL query options, namely:

• select clazz - tells Hibernate to restrict the objects it returns to the specific class you are interested in.
• select distinct clazz - tells Hibernate to remove duplicates in the list returned.
• join - performs an inner join.'
• left join fetch - performs a left outer join (for eager fetching).

Example:


Suppose we have a class, ParameterValue. There are two different types of ParameterValues, which are subclasses of ParameterValue. Namely "ElementParameterValue" and "UnitParameterValue". We use the Hibernate "Table per class hierarchy" mapping strategy to map this to a single table in our database.

All ParameterValues have a Unit class reference (so each ParameterValue belongs to one and only one Unit). In addition, a ParameterValue has a SaveTag reference (so each ParameterValue also has a unique SaveTag).

In my example, I needed to find a list of SaveTags for a given Unit. That is, the question was "for a particular Unit what SaveTags were represented amongst its ParameterValues?"

The simple HQL that achieves this is:

select distinct saveTag from ParameterValue pv join pv.unit u join pv.saveTag saveTag where u.id = :unitId

The select distinct saveTag tells HQL that the result you want sent back is a List of SaveTag objects with no single SaveTag object represented more than once in the list.

The joins perform inner joins with the required Unit data and the SaveTag data, allowing us to reference (in the where clause) or retrieve these objects by name, respectively.

The where clause defines the search criterion.

All pretty easy stuff - nothing too profound - but I've been playing around with a range of other HQL queries, and they're all as easy as this - even for quite complex datasets, and polymorphic mappings.

The take-home message: Have a look at HQL and don't assume that just sticking with SQL (even if you know it well) is going to be easier.

[NB: This post is a placeholder - I might put some more information about useful HQL queries in future].

Hibernate - Mapping booleans using annotations.

Summary:
Using Hibernate annotations to map a Java boolean in MySQL.

The Problem:

I've recently experienced some problems with mapping boolean values in a Hibernate entity to mySQL. The problem arose because the "boolean" type in mySQL is not actually a boolean. It's a tinyint.

MySQL does support the SQL Standard BOOLEAN or BOOL keywords, but treats these as synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.


The solution:

Luckily, this isn't a problem for Hibernate, once you know what you're doing. Indeed, if Hibernate is creating your database, all you need to do is annotate your booleans with correct annotations, namely:

@Column(columnDefinition = "tinyint", nullable = false)
boolean myTrueOrFalseValue;

or, if you need to allow potentially null columns:

@Column(columnDefinition = "tinyint")
Boolean myTrueOrFalseValue;

Both of these will work, however the first one does not allow null values.

WARNING: This means that if you have been trying a number of different attempts to map "myTrueOrFalseValue" you'll probably already have a column in your mySql table and it will be full of null values. You'll need to delete this column manually (and allow hibernate to regenerate), or use some other manual method to ensure there are no null values in the column. If you do have null values in your database, these annotations will cause a "
org.hibernate.PropertyAccessException: could not set a field value by reflection setter of..." error on startup.