#1
|
|||
|
|||
Function breaks when directory switched to OneDrive
Hi all,
I was running a sub to create an Outlook email and attach the most recent Excel file from a folder. It was working great from our shared drive, but they updated us to OneDrive. I thought I could just enter the new directory in the code, but upon doing so, got a RunTime ERror 9 (Subscript Out of Range) for the following line of code: Code:
Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xls*"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0) Code:
Private Sub EmailwithRecentAttachment_Click() 'Variable declaration Dim oApp As Object, _ oMail As Object, _ Pth As String Dim StrBody As String 'Turn off screen updating Application.ScreenUpdating = False 'Thanks to SNB c00 = "C:\Users\me\OneDrive Location This Has Spaces Not Sure if That Is Problem\me\folder name also has spaces was not a problem before" Pth = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.xls*"" /b/s/a-d/o-d").stdout.readall, vbCrLf)(0) 'Create and show the outlook mail item Set oApp = CreateObject("Outlook.Application") Set oMail = oApp.CreateItem(0) StrBody = "<font size=""4"" face=""Calibri"" color=""black"">" & _ "Hi " & ActiveSheet.Range("B1").Text & ", <br><br>" & _ "Attached is my token log for this month." & _ "</font>" With oMail .Display .To = ActiveSheet.Range("E1").Text .CC = "" .BCC = "" .Subject = "My Token Log" .HTMLbody = StrBody & "<br>" & .HTMLbody .Attachments.Add Pth .Display End With 'Restore screen updating and release Outlook Application.ScreenUpdating = True Set oMail = Nothing Set oApp = Nothing End Sub |
Tags |
onedrive, path, vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Issue with line breaks and paragraph breaks in bullet point document | philiphunter7 | Word | 8 | 05-10-2020 11:51 PM |
Avoid continous breaks turn into page breaks | sponge | Word | 3 | 09-14-2017 12:11 PM |
directory: want to a ensure all three lines in a directory entry appear on the same page. | JON25T | Mail Merge | 3 | 12-22-2016 01:34 PM |
Excel function does not work in OneDrive for Business | Laibiang | Excel | 0 | 11-04-2014 07:48 PM |
Detecting Page Breaks in Mail Merge Directory | TimK9VB | Mail Merge | 6 | 11-20-2012 01:52 PM |