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
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