Automating Tip Capture with Office Systems 2003 – Part 1

Automating Tip Capture with Office Systems 2003 – Part 1

Hollis D. Paul

March, 2005

Summary:  Tips are captured as  a flat thread of plain text messages, that comprise a “support solution”.  The newsgroup client is Virtual Access and forwarded to an Exchange mailbox.  There are client-side rules in the mailbox that are applied when Outlook is opened which categorize and initialize an Outlook variable in each mail item.  A third rule then moves them to  a specified Exchange folder.  After a number of tips have accumulated, a custom Outlook form is run to convert the email message to a .mht file, which is saved to a directory in the file system, and then attached to an email message that is sent to a SharePoint –enabled Exchange folder.  Within the next hour, a SharePoint process accesses this email message, extracts the attached .mht file and uploads it to the designated SharePoint document library.  Finally, in the early hours of the morning, when not a creature is stirring, the indexing process finds the new files, and includes them in the index.  It is not a completely automated process, but all the heavy lifting is automated. The payoff comes when Tip index is searched and the solution is passed back in response to a similar question by a new user

Contents

Introduction

There are many distinct steps in process that takes newsgroup tips from collection to document library; and beyond to the blog out on the Internet.  These are:

1.      Collect the message thread in Virtual Access.

2.      Apply “quality control” and categorize it with rules.

3.      Move them to collection folders with rules.

4.      Run the Macro that initiates the custom form to process the collected messages.  Select the folder, the Word template, and the Category, and watch the finished pages flash by.

5.      Upload the created files in the file-system to a WSS site on the internet.

6.      Copy the files to new posts to my web-log.  This gives me a public URL to which I can direct suppliants in the newsgroups.  This is not automated, because the web-log software does not have multiple file upload interface.

There are several kinds of payoff of this tip capture process.  It provides a solution “data-base” for my own operation of my SharePoint portal and sites.  I allows me to find and return helpful hints to questioners in the newsgroups and gives me an efficient means of delivering that information.  Others are setting up alerts to my blog and are downloading the new items as them come in, and they can conduct their own searches of the blog entries on the site itself.

The Dory Getails

Operations in VA:  When I recognize a short string of messages as a complete, and interesting, “support solution”, I select that first message, and forward it to one of my Exchange mailboxes.  This gives me an Edit box, in which alter the message.  I delete the original senders email address, retaining his name.  I do this so that my blog entries will not be sources of spamming.  I also edit the Subject line of the forwarded message: I add a three-letter prefix, followed by a colon and a space, which is used by the Outlook inbox rules to set the category on the incoming message.  I also remove any character I recognize as not being legal in a file name, so that when Word constructs a file name from the subject line, it will not quit when it finds the first illegal character.  Then I copy and paste the subject line into the message as the first line of text in the message.  If something does go wrong in the generation of the Subject line, then Word will make a filename from the first line of text.  When I was first testing the code in the Custom form, I did find some files named “

The content of the Tip is the combined text in the thread, from the initial question, to the final definitive answer.  I separate each message by a half-line of asterisk characters.  Consequently, some threading context is lost, but I try to stay away from long, complicated threads.  I do have to perform some quality control, as VA uses fixed line-lengths and inserts a new line character at the end of each line.  Since I am moving text from one VA edit space to another, I do not understand how sometimes I get lines badly split.  But it happens, and I try to manually restructure the paragraphs when it does. 

The outgoing VA message is routed to the Exchange server, which delivers it to the designated mailbox.

The Outlook Inbox rules:  The Outlook Inbox rules are the least tractable of the various links in this automation chain.  They do not have a case structure, so you can’t say set the category of the incoming messages to five different categories based on five different words in the message of an incoming message.  You have to have five different rules, each one recognizing a specific word.  You can assign a category to the message, but you can’t then put a pre-defined value into a standard field in the same rule.  In fact, you can’t set that pre-set that value using a rule—you have to run a macro.  Then, you can’t use that macro to move the message to a designated folder, because the WebDav processing to pre-set the value in the field takes so long that the macro, if you include the move instruction, has moved the message before is saves the new value back to the message, and the moved message does not have the pre-set value.  So you wind up having five inbox rules for each step, and you can’t have the move rules run automatically.  You have to kick of the move operation later, manually.  There is one other gotcha—if you do not reduce your macro security setting from the default High, to Medium or Low, none of the macros will execute. 

