0

I have an excel file that will be moved around a sharepoint drive to different folders that will have a number of PDFs in them. The purpose of this file is to do some analysis, then hit a button that creates an email and attaches all the PDF's in the folder its in to the email.

I am receiving an error when attempting to loop through all the files in the sharepoint folder on making the path a directory.

I get: "Bad file name or number"

Code:

Sub deal_email()

Dim summary_ws As Worksheet, email_ws As Worksheet
Dim to_email As Variant, cc_email As Variant, bb_email As Variant, sxs_email As Variant, email_body As Variant
Dim path As String, trunc_path As String, pdf_name As String, attachment As String, file_name As String
Dim OutApp As Object, Outmail As Object

Dim tier As String
Dim location As String

Set summary_ws = ThisWorkbook.Worksheets("SUMMARY")
Set emaill_ws = ThisWorkbook.Worksheets("EMAIL")

path = ThisWorkbook.path & "/"
path = Replace(path, "https:", "")
path = Replace(path, "/", "\")
path = Replace(path, " ", "%20")
file_name = Dir(path & "*.PDF") ' Error here!

Do While Len(file_name) > 0
    If Right(file_name, 3) = pdf Then
        pdf_name = file_name
    Else
    End If
Loop

The output of the variable Path is:

"\\lents-my.sharepoint.com\personal\jad_other_com\Documents\lett%20and%20course\rates%20cast\Inputs\check\"

I've tried with and without replacing white space with %20 to no avail

I have used the following references:

Excel's fullname property with OneDrive

Get the content of a sharepoint folder with Excel VBA

https://www.mrexcel.com/board/threads/loop-through-a-sharepoint-folder-and-open-in-excel.1176257/

https://www.pcreview.co.uk/threads/bad-file-name-or-number-accesing-sharepoint-folder.3895812/

0 Answers0