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
                      

Select all Open in new window

Note: This is a repost of a posting I created on my external blog. And while I say "I hope he [paul] finds it useful", what I really mean is, I hope you too can find it helpful :)

When trying it for yourself, start small, in easy to identify steps and always double check before you begin.

Any comments or issues, please post a comment below and we will try to address them.