How To Use VBA To Share Excel File With Fellow Office User

Question

How can one share an excel file with another office user using vba? I have a template that needs to be minimally modified and shared with hundreds of people as a separate workbook (same active directory).

Here is a table I have that details which people should have access to which files:

I thought I could use a sharing method to set the permissions using MSOPermission. I've tried quite a few approaches which have all failed, but these were the ones I was most optimistic would work:

wkBk.Permission.Add "bill_User@company.com",msoPermissionEdit
wkBk.Permission.Add "SallyCEO@company.com",msoPermissionRead
wkBk.Permission.Add "billy_companyID",msoPermissionEdit

The specific error I receive indicates something is wrong with the Permission Object. I can't find much documentation on this (a common pet peeve of mine with Microsoft).

I've seen a couple of posts shown below, but none address my question.

• Automate File sharing
• Share and unshare the file
• Permission sharing

I'm sure I'm in the wrong area or maybe I need to enable a library. I'm embarrassed to say that I even attempted to use the macro recorder, but no code was logged when I interacted with the sharing menu shown here.

Answer 1

PowerApps were referenced; if you have a basic O365 license and access to SharePoint Online, you can set access as necessary. Here is an example taken from a file in the SPO Documents library: