Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CreateObject Function (VBA/Excel/2002-3)

    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:

    <font color=blue><pre>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", <font color=red> "Localhost")</font color=red>
    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</pre>

    </font color=blue>
    This is just one part of the code and form required but gives an idea of the workaround.

    Merry Christmas, Leigh

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateObject Function (VBA/Excel/2002-3)

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

    Set OutApp = New Outlook.Application

    ?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateObject Function (VBA/Excel/2002-3)

    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. <g>

    Regards, Leigh

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: CreateObject Function (VBA/Excel/2002-3)

    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.

  6. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CreateObject Function (VBA/Excel/2002-3)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •