Code For Excel And Outlook Blog

Outlook Automated File Request System

Here is the finished code for the Outlook File Request System.

File Server Code

This is event code which acts as an automated file server, putting files into the specified folder, and sending files back to the requestor, all via email. Once the code is installed, another user requests a file from you by using the following syntax in the Subject line:

Subject: FILEGET drive:path\filename

Where ‘drive’ is is a valid drive letter, ‘path’ is the folder and ‘filename’ is the name of the file requested.

For example,

Subject: FILEGET E:\Files\MyFile.doc

To send a file to another user’s folder, subject should be:

Subject: FILEPUT D:\

And there should be at least one attachment to the email. All attachments will be saved to the same folder.

Note that for security reasons, I limited access to the C: drive, however if you set up a folder called ‘Shared’ on your desktop (Windows 2000/XP), you can place files there that you want to share, and others can request files from there. Of course if this isn’t a concern for you, you can simply remove those restrictions.

Due to object model restrictions, I decided to remove the feature that replies to the group (if applicable). For example, if you CC: five other people on your request, the code simply ignores the other recipients. Originally, I had it set up to reply to them all. However, accessing the Recipients collection of the mail item triggered the object model guard, which was contrary to the intent of the code which is to fully automate the request process. You’d have to sit there clicking ‘OK’ every time someone sent you a request!

All of this is done via Outlook automation. Due to the blogging software I use (which I’ll probably change soon) I cannot post the full code here. I will be demonstrating some of the techniques used to produce this code; click the link above to get the entire thing in the proper format.

First, we initialize the event code using the following code which should be familiar to you by now, if you have browsed the Outlook page. This is stock code which should be part of your code library to be reused over and over.

1
2
3
4
5
6
7
8

Private WithEvents Items As Outlook.Items
 
Private Sub Application_Startup()
Dim objNS As Outlook.NameSpace
 
Set objNS = GetNamespace("MAPI")
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub

All of this code should be placed in ThisOutlookSession module in your Outlook VBE. Just press Alt-F11, double-click on ThisOutlookSession and paste in the above code. Remember, after you install (or edit) the code in this module, you have to restart Outlook for any changes to take effect. Also, any existing code you have seems to stop working until you save and restart Outlook.

Here is the ItemAdd event code that is checking the Inbox for new mail items. We first set an object reference to the mail item that is passed as an argument. Then we determine the full path of the user’s desktop folder, to figure out where the ‘Shared’ folder should sit. Keep in mind this will only work pre-Windows Vista. Then we check the subject line to see if it fits what we are looking for.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Private Sub Items_ItemAdd(ByVal item As Object)
 
If TypeName(item) = "MailItem" Then
    Dim ToDo As String
    Dim WhatAndWhere As String
    Dim Msg As Outlook.MailItem
    Dim MsgAttach As Outlook.Attachments
    Dim MsgReply As Outlook.MailItem
    Dim SlashSign As Long
    Dim sPath As String
    Dim sFile As String
    Dim fso As Object
    Dim UserN As String
    Dim DeskTopSharedFolder As String
    Dim strHelpText As String
' the mailitem is passed to the event code as an argument
Set Msg = item
' get current username so we can figure out the desktop folder name
UserN = Environ("username")
DeskTopSharedFolder = "C:\Documents And Settings\" & UserN & "\Desktop\Shared\"
 
On Error Resume Next
ToDo = Left$(Msg.Subject, 7)
WhatAndWhere = Right$(Msg.Subject, Len(Msg.Subject) - 8)
On Error GoTo 0

At this point we either have a FILEGET request or a FILEPUT request (or maybe neither). Then we cycle through every possible error condition (no attachments, malformed subject) and, if no errors occurred, call the sending sub which sends back the file requested (or saves the attachments to the specified folder).

In the interest of modular programming, there are actually four separate subs which each do something different:

• The File Server Sub, acting as an intermediary sub which takes the validated user-input and passes it to the sending sub.
• The Sending Sub, which takes four arguments (two optional) and is responsible for sending out all emails.
• The Logging Sub, which (if you allow it) logs all file requests and sends to a CSV file, and
• A String Checking Sub which checks if one string is found within another and returns a Boolean value (True or False).

Check out the full code here.

File Server Code

Enjoy,
JP