Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-10-2020, 11:20 AM
PrincessApril PrincessApril is offline Function breaks when directory switched to OneDrive Windows 10 Function breaks when directory switched to OneDrive Office 2019
Advanced Beginner
Function breaks when directory switched to OneDrive
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default 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)
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)
Reply With Quote
  #2  
Old 06-12-2020, 06:53 AM
PrincessApril PrincessApril is offline Function breaks when directory switched to OneDrive Windows 10 Function breaks when directory switched to OneDrive Office 2019
Advanced Beginner
Function breaks when directory switched to OneDrive
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

sorry to bump my own thread but just curious if any ideas on this one? Thank you all
Reply With Quote
  #3  
Old 06-14-2020, 05:34 AM
PrincessApril PrincessApril is offline Function breaks when directory switched to OneDrive Windows 10 Function breaks when directory switched to OneDrive Office 2019
Advanced Beginner
Function breaks when directory switched to OneDrive
 
Join Date: Nov 2019
Posts: 99
PrincessApril is on a distinguished road
Default

After further research it appears that it was as simple as adding a slash to the end of the directory location (must have deleted it when I pasted in my new path).
Reply With Quote
Reply

Tags
onedrive, path, vba

Thread Tools
Display Modes


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
Function breaks when directory switched to OneDrive Avoid continous breaks turn into page breaks sponge Word 3 09-14-2017 12:11 PM
Function breaks when directory switched to OneDrive 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
Function breaks when directory switched to OneDrive Detecting Page Breaks in Mail Merge Directory TimK9VB Mail Merge 6 11-20-2012 01:52 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft