Share an excel file on onedrive/sharepoint with vba

Hi All,

I am struggling to find the code necessary to do the following (if it actually exists). I would like to share a file (xlsx) that is on onedrive/sharepoint (desktop version) with a specific person i.e through email address. I know how to email a file as an attachment but I would prefer it to be a file saved on onedrive/sharepoint so that I can update it from my end constantly. I need to do this automatically as there are approximately 800 files needing to go to 800 different pupils.

Many thanks in advance for your help.

Why not simply share the file from within the program? Use the Share button top right to generate a link. Adjust the settings if you want the co-workers to be able to edit the file, then E-mail them all the link. You could probably record a macro and then adapt the code to do this.

Thanks for your reply! I would have to do this individually for all 800+ files as they are personal to each pupil. Unfortunately the record a macro does not record the sharing of the file.

Ah, I see.

Why not mail merge the data they need from the master file and just send them an E-mail with that? What is the ultimate purpose here?

I am a teacher, too.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Since you are a teacher I imagine you will understand. My aim is to set up a personalised spreadsheet for each pupil with a topic list on that is RAG rated for that pupil, which in turn is automatically updated from my QLA (question level analysis) spreadsheets and that the pupil can view an up to date sheet at any point in time.

P.s I did press the reply button under your post on my phone so I’m not sure what happened, sorry!

Ah, I think it defaults to Reply With Quote on a phone - just delete the quote if it does.

I presume each pupil's file has a name that is generic plus a unique identifier? If so, then you can parse a field using the generic portion of the file URL plus the unique ID and mail merge this as a link into each of the E-mails you send.

You'll have to wait for a VBA expert with specific knowledge of Sharepoint to advise.