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.
Const olFolderInbox = 6
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
'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.
'Edit the SQL statement on the next line as needed
Set adoRec = adoCon.Execute("SELECT * FROM Table_Name")
'Process the records
Do Until .EOF
Set olkDoc = olkFld.Items.Add("IPM.Document")
'Edit the field name on the next line
'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)
olkDoc.MessageClass = "IPM.Document.Word.Document.8"
olkDoc.MessageClass = "IPM.Document.Word.Document.12"
olkDoc.MessageClass = "IPM.Document.AcroExch.Document"
olkDoc.MessageClass = "IPM.Document.PwerPoint.Show.8"
olkDoc.MessageClass = "IPM.Document.PwerPoint.Show.12"
olkDoc.MessageClass = "IPM.Document.txtfile"
olkDoc.MessageClass = "IPM.Document.Excel.SheetMacroEnabled.12"
olkDoc.MessageClass = "IPM.Document.Excel.Sheet.8"
olkDoc.MessageClass = "IPM.Document.Excel.Sheet.12"
olkDoc.MessageClass = "IPM.Document.WinZip"
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"
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.