Tuesday, February 24, 2015

Sending email: Early v. Late Binding


One of my projects, as a final step each month, emails the results to my client's clients.

Initial Solution

The first working code I wrote for this is
Sub PrepareEmail(Optional Recipients As String, Optional CopiesTo As String, Optional Subj As String, _
                 Optional Msg As String, Optional AttachmentLocation As String, Optional HTMLMsgStyle As String)
    Dim appOutlook As Outlook.Application
    Dim EmailItem As Object
    On Error GoTo PrepareMail_Error
    Set appOutlook = New Outlook.Application
    Set EmailItem = appOutlook.CreateItem(olMailItem)
    With EmailItem
        .Display ' This must come before adding the Msg or the Signature will be wiped out
        .To = Recipients
        .CC = CopiesTo
        .Subject = Subj
        .HTMLBody = HTMLMsgStyle & "<p class=""msg"">" & Msg & "</p>" & EmailItem.HTMLBody
    End With
    If IsAvailableFile(AttachmentLocation) Then
        ChDir (PathFromFullFileName(AttachmentLocation))
        EmailItem.Attachments.Add AttachmentLocation
    End If
    GoTo PrepareMail_Exit
    MsgBox "There was a problem generating emails.  You will need to do this step manually."
End Sub

The Problem

All seemed fine until my user's computer went belly-up and had to go for a 2-day visit to IT, so I took over creating the report for her.  

Since we had made some config entries in her copy, and there were some refinements she wanted, rather than making the changes in my copy and running using that copy, then re-configuring when I passed it back to her, I took her copy.  

That's when the problem came in.  All of a sudden it blew up when I tried to load it.  Looking and looking, I finally figured out the Reference to MS-Outlook was marked as missing, so I unchecked the one that was missing and checked the one lower in the list.

All was well and good...or so I thought.

I finished the changes and tested them, but when I passed the file back to her we had the same problem as before, and this time I couldn't fix it as I had before.

Real Research

Since I couldn't just make changes to fix the problem this time, I decided to Google "MISSING: Microsoft Outlook 15.0 Object Library".

A few clicks and a bit of reading confirmed what I thought: The problem was  differences in versions.  She has Excel 2013.  I have Excel 2010.  The object libraries for 2013 are 15.0,  14.0 for 2010.

One source said the file would autocorrect when going from earlier version to later, but not vice versa.  That was what I observed.

However, there also seems to be a persistence issue once the problem is fixed once because when I passed it back to her, then back to me, I couldn't fix it as before.

The Solution: Early v. Late Binding

One person was looking for some code to determine which version of Office was installed, then including the right file.  This was pooh-poohed.  

The consensus was the fix is to change from Early Binding to Late Binding.  This means to make a few (very few) changes.

Step 1: Remove the Reference

While in the VBE, click on the References option of the Tools menu.   When the dialogue comes up, click off the reference to "Microsoft Outlook xx.0 Object Library".

Step 2: Modify the Code

This is what I feared.  I imagine horrible lengths of change with deep meanings to be derived, a bit of voodoo in a Potemkin village.  Ahhh....Here it is!

Excerpted from above: Commented out old code...Replacement Code...Unchanged

'    Dim appOutlook As Outlook.Application
    Dim appOutlook As Object
    Dim EmailItem As Object
    On Error GoTo PrepareMail_Error
'    Set appOutlook = New Outlook.Application
    Set appOutlook = CreateObject("Outlook.Application")