The Inbox rules for one category of messages that I am processing are shown below:

          Categorize Rule:     Apply this rule after the message arrives

                                      With SHP: in the subject

                                      Assign it to the SharePoint category

          Pre-set Field Rule:  Apply this rule after the message arrives

                                      Assigned to SharePoint category

                                         And on this machine only

                                      Run Project1.ThisOutlookSession.SetMileage

          Move Cat Rule:      Apply this rule after the message arrives

                                      Assigned to SharePoint category

                                      Move it to the Z_SharePoint_Tips folder

I am not sure why, but the messages are always open on the desktop after the first two rules are applied.  I check to see if the line length manipulation in the collection has worked, editing any messages that show short lines in the text.  On occasion I forget to put the subject line as the first line in the body of the message, or forget to remove characters that are illegal in file names, so I make any corrections I notice and save the messages.  I then mark all such messages as unread.  This allows me to select to apply the Move Cat Rule to only the Unread messages in the Inbox, and not to all messages in the Inbox.  It also sets up the receiving folders with unread messages to be processed, and helps in selecting the correct folder when you are selecting the folder to use when converting the message to *.mht files.

Kicking off the Message conversion:  I use a custom Outlook form to do the message conversion because I can quickly put VBScript code behind it to open the word document, etc.  I think of this as an Action Item, because what it does has nothing to do with a contact form, even though it is a custom contact form.  You will notice that I name the form beginning with a numeral, so that it will appear at the beginning of the Contact folder, and I just have to open the Contacts folder and there it is.  In a just world, it should be only a matter of double-clicking on the name of the form, it opens up and I make the selections of the folder containing the target items, select the Word Template Name, fill the list, and start the conversions processing.  Alas, a world built on the foundation of Outlook is not a just world—I have never been able to re-use a form.  I presume it is because I didn’t start the form from scratch, but it really is not worth doing all that.  So, to avoid the mousey little process of opening Outlook, clicking File -> New -> Choose Form, and running down the forms listed in the Organizational Forms Library and picking the proper one, I wrote a macro to open a new instance of the form.  The opening process now consists of opening Outlook, pressing ALT-F8, and selecting the macro from the list.  (In my case there is only one. )  Within seconds the blank form appears.  The code for this macro, on my machine, is:

Sub SharePointTips()

    'Declarations

    Dim myItem As ContactItem

    Dim myMailItem As MailMessage

    Dim myRecip As Recipient

    Dim myOlApp As New Outlook.Application

    Dim myOlNS As NameSpace

    Dim myOlfldr As MAPIFolder

    Dim myOlExp As Outlook.Explorer

        Set myOlExp = myOlApp.ActiveExplorer

        Set myOlNS = myOlApp.GetNamespace("MAPI")

        Set myOlfldr = myOlNS.GetDefaultFolder(olFolderContacts)

        Set myItem = myOlfldr.Items.Add("IPM.Contact.SharePointTips_N")

        myItem.Display

        Set myRecip = Nothing

        Set myMailItem = Nothing

    'free variables

    Set myItem = Nothing

    Set myOlNS = Nothing

    Set myOlfldr = Nothing

    Set myOlApp = Nothing

    Set myOlExp = Nothing

End Sub

The following statement adds a contact item using the custom form definition— IPM.Contact.SharePointTips_N—to the default Contact form:

        Set myItem = myOlfldr.Items.Add("IPM.Contact.SharePointTips_N")

The ‘N’ on the end of the name designates the version of the form, and you should set it back to A on your first version name, when you modify it for use on your computer.  (The version indication is included in the form name, because that is the only way to force the operating system to get the right form every time.  If you re-publish a form using the same name, then most of the times it works properly, but sometimes it doesn’t.  Those are the times you make major strides towards complete baldness.  So always do it right—publish using Publish As and change the version indicator in the Name field.)

After the Custom form is displayed, the macro destroys its variables and exits.  The form then has a life to live and die at your hands in the Outlook GUI.  Treat it kindly and it will do many things for you.  Figure 1 shows a new instance of the custom form: 

Figure 1:  A blank instance of the custom form

