Email with OneDrive Link

[karldugan]

Hi all - I have some code that sends a spreadsheet as a OneDrive link however whenever any one other than me clicks the attached link they get an error message saying Unknown Error trying to lock file. I've tried troubleshooting the error message but nothing I've tried has resolved the issue.

I can manually share the file from excel and they can open it fine or they can navigate to the folder and open it from there, but I really want to make it as simple as clicking the file in the email. It has got to be in OneDrive as I need to keep version history.

Any help or guidance is appreciated.

The full code is below but I think the bit that is causing the issue is;

Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

'Sets the To based on who is the lead for that department
MyMail.To = Range("K1").Value
'Subject message set as the PO number and text is standard
MyMail.Subject = Range("K6").Value
MyMail.Body = "Please find link to purchase order for first approval"
'Attaches the file as an OneDrive link
MyMail.Attachments.Add Range("K4").Value, 7
'Sends the email
MyMail.Send

Full code from here

' Send PO for first approval.
'Saves the current workbook
ActiveWorkbook.Save
'Stops the screen from refreshing
Application.ScreenUpdating = False
'Unhides the audit sheet
Sheets("Audit").Visible = True
'Selects the audit sheet
Sheets("Audit").Select
'Finds the next blank row in column A and enters the date and time now
Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now()
'Finds the next blank row in column B and enters the username.
Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Application.UserName
'Hides the audit sheet
Sheets("Audit").Visible = False
'Unhides the lookups sheet
Sheets("Lookups").Visible = True
'Selects the lookups sheet
Sheets("Lookups").Select

Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

'Sets the To based on who is the lead for that department
MyMail.To = Range("K1").Value
'Subject message set as the PO number and text is standard
MyMail.Subject = Range("K6").Value
MyMail.Body = "Please find link to purchase order for first approval"
'Attaches the file as an OneDrive link
MyMail.Attachments.Add Range("K4").Value, 7
'Sends the email
MyMail.Send
'Hides the lookups sheet
Sheets("Lookups").Visible = False
'Turns back on screen refreshing
Application.ScreenUpdating = True
'Saves the current workbook
ActiveWorkbook.Save
End Sub