Email with OneDrive Link

karldugan

New Member
Joined
May 10, 2016
Messages
47
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,222,246
Messages
6,164,800
Members
451,917
Latest member
WEB78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top