Microsoft Office Forums - View Single Post - Function breaks when directory switched to OneDrive

PrincessApril · 06-10-2020, 11:20 AM

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)

The full code I am using is here:

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

I see the shell window open briefly and then the RT error hits. Any ideas of how to get this working with OneDrive (I suppose it's OD for Business)