Assigning a excel Macro to work in all workbooks

[FatBoy]

Hi all,

 

I have found a cool little macro to e-mail individual worksheets inside a workbook, here: Sending Just a Worksheet via E-mail. I have managed to save the vb macro to a workbook but what I really want to do for a staff member is save the macro in excel not the workbook so its available in any workbook she opens! Is this possible? She tells me this option used to be available to her but the staff member that did this has long since left and I can't see how you would do this!

 

Thanks

FB

[LosOjos]

What you need is an Excel Add-In: create a new workbook, add in the code you want to a module within it, then save as an Excel Add-In (*.xla). You can then add this to a users Excel via the trust center on 2007+ or via the Tools > Add-In menu on older versions of Excel.

 

I'd also recommend adding code to add a menu item on load and also to remove it should the Add-In be removed, something like this (on the 'ThisWorkbook' module of your add-in's VBA):

 

Private Sub Workbook_AddinInstall()
On Error Resume Next
Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Dim CmdBarMenuItem As CommandBarControl
'
' Point to the Worksheet Menu Bar
'
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
'
' Point to the Tools menu on the menu bar
'
CmdBar.Controls("My Add-Ins").Delete                             
Set CmdBarMenu = CmdBar.Controls.Add(Type:=msoControlPopup)
CmdBarMenu.Caption = "My Add-Ins"
'
' Add a new menu item to the Tools menu
'

Set CmdBarMenuItem = CmdBarMenu.Controls.Add(Type:=msoControlButton)
With CmdBarMenuItem
   .Caption = "Outlook Send All"
   .OnAction = "'" & ThisWorkbook.Name & "'!outlook_send_all"      'Replace outlook_send_all with the name of your sub routine
End With
End Sub

Private Sub Workbook_AddinUninstall()
   On Error Resume Next 'In case it has already gone.
   Application.CommandBars("Worksheet Menu Bar").Controls("My Add-Ins").Delete
   On Error GoTo 0
End Sub

Edited November 22, 2010 by LosOjos
Spelling & Grammar

[LosOjos]

If you're unsure how to go about it, post/PM the code you're using and I'll turn it in to an add-in for you if you want

[FatBoy]

I think I have created the addin ok...(Not tested it yet!) but can't work out how to add it (DOH) gone to excel options > Trust center settings... > but than can't find a place to add it. I can see a place to add the trust location but not add the actual add-in!

[LosOjos]

I think I have created the addin ok...(Not tested it yet!) but can't work out how to add it (DOH) gone to excel options > Trust center settings... > but than can't find a place to add it. I can see a place to add the trust location but not add the actual add-in!

 

That's because I've sent you on a wild goose chase (I should never work from memory, I forget too much!)

 

There is a page in Excel Options called Add-Ins, at the bottom of that page is a label with a drop down box (Manage), make sure it is set to "Excel Add-ins", then press "Go..." In the window that pops-up, click browse and find your add-in file; it'll ask if you want to move it to the user area, say yes otherwise Excel sometimes struggle to find it again; then make sure your add-in is ticked and it should now be loaded whenever that user loads Excel

[FatBoy]

No worries...

 

I have done it and it worked, the only thing that didn't was the adding a button script thing you put in the code above (Unless I can't see the button, where should it be?) I change the "outlook_send_all" to the name of the subroutine "EmailWithOutlook" but no luck with that part. Not the end of the world I added a quick access button to the macro and that works fine it seems. Where should the button be added out of interest though?

 

Thanks

[LosOjos]

There will be a new ribbon called "Add-Ins" and any buttons you add via code will be in there

[FatBoy]

All working well thanks via the button it creates as well. Spot on, really appreciate your help, a thousand thanks

[LosOjos]

Any time!