Package Outlook VBA Macro with Custom Button on ribbon

Question

I have a VBA Macro on outlook that allows me to forward an email as an attachment to different email addresses.I have customised my outlook ribbon and created a button that is linked to this macro. How would i go by packaging this item (Macro and Ribbon Button) so i can give it to other users to install on their machines?

Thanks,

score 2 · Answer 1 · answered Nov 15 '18 at 18:20

I think you could do it programatically. I have a process for sharing my Excel macros/ribbon, and you could try modifying it for Outlook. I'd be curious as to whether it works the same, since I'd like to eventually share my outlook macros as well. All the user has to do is run a batch file. There are a couple of components:

Create a folder in a shared directory, where you can store your personal macros and ribbon.
Copy over your macros from your local folder. For Excel, I copied over my personal excel file where I store my macros, out of C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART. For Outlook, I assume you'd use the VbaProject.OPM file in C:\Users\username\AppData\Roaming\Microsoft\Outlook.
Copy over your UI file (ribbon) from your local folder: C:\Users\username\AppData\Local\Microsoft\Office. The Excel ribbon is called Excel.officeUI, and I assume the Outlook ribbon is the file called olkexplorer.officeUI.
Create your batch file, which will Copy the macros and UI out of the shared directory and on to the user's local drive. It will then call a VBS file which will slightly modify the UI file, replacing occurrences of your username with the user's username. I'm new to batch files and not sure how to insert comments into the code, but you should modify the first path (use the above path where the Outlook OPM file is stored), as well as the name of the macro file and the UI file (VbaProject.OPM and olkexplorer.officeUI).
```
 Echo off
 cls
 Echo Please ensure that Excel(Outlook) is closed before continuing.
 pause     
 cls

 cd YourSharedDirectory
 copy PERSONAL.XLSB c:\users\%USERNAME%\AppData\Roaming\Microsoft\Excel\XLSTART\*.* /y
 cls

 cd YourSharedDirectory
 copy Excel.officeUI C:\Users\%USERNAME%\AppData\Local\Microsoft\Office\*.* /y
 cls

 @echo off
 wscript "findReplaceUserNameinUI.vbs"
```

Create a VBS file (which you run at the end of the batch file) to find YOUR username in the UI file and replace it with the username of the person downloading the macros, since it won't work on their computer otherwise.

Const ForReading = 1
Const ForWriting = 2

'get username
username= CreateObject("WScript.Network").UserName

'change this to the local location for outlook UI
uiPath = "C:\Users\" & username & "\AppData\Local\Microsoft\Office\Excel.officeUI"

'open the UI file in notepad, find & replace with new username
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(uiPath, ForReading)
strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "your user name", username)
Set objFile = objFSO.OpenTextFile(uiPath, ForWriting)
objFile.WriteLine strNewText 
objFile.close

msgbox "Completed!"

I do the same for Excel and it works great, everyone in the office gets my updates as the batch file is in their startup, I'd like to figure out how to do it for Outlook, but it's not looking as easy. Here is how I do it, same idea, different batch code. https://www.freesoftwareservers.com/display/FREES/Distributing+Macro+via+Add-In+-+Customized+Ribbon+-+Via+Batch — FreeSoftwareServers, Sep 25 '19 at 08:45

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Outlook macro aren't made to be disitributed such as in Excel. You should build an add-in in .NET Framework using Office tools.

Here's an introduction : https://learn.microsoft.com/en-us/outlook/add-ins/quick-start?tabs=visual-studio

If you still want to export your macro, there are some workaround :

Solution 1

You can export modules as .bas, .cls, frx, and .frm files.Other users will have to reverse the process by importing them.

With this method no user will lose any code.

BUT

When importing the ThisOutlookSession module, it will be renamed ThisOutlookSession1. You'll have to copy the code from here and paste it in ThisOutlookSession or it won't run.

Solution 2

If the users don't have any macro project in their Outlook, you can copy (assuming there is only this macro on your Outlook) the VBAProject.OTM that you can find in %AppData%\Microsoft\Outlook and overwrite the user's one with it.

The last method is the one I used when I had to share a macro. But if I knew it would be such a ****** I would have use VB.NET immediately. There are other solutions but I never experimented them.

Hi Thanks for that. The only issue is that i had to create the button on the ribbon myself and then link the macro to it. Is there a way i would implement this into the code so that it would add the button to the ribbon when i distribute to the users. — Will, Jun 19 '18 at 12:50
I'm currently doing this, I'll let you know if I find a solution. — Jérémy Gamba, Jun 22 '18 at 06:52
Look at Roi-Kyi Bryant's answer on this post : https://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba You can load a custom ribbon when opening your workbook. The disadvantage of this method is that you'll lost any previous customizations on your ribbon. — Jérémy Gamba, Jun 22 '18 at 09:06

score 0 · Answer 3 · answered Jun 19 '18 at 13:42

VBA macros are not designed for distributing solutions on other/multiple machines. Moreover, VBA macros doesn't allow to customize the Fluent UI (aka Ribbon UI) programmatically. You need to develop a COM based add-in for these reasons. That is for they were invented! See Walkthrough: Create your first VSTO Add-in for Outlook to get started quickly.

You can read more about the Fluent UI (aka Ribbon UI) in the following series of articles:

VSTO provides two main ways for creating a custom ribbon UI:

In case you still want share your VBA macro see To distribute Microsoft Outlook VBA code to other users for possible solutions.

Package Outlook VBA Macro with Custom Button on ribbon

3 Answers3

Solution 1

Solution 2