Attach current workbook to open email

attachment paperclip

In Attach Current Workbook to Current Email Doug Glancy has some VBA code to attach the current workbook to an open email (using the Outlook object model). It's early bound, but it is simple to convert it to late bound.

As you may already know, I'm a fan of late bound code. Early binding requires a manual reference to the appropriate object library which can be a nuisance to remember.

For code that is going to be given out, I always recommend late binding. However, that is no reason why you should not take advantage of Intellisense when writing code, then convert it to late bound when giving it out.

Part of the process of making code late bound is converting any constants to their numeric equivalents. I maintain a list of enumerated constants for late bound VBA automation for just such an occasion.

That being said, only a few changes to Doug's code are necessary to make it late bound.

Change Object References

Original code:

Dim outApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Late bound code:

Dim outApp As Object ' Outlook.Application
Dim OutMail As Object ' Outlook.MailItem

Late bound objects are always declared As Object (or As Variant I suppose). I maintain the early bound information in a comment for documentation (and also so I can switch later if necessary).

Convert or Enumerate built-in constants

We still have two Outlook-specific variables to deal with: MailItem and olMailItem.

Set OutMail = outApp.CreateItem(olMailItem)
If Not TypeOf .ActiveInspector.CurrentItem Is MailItem Then

Since we are abandoning an early bound reference to Outlook, VBA has no idea what MailItem and olMailItem are. To solve the first problem, all we need to do is check our list of enumerated constants for late bound VBA automation and declare the appropriate constant:

Const olMailItem As Long = 0

Instead of simply writing outApp.CreateItem(0) (as most late bound code samples will do), now we can write our code using the olMailItem constant as if our code was early bound.

The second problem requires changing the way we determine if the currently open item is an email. Instead of comparing objects using TypeOf, we use the TypeName function.

Original code:

If Not TypeOf .ActiveInspector.CurrentItem Is MailItem Then

New code:

If Not TypeName(.ActiveInspector.CurrentItem) = "MailItem" Then

Make these three changes and your code will be late bound.

Anything else?

The only other thing I might change is the way Outlook is instantiated.

My code would assume that the user already has an open email and is trying to attach the current workbook to it, in which case Outlook would already be open. If that is the case, I would use CreateObject to grab the existing instance instead of testing if it is already open, since CreateObject will return the existing instance of Outlook.

Thanks to Doug for posting such a useful piece of code!

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Attach current workbook to open email:

  1. Thanks for tips JP, especially about late binding.

    My code originally assumed the user has an open email too, but I had some feature creep.

    This may end up being my only Outlook VBA post, as you, and a couple others, have pretty well covered the waterfront!

Mentions:

  1. [...] JP at JP Software Technologies posted a follow-up to this. Share this:FacebookPrintEmailShareDiggRedditStumbleUpon This entry was posted in Outlook, VBA by [...]

This article is closed to any future comments.
excel school learn dashboards