Automate Outlook

Automate Outlook, open Excel and run Macro on .xls file attachment

    I suppose this page should really be called "how to Automate Excel from Outlook", since we're really using Outlook to automate Excel.

    Oftentimes I find it extremely useful to completely automate some macro process using VBA; to make a procedure completely hands-off. I can leave Outlook running 24/7 and then use a procedure like the one below to run an Excel macro from Outlook.

    This code runs as an event, checking each incoming email for certain characteristics. If an email matches the profile, it opens the attachment (by saving it first), runs a pre-written macro, then cleans up and moves the now-processed email out of the Inbox to another folder.

    You could easily adapt this code to many other purposes, for example, set up an automated file request system in your office. Users would email you with specific words in the subject, and, in response, your code could run a macro or attach a specific file to a blank email and send it back to them. In fact, I went ahead and did just that; check out Outlook File Request System.

    First thing you will need to do is declare a new event handler by putting this line at the top of your ThisOutlookSession module.

    ThisOutlookSession is a built-in class module, so I usually put all my event/class code here to avoid confusion.

    Then, in the Application_Startup() event, place the following code to instantiate the Items event handler when Outlook starts.

    If you don't already have a Startup event, simply add Private Sub Application_Startup() to the top of the above code, and End Sub to the bottom. This will tell Outlook that there will be some events associated with the Items collection in your default Inbox.

    Once you paste in the code above, you will be able to choose the Items class from the dropdown boxes at the top of the VBE code window. The dropdown box on the right will show you the possible events you can monitor from the Items collection.

    Finally, here is where the real work gets done. The VBA code monitors the default Inbox for any new items added. If they are mail messages, it checks the sender, subject and number of attachments. We have already pre-determined where the email will come from, the subject line and how many attachments; if you decide to use this code in your office, you'll need to agree on a format and subject with the sender beforehand. The code will open the attached workbook, run a pre-written macro on it, then move the email to our storage folder, all without any user input needed. In this example, the macro is in the personal.xls workbook.