To use the form, one clicks the Select a Folder button, and browses to the desired folder, selects a document template from the drop-down list box, selects the category appropriate to the folder that was selected above, and then clicks the Fill List button.  The code behind the button then finds the items in the folder that [are of the selected category] AND [are unprocessed] and builds a list and displays them in the large text box control. (The control expands as needed.  You can check that it is running correctly if the count of the items in the list is the same as the number of the unread items in the folder.)  Once the list is filled, you click the Create Documents button, and the code behind that button then converts the messages to Word documents, saves them to the file system as *.mht files, creates a new Outlook message, attaches the just saved *.mht file to it, and sends it to the appropriate Exchange public folder.  Sometimes one sees the Word document appear in foreground, and then it changes to a *.mht file (you can tell the difference by where the left-hand margin is), and then the next Word doc appears.  Other times you just see an indication in the task bar that Word has DocumentN open, and then it receives a real file name, and then DocumentN+1 is shown, etc.  When all evidence of Word files goes away, you can delete the custom form.  Don’t bother to save it to have a record of what messages you have processed.  It turns out you have the original Outlook message still in its folder in the inbox.  You have a copy of the converted message document in the file system.  You have the Outlook message with the attached document in the designated Public Folder.  Finally, you have the *.mht document in the portal site’s document library.  So you can’t really lose anything.  Indeed, eventually, once you are satisfied that you have your documents where you want them, you have to clean out the intermediary containers or grind to a halt for lack of free resources.

Select the Source folder:  The user selects the Source Folder by clicking the “Select a Folder” button.  The form opens a folder list control from which the user makes a selection.  Figure 2 shows the folder list control.

Figure 2:  The folder selection control showing the 5 folders containing the Tips messages.

The folder selection control will open pointing to the folder one sets as the default in the display field opposite the Select a Folder button.  It is important that this default button is not set to one of the Tips folders, because the user might forget to select a folder.  If a folder is not actually selected, then an object required by a later button is not created and the form throws an error message and halts.  So, the forms designer should not get super efficient, and set the most used tip folder as the default folder.  This could cause confusion when the form user forgets to select a folder and the error appears.

Select a Document:  The control opposite the Select a Document is a straight drop-down List box.  Figure 3 shows the Select a Document in its drop-down state. 

Figure 3:  The extended List in the Select a Document Drop-Down List Box.

The document that is being selected is the Word Template that is used to give the page a colored background.  (Other things could be contained in the template, but I was only interested in the background color to provide the user a continuous affirmation that they were in the correct document library, etc. )  The text in the list box is the name of the Word Template that is stored in the standard Office Systems 2003 Word directory for templates for the user who is running the custom form—somewhere in the users’ profile. (In code, the form gets the standard path to templates, concatenates the name from the list box, and adds the template to the newly opened document.)  It is reasonable, in this control, to set the most commonly used template name as the default value.  No error is caused if you do not actually select a name from the list.

 Select a Category:  Click the Select a Category button to bring up the standard Outlook control that allows the user to select the category that is used to select messages to convert.  Figure 4 shows this Category Selection control in full extension.

Figure 4:  The standard Outlook category selection control.

The user should only have one box checked.  The specific category names that the user chooses to use in the tips conversion operation are entered in the Master Category List, and will appear in the full list in the control.  Each category must exactly match the corresponding text string that is used by the code to recognize the category assigned to an Outlook message item.  (This match between code and Master Category List has to exist in both the custom form code and the category assignment and recognition macros in the Outlook Inbox rules.)  It is OK to set the most commonly used category as the default category of the “Select a Category” control.

Fill the List:  The list of messages is shown in the text box control that resides next to the  “Fill the List” button.  When the button is clicked, the code behind the button opens up the folder that is associated with the selected category, finds all the messages in the folder that are assigned the chosen category AND are marked as unprocessed, and finally displays the qualified messages in the text box.  Figure 5 shows a textbox that has been filled in a processing run.

Figure 5:  An instance of the Filled List text box during processing.

Operationally, this list is not required, but it is a good check for the user to be able to compare the number of items in the list with the number of items in the Outlook folder waiting to be processed.  I do this often enough so that it is a small number, and the comparison is easy.  However, most of the code behind the Fill List button actually creates and fills the items object that is used to create the converted documents.

Shabang!  Once three parameters have been chosen, and the list filled, it is time for the user to stand forth as Captain Marvel and click the magic “Create  Documents” button.  There is a loud “Sproing-oing-oing” sound, a small, green talking frog crawls out of your Buster Brown shoes, and the documents march across your terminal and disappear.  The man in cape and tights turns out to be just the user, which is always a disappointment.  But the frog is sometimes funny.  The dog remains in your shoe, but sometimes barks.  A word to the wise—don’t tell any of this to your analyst.

Note:  The Figures, which display as blank boxes in the body the blog article, are available in the AtomaTips Gallery of this blog.  Better quality images are available in a zip file, and can be downloaded from the following URL: