VBA References to new workbooks or set to run at startup

BoilerBob

Board Regular
Joined
Apr 10, 2006
Messages
98
Thanks to the board, I have been able to create a tool that automatically opens Outlook, creates and displays an email with activeworkbook as an attachment (see below).

My dilemma now is that when I extract tabs (along with he VBA code) and save the new worksheet, the references to the "Microsoft Outlook 10.0 Library" that are required to run the application are no longer selected. Hence the new worksheet gets a subscript out of range error when running the macro.

I tried adding the references to my personal.xls but that did not work.


Is there a way to either send the references along with the VBA, generate code that will select a reference, or to set up Excel so that the "Microsoft Outlook 10.0 Library" (msoutl.olb) references are always selected and available when Excel opens?


THANKS!


Dim olkApp As Outlook.Application
Dim olkNameSpace As Outlook.NameSpace
Dim olkDefaultFolder As Outlook.MAPIFolder
Dim olkMail As Outlook.MailItem
...
Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(Outlook.olFolderInbox)
Set olkMail = olkDefaultFolder.Items.Add(Outlook.olMailItem)
...

' Display mail message
With olkMail
.To = "jj@mrexcel.com"
..
.Subject = "Test"
.Body = "This sure was fun."
.Attachments.Add ActiveWorkbook.FullName
.Display 'Use .Send to send
End With

' Release variables
Set olkMail = Nothing
Set olkDefaultFolder = Nothing
Set olkNameSpace = Nothing
Set olkApp = Nothing

End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You could use late-binding so you don't need the references.
Code:
Sub test()
Const olFolderInbox = 6

Dim olkApp As Object
Dim olkNameSpace As Object
Dim olkDefaultFolder As Object
Dim olkMail As Object

Set olkApp = CreateObject("Outlook.Application")
Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(olFolderInbox)
Set olkMail = olkDefaultFolder.Items.Add


' Display mail message
With olkMail
.To = "jj@mrexcel.com"

.Subject = "Test"
.Body = "This sure was fun."
.Attachments.Add ActiveWorkbook.FullName
.Display 'Use .Send to send
End With

' Release variables
Set olkMail = Nothing
Set olkDefaultFolder = Nothing
Set olkNameSpace = Nothing
Set olkApp = Nothing

End Sub
 
Upvote 0
With your suggested changes, my entire code is as follows:

Sub Email()
' Opens Outlook Application, creates and displays an email with
' activeworkbook as attachment


Const olFolderInbox = 6
Dim olkApp As Object
Dim olkNameSpace As Object
Dim olkDefaultFolder As Object
Dim olkMail As Object

Set olkApp = CreateObject("Outlook.Application")
Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(olFolderInbox)
Set olkMail = olkDefaultFolder.Items.Add


' If Outlook isn't open, open it
On Error Resume Next
Set olkApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then Set olkApp = CreateObject("Outlook.Application")

On Error GoTo 0

Set olkNameSpace = olkApp.GetNamespace("MAPI")
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(Outlook.olFolderInbox)
Set olkMail = olkDefaultFolder.Items.Add(Outlook.olMailItem)

' Use of CreateItem can cause run-time permission error -2044526587
'Set olkMail = olkApp.CreateItem(Outlook.olMailItem)

' Create Header

Dim myHeadder$
myHeadder = ActiveWorkbook.Worksheets("Quote LTR").Cells(1, 54)

' Display mail message
With olkMail
.To = ActiveWorkbook.Worksheets("Quote LTR").Cells(1, 56)
.CC = ""
.BCC = ""
.Subject = myHeadder
.Body = "…We look forward to working with you on this project."
.Attachments.Add ActiveWorkbook.FullName
.Display 'Use .Send to send
End With

' Release variables
Set olkMail = Nothing
Set olkDefaultFolder = Nothing
Set olkNameSpace = Nothing
Set olkApp = Nothing

End Sub





I am getting a "compile error: Variable not defined" on the following line:

Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(olFolderInbox)




The declarations look fine to me. Again, any help would be appreciated.

Thanks...
 
Upvote 0
Are you sure that's the line that causes the error?

This is the line that errors for me.
Code:
Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(Outlook.olFolderInbox)
The next line will also error.

The reason for the error is the use of Outlook.

Now that would be OK if you still had the reference to Outlook, but won't work without it.

Try this.
Code:
Sub Email()
' Opens Outlook Application, creates and displays an email with
' activeworkbook as attachment
Const olFolderInbox = 6
Dim olkApp As Object
Dim olkNameSpace As Object
Dim olkDefaultFolder As Object
Dim olkMail As Object
Dim myHeadder$
    
    Set olkApp = CreateObject("Outlook.Application")
    Set olkNameSpace = olkApp.GetNamespace("MAPI")
    Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(olFolderInbox)
    Set olkMail = olkDefaultFolder.Items.Add
    
    
    ' If Outlook isn't open, open it
    On Error Resume Next
    Set olkApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then Set olkApp = CreateObject("Outlook.Application")
    
    On Error GoTo 0
    
    Set olkNameSpace = olkApp.GetNamespace("MAPI")
    Set olkDefaultFolder = olkNameSpace.GetDefaultFolder(olFolderInbox)
    Set olkMail = olkDefaultFolder.Items.Add
    
    ' Use of CreateItem can cause run-time permission error -2044526587
    'Set olkMail = olkApp.CreateItem(Outlook.olMailItem)
    
    ' Create Header
    
    
    myHeadder = ActiveWorkbook.Worksheets("Quote LTR").Cells(1, 54)
    
    ' Display mail message
    With olkMail
        .To = ActiveWorkbook.Worksheets("Quote LTR").Cells(1, 56)
        .CC = ""
        .BCC = ""
        .Subject = myHeadder
        .Body = "…We look forward to working with you on this project."
        .Attachments.Add ActiveWorkbook.FullName
        .Display 'Use .Send to send
    End With
    
    ' Release variables
    Set olkMail = Nothing
    Set olkDefaultFolder = Nothing
    Set olkNameSpace = Nothing
    Set olkApp = Nothing

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top