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