Importing Documents Into Outlook


I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook ?”.  I replied back and told Paul that using automation, in this case scripting, to add files to Outlook is pretty simple.  Paul and I exchanged a few more tweets and from them I learned that he has the following requirements for this process:

  • Read the file names from a DB table.
  • The DB could be MS Access or SQL Server.
  • Files could be of any type.
  • Needs to work with Outlook 2003, 2007, and 2010.
  • The process needs to run from outside of Outlook.

The code for handling this is below. It’s pretty simple and I added a number of comments to help both you and Paul figure out what it’s doing.  The script uses Microsoft’s ADODB technology to connect to and read a table in the DB.  For each row in the table the script creates an Outlook DocumentItem object, a file within an Outlook folder, fills in some information about it, and saves it into an Outlook folder.  In this case I used the inbox, but Paul can modify the code to save it into the folder of his choice.  Paul will also need to set the ADODB connection string to connect to his database.  He can use Connectionstrings.com to find the correct string for either Access or SQL Server.  Finally, he will also need to edit the field names to match those that appear in his table.

The code is written in VBScript making it easy for Paul to edit and use.  All he has to do is copy and paste the code into Notepad, edit it (I included comments where things can or need to change), and save it with a .vbs extension.  He can run it by double-clicking the saved .vbs file.

Since I don’t have Paul’s database and therefore don’t know what fields it contains or the names of those fields I couldn’t test the code before posting.  It may contain errors and there might be some debugging required to get it fully operational.  That aside, it should be pretty close to what Paul needs to solve his problem.  Hopefully he’ll find it useful.

'Create constants
Const olFolderInbox = 6
'Create variables
Dim olkApp, olkSes, olkFld, olkDoc, adoCon, adoRec, strFileType

'Connect to Outlook
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
'Change the default profile name on the next line as needed
olkSes.Logon "Outlook"
'Change the folder as needed
Set olkFld = olkSes.GetDefaultFolder(olFolderInbox)

'Connect to the database table
Set adoCon = CreateObject("ADODB.Connection")
'Edit the connection string on the next line.
adoCon.Open "Connection_String_Goes_Here"
'Edit the SQL statement on the next line as needed
Set adoRec = adoCon.Execute("SELECT * FROM Table_Name")

'Process the records
With adoRec
    Do Until .EOF
        Set olkDoc = olkFld.Items.Add("IPM.Document")
        'Edit the field name on the next line
        olkDoc.Attachments.Add .Fields("Name_of_File_Path_Field")
        'Edit the field name on the next line
        olkDoc.Subject = .Fields("Name_of_File_Name_Field")
        'Edit the field names on the next line
        strFileType = Mid(.Fields("Name_of_File_Name_Field"), InStrRev(.Fields("Name_of_File_Name_Field"), ".") + 1)
        'Add more file types as needed
        Select Case LCase(strFileType)
            Case "doc"
                olkDoc.MessageClass = "IPM.Document.Word.Document.8"
            Case "docx"
                olkDoc.MessageClass = "IPM.Document.Word.Document.12"
            Case "pdf"
                olkDoc.MessageClass = "IPM.Document.AcroExch.Document"
            Case "ppt"
                olkDoc.MessageClass = "IPM.Document.PwerPoint.Show.8"
            Case "pptx"
                olkDoc.MessageClass = "IPM.Document.PwerPoint.Show.12"
            Case "txt"
                olkDoc.MessageClass = "IPM.Document.txtfile"
            Case "xlsm"
                olkDoc.MessageClass = "IPM.Document.Excel.SheetMacroEnabled.12"
            Case "xls"
                olkDoc.MessageClass = "IPM.Document.Excel.Sheet.8"
            Case "xlsx"
                olkDoc.MessageClass = "IPM.Document.Excel.Sheet.12"
            Case "zip"
                olkDoc.MessageClass = "IPM.Document.WinZip"
        End Select
        olkDoc.Save
        .MoveNext
    Loop
End With

'Clean up
adoRec.Close
adoCon.Close
olkSes.Logoff
Set adoRec = Nothing
Set adoCon = Nothing
Set olkDoc = Nothing
Set olkFld = Nothing
Set olkSes = Nothing
Set olkApp = Nothing
msgbox "Import complete.", vbInformation + vbOKOnly, "Import Files to Outlook"
WScript.Quit

Addendum.

The following script can be used to display the MessageClass of the currently selected document in Outlook. This is useful in case you want to modify the code to handle other document types.

Dim olkApp, olkDoc
Set olkApp = GetObject(, "Outlook.Application")
Set olkDoc = olkApp.ActiveExplorer.Selection(1)
msgbox "The message class of the selected item is " & vbCrLf & vbCrLf & olkDoc.MessageClass, vbInformation + vbOKOnly, "Document Message Class"
Set olkDoc = Nothing
Set olkApp = Nothing
Advertisements

3 comments on “Importing Documents Into Outlook

  1. where can I find a list of all possible values for the olkDoc.MessageClass.. like say the document is image type, what will be the correct IPM.DOcument. value for that?

    and thanks for the article.. 🙂

    • Hi, Bhootnath.

      Thanks!

      I’m not aware of any such documentation. I’ve added another script at the bottom of the article that displays the MessageClass for the currently selected document in Outlook. You can import a document to Outlook and then use it to discover what the MessageClass is.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s