Make VBA sub available to any workbook
 

I have a very simple sub I have written in VBA and I would like to have it
available in every workbook I open without inserting the code in each one.
My preference would be to have it called by a custom toolbar button, but I'll
take just about anything. Is this possible and if so, any pointers on how to
actually do it?

I have attached the sub in question:
Private Sub SaveAndSend()
Dim thisSheet As Worksheet
Dim strName As String
Dim strFullPath As String
Dim myOlApp As New Outlook.Application
Dim myItem As Outlook.MailItem
Dim bPos As Byte

Set thisSheet = ThisWorkbook.ActiveSheet
bPos = InStr(1, ThisWorkbook.Name, ".xls")
strName = Mid(ThisWorkbook.Name, 1, bPos) & thisSheet.Name
strFullPath = Application.GetSaveAsFilename(strName, "Microsoft Office
Excel Workbook (*.xls), *.xls")

If strFullPath = "False" Then
MsgBox "Canceled by the user"
Else
thisSheet.SaveAs (strFullPath)
Set myItem = myOlApp.CreateItem(olMailItem)
myItem.Subject = strName
myItem.BodyFormat = olFormatHTML
myItem.HTMLBody = "<a href=file:///" & strFullPath & "" & strName &
"</a"
myItem.Display
End If
End Sub

Make VBA sub available to any workbook
 

Olan Wilson wrote:
I have a very simple sub I have written in VBA and I would like to have it
available in every workbook I open without inserting the code in each one.
My preference would be to have it called by a custom toolbar button, but I'll
take just about anything. Is this possible and if so, any pointers on how to
actually do it?

I have attached the sub in question:
Private Sub SaveAndSend()
Dim thisSheet As Worksheet
Dim strName As String
Dim strFullPath As String
Dim myOlApp As New Outlook.Application
Dim myItem As Outlook.MailItem
Dim bPos As Byte

Set thisSheet = ThisWorkbook.ActiveSheet
bPos = InStr(1, ThisWorkbook.Name, ".xls")
strName = Mid(ThisWorkbook.Name, 1, bPos) & thisSheet.Name
strFullPath = Application.GetSaveAsFilename(strName, "Microsoft Office
Excel Workbook (*.xls), *.xls")

If strFullPath = "False" Then
MsgBox "Canceled by the user"
Else
thisSheet.SaveAs (strFullPath)
Set myItem = myOlApp.CreateItem(olMailItem)
myItem.Subject = strName
myItem.BodyFormat = olFormatHTML
myItem.HTMLBody = "<a href=file:///" & strFullPath & "" & strName &
"</a"
myItem.Display
End If
End Sub

Olan,

Store it in your Personal macro workbook (Personal.xls), which is
automatically loaded (but hidden) whenever you open Excel. If you do
not yet have a Personal macro workbook you can create one by using the
macro recorder with the option of storing it in your personal macro
workbook. After you've created it and put your macro in it, go to Excel
and use View ToolBars Customize to create a new toolbar. On the
command tab choose macro. Drag the little smiley face to your new
toolbar (you'll see what I mean). Right click on it to get options such
as which macro to attach and which icon to use (get rid of that
annoying smiley face). It is not a bad idea to change the name property
which controls the tip which appears when your mouse hovers over the
button.

Hope that helps

-John Coleman

Make VBA sub available to any workbook
 

"John Coleman" wrote:
Olan,

Store it in your Personal macro workbook (Personal.xls), which is
automatically loaded (but hidden) whenever you open Excel. If you do
not yet have a Personal macro workbook you can create one by using the
macro recorder with the option of storing it in your personal macro
workbook. After you've created it and put your macro in it, go to Excel
and use View ToolBars Customize to create a new toolbar. On the
command tab choose macro. Drag the little smiley face to your new
toolbar (you'll see what I mean). Right click on it to get options such
as which macro to attach and which icon to use (get rid of that
annoying smiley face). It is not a bad idea to change the name property
which controls the tip which appears when your mouse hovers over the
button.

Hope that helps

-John Coleman

Exactly what I needed. Thanks so much.