<

Importing Documents Into Outlook

Published on
9,282 Points
3,182 Views
1 Endorsement
Last Modified:
Approved
Community Pick
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

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.
1
Comment
Author:David Lee
0 Comments

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month