• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Automate E-Mail Operations with Outlook and VBA

    October 6, 2010 Michael Sansoterra

    Note: The code referenced in this article is available for download here.

    Microsoft Office is a wonderful productivity suite. Besides being packed with features, its claim to fame for me (and the major reason I haven’t switched to other competing Office suites) is that it allows a developer to customize and enhance the product’s functionality using Visual Basic for Applications (VBA) or .NET code. For businesses, this means that Office applications (Word, Excel, Access, PowerPoint, Outlook, and others) can be used in the software development process to solve certain problems that would otherwise be difficult to write from scratch, such as automated document and spreadsheet creation.

    Over the years, Four Hundred Guru has run quite a few tips on how to wed DB2 for i data with Excel using VBA. This time, I’d like to focus on doing something similar with Outlook. To follow this tip, you’ll need some basic knowledge of the VBA language and the ActiveX Data Objects (ADO) library, which will allow Outlook to communicate to the IBM i (a.k.a. AS/400). Further, this tutorial was written using Outlook 2007 (a.k.a. Outlook 12), although I don’t believe there are any 2007 specific features implemented.

    The Challenge

    Consider the common dilemma of receiving manifold e-mails with text file attachments and the need to automatically upload these attachments to a DB2 table (without user intervention). These specific e-mails are received by a company’s Exchange server and delivered to a Microsoft Outlook client. From there, Outlook should:

    1. monitor all new mail items
    2. identify each specific e-mail as an automation candidate by inspecting the e-mail’s sender, subject, attachment, or body (e-mails that don’t fit the specified criteria are left alone)
    3. automatically save the text attachment (in this example, a comma delimited “csv” format)
    4. read the saved attachment and upload the data to DB2 for i
    5. move the e-mail to a “processed” folder when done

    Outlook can do this relatively easy!

    The Outlook Integrated Development Environment

    To begin, the VBA Integrated Development Environment (IDE) is accessed within Outlook the same as in other Office applications: choose Tools→Macro→Visual Basic Editor or use the Alt+F11 key sequence. Once in the VBA IDE, you have the ability to add your own standard or class modules.

    When viewing the Outlook VBA project window (see Figure 1 below), you’ll notice a special VBA class module specific to Outlook called “ThisOutlookSession”. When programming VBA within Outlook, the “ThisOutlookSession” class module can be used to tap into the Outlook event model. If you’re not familiar with Office VBA coding, Office follows an event-driven programming model. “Events” are specified areas where a developer can place code that will be automatically called in response to certain incidents such as when Outlook starts, an e-mail is received, an e-mail is sent, etc. You can see an Outlook Object Model reference here.

    Figure 1: Outlook VBA projects have a special class module called ThisOutlookSession.

    The code you create within Outlook will be stored in a special file called VbaProject.otm. For backup purposes, know that this file is stored within the Windows user profile’s application data subfolder. (Get OS specific information here.) Once you’ve written code you’ll want to make sure you have a backup of this .otm file or at least obtain an export your modules for safe keeping.

    The VBA Code

    For simplicity, the code example is primarily contained in one subroutine with a secondary subroutine used for initializing the automatic “monitor” for new e-mails. All of this sample code belongs in the “ThisOutlookSession” class module.

    The sample code can be downloaded here. This VBA code is documented, but I’ll briefly explain a few of the highlights.

    First, the class module starts with this class level object declaration:

    Private WithEvents olInboxItems As Items
    

    This statement declares object variable “olInboxItems” as an Items collection. The Items collection is an Outlook specific class that can be used to reference any group of Outlook “items,” including mail, notes, meetings, tasks, etc., that are contained in a given Outlook folder (such as Inbox, Sent Items, Outbox, etc.). The “WithEvents” keyword signifies that the class module can define code for any of the events triggered by the Items collection. The Items class offers the following important events: ItemAdd, ItemChange, and ItemRemove. As you’ll see in a minute, the code will take advantage of the ItemAdd event, which will be made to execute when an item is received in the Inbox.

    The next thing to consider is a special event called “Application_Startup”. Any code in this subroutine will be called automatically when Outlook starts. In this example, the only thing to do when Outlook starts is to make the “olInboxItems” variable reference the Inbox folder:

    Private Sub Application_Startup()
        Set olInboxItems = _ 
            Session.GetDefaultFolder(olFolderInbox).Items
    End Sub
    

    When Outlook starts, this statement creates the “monitor” of the Inbox. (Note that any Inbox subfolders will not be monitored by this statement.) olInboxItems is told to reference the items in the Inbox folder. Because it was declared using WithEvents, you can use its ItemAdd event to do the necessary processing when an e-mail comes in. With event driven programming, Outlook will do most of the work–you just need to fill in the missing pieces to do what you want when the event occurs.

    The subroutine that will do all of the “real work” will be called when the ItemAdd event of the Inbox is triggered. The subroutine definition is shown here:

    Private Sub olInboxItems_ItemAdd(ByVal Item As Object)
    

    When this event is fired, Outlook will pass a reference to the item just added to the Inbox; it could be an e-mail, a note, an appointment, etc. For the Inbox, usually this event is fired when new mail is received, but it can also be fired by simply moving an item from one folder to another, such as moving an item from “Deleted Items” back to the Inbox.

    When the item is received, first make sure it’s an e-mail and not a note or a meeting request. Then make sure the newly arrived e-mail has one attachment and has a specific subject line before attempting to automatically process it:

    If TypeOf Item Is MailItem Then
        Set olMailItem = Item
        If olMailItem.Attachments.Count = 1 _
        And InStr(olMailItem.Subject, "My Import") > 0 Then
    

    In a more elaborate scenario, you may also want to enhance the filter to check things like attachment type, sender name, and even received date, just to make sure someone didn’t drag an old e-mail back to the Inbox.

    Once the mail item “passes” the validity test, it is saved in the C:tmp folder. For the record, this attachment is assumed to be a comma delimited file without headers:

    strAttachmentName = olMailItem.Attachments.Item(1).FileName
    olMailItem.Attachments.Item(1).SaveAsFile "c:tmp" + strAttachmentName
    

    The code that follows (not shown here) uses the ADO library to open a table (physical file) on the IBM i. It then opens and loops through the text file, parses the row data into columns, and sends the data to DB2 for i. To keep things uncomplicated, it is assumed that the work table columns in DB2 match the fields in the .csv attachment file in number of data columns and data types. In a more sophisticated scenario, we could also kick off a stored procedure or i/OS command to process the data that was just uploaded. Remember, to use the code sample as-is, you’ll need to add the ActiveX Data Objects library to your project’s references, which can be found in the VBA IDE choose Tools→References.

    Finally, once the attachment has been copied to DB2, the Move method of the olMailItem object is used to automatically move the message out of the Inbox to an Inbox subfolder:

    olMailItem.Move olDestFolder
    

    olDestFolder is a Folder reference that is set in the code to point to a pre-existing subfolder called Processed_OK unless an error is encountered. When an error occurs, olDestFolder is set by code to reference a pre-existing subfolder called Processed_Errors. Pretty simple stuff!

    If you know VBA, programming Outlook is fairly simple–it just takes a bit to learn the object model and event model. The Microsoft Outlook documentation and a site called OutlookCode.com were all I needed to get started.

    A Few Pitfalls

    I should mention a couple of disappointing things: there is no easy way to set the status bar or the hourglass (which are relatively easy to control in Excel and Access) to alert the user to what’s happening. You can however, add the Microsoft Forms library to your Outlook project references and then create a status form similar to how you would create an Access or Excel form. However, this is more work than I want to do for such a simple task.

    Also, while testing startup related code, you may find yourself needing to shut down and re-restart Outlook. In some cases Outlook has been known to hang around in the Windows background after being closed. Once you shut it down, check the Windows task manager to make sure Outlook.exe doesn’t appear in the list of active processes.

    For those using an older version of Outlook, the following declaration in Outlook 2007:

    Dim olDestFolder As Folder
    

    will need to be changed to:

    Dim olDestFolder As MAPIFolder
    

    Remove the Security Noose

    Because of vulnerabilities in receiving e-mails and the potential nefarious uses of VBA code (and Microsoft’s reputation for security issues), recent Outlook versions default the macro security setting to ignore VBA code. This restrictive setting can be overridden by choosing Tools→Macro→Security and then selecting “Warnings for all macros” or “No warnings for macros.” In earlier versions of Office these levels were labeled High, Medium, and Low. If you choose “Warnings for all macros” (a.k.a. medium) and add VBA code, you’ll receive this message every time you start Outlook:

    Figure 2: Outlook Macro security “warning”.

    If you do not change this setting your VBA code will not run!

    The Benefit

    The Outlook object and event model is full featured and can be used to do myriad tasks including:

    • sending e-mails
    • tracking when a specific e-mail was sent
    • automatically forwarding certain messages
    • doing something specific with received e-mails and attachments
    • automatically adding a reminder/alert to your calendar when, say a shipment or customer repair hasn’t completed in time

    The possibilities are endless. Further, the Outlook model can be referenced by code outside of Outlook including VBScript (with some limitations), .NET, or from within another ActiveX/COM-based application. Microsoft Office is a great suite and you paid good money for it, so use it to its full potential.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

    RELATED RESOURCES

    Office 2007: Outlook Object Model Reference

    Office 2007: Outlook Events

    Writing VBA Code for Microsoft Outlook



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Help/Systems:  FREE Webinar. Oct. 7, 9 a.m. CST. Robot/REPORTS: Satisfy Your Audit Requirements
    System i Developer:  RPG & DB2 Summit in Minneapolis, October 12-14 for 3 days of serious training
    COMMON:  Join us at the 2011 IT Executive Conference, May 1-3, in Minneapolis, MN

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Tango/04 Touts International Sales IBM i Dominates the CPW Capacity Budget

    3 thoughts on “Automate E-Mail Operations with Outlook and VBA”

    • AC says:
      September 6, 2017 at 11:32 am

      Just looking for a complete VBA for Outlook 2010 to: delete new incoming email messages subject of which contains specific text. None of above code works, maybe because code is not complete.

      Reply
    • Dinesh Mishra says:
      May 26, 2018 at 3:39 pm

      Great article. I was able to do the coding for saving the attachment however was having hard time to call it when the new mail arrives without using the run script option in rules. This article has been very helpful. Thanks a lot for posting it.

      Reply
    • Eric Havemann says:
      October 5, 2018 at 11:44 pm

      Even though I have done some fairly involved Excel VBA applications including processing emailed attachments, I have been relying on command-line POP3 and SMTP utilities. I now realize that Outlook should be viewed as an email processing automation engine and not just an end-user client. This article has given me enough information and background to see how I can re-engineer and simplify these applications – thank you! Now it’s just a matter of learning the object model to see where the hooks are to do what I need to do.

      Reply

    Leave a Reply Cancel reply

Volume 10, Number 30 -- October 6, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
inFORM Decisions

Table of Contents

  • Automate E-Mail Operations with Outlook and VBA
  • USA Time Format in Query for i, Redux
  • Admin Alert: Getting Started with i/OS Security Auditing, Part 2

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle