CreateObject Function (VBA/Excel/2002-3)

Topic

New Reply

For a client’s quotation system I have been using code to send an Excel sheet from a multipage workbook via MS Outlook. All was fine until recently.

The code would not execute completely but would throw up an error message when it got to the CreateObject line. The message related to “The specified module could not be found”.

A search of the error number (8007007e) revealed the following. If you add the optional variant ServerName (in this case “localhost”), the code runs as expected.

Original code in blue, update added in red:

Sub SendMail()

    Sheets("Quote - e-mail version").Select
    Application.Goto Reference:="CustomerCopy"
    
 Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "ddmmyyyy")
    Application.ScreenUpdating = False
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    With wb
        .SaveAs ThisWorkbook.Name _
              & " " & strdate & ".xls"
        Set OutApp = CreateObject("Outlook.Application",  "Localhost") 
        Set OutMail = OutApp.CreateItem(olMailItem)
        With OutMail
            .To = "Edit_This_Text"
            .CC = ""
            .Subject = "Edit_This_Text"
            .Attachments.Add wb.FullName
            .Display
        End With
            .ChangeFileAccess xlReadOnly
        Kill .FullName
           .Close False
    End With
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

This is just one part of the code and form required but gives an idea of the workaround.

Merry Christmas, Leigh

Reply | Quote

Replies

Maybe my ignorance, but why use createObject when you are using early binding? Wouldn’t this suffice:

Set OutApp = New Outlook.Application

?

Reply | Quote

Jan, my knowledge of VBA would be on a much higher level if I knew how to properly respond to your reply.

I do not know why early binding is indicated in the code I posted. Simply that I needed code to do something (create an object to allow me to send an XL sheet to a mail recipient) and that code was available after a brief search.

My posting was to alert others to the problem that surfaced on a client workstation and that was resolved by the addition of ‘localhost’ in the CreateObject function. Why? I do not have sufficeint experience to explain why.

But thanks for the heads up, now I have more research to do.

Regards, Leigh

Reply | Quote

Early binding means that you set a reference to the relevant object library in Tools | References…, in this case to the Microsoft Outlook n.0 Object Library. You can then declare an object such as

Dim OutApp As Outlook.Application

Advantages of early binding:
– IntelliSense will kick in when you type OutApp followed by a point (period).
– You can perform a syntax check.
– You can use symbolic constants such as olMailItem, making your code more readably.
– Code execution will be more efficient.

Disadvantages of early binding:
– The reference may break if the user has a different version of Outlook (in particular an earlier version than you have) or if Outlook is installed in a different folder than on your PC.

Its counterpart is late binding, where you don’t set a reference to the object library. You declare objects associated with the application as plain Objects:

Dim OutApp As Object

Advantages of late binding:
– The code will also work if the user has a different version of Outlook, or if Outlook is installed in another folder.

Disadvantages of late binding:
– You can’t use IntelliSense.
– You can’t check the syntax for application-specific issues.
– You cannot use symbolic constants such as olMailItem, you must use their value instead.
– Code execution will be less efficient.

Reply | Quote

SInce you dimmed OutApp as Outlook.Application you MUST have early binding, like Hans explained.

This implies you can start Outlook using the statement I posted, instead of the CreateObject statement you used yourself.

Reply | Quote