VBA open up pdf found in a subfolder of a subfolder

[zin1994]

Hello all,

First off I want to thank you for taking the time to read my post. Second off, if someone else has posted about this and come up with a solution, could you kindly send me a link to that post.

So before I start posting code I want to ask if what I want to do is possible. What I would like to do is to have a macro that goes into 3 sub folders deep and retrieves a pdf file and puts it on the attachment part of an email. The file path is going to be taken from a cell on an excel spread sheet. For this instance the file i want to attach will be found in a main folder and then it will need to go through the proper sub folders to find the exact pdf. for example my file path will be mainfolder\2016\march\8\thefileiwant.

As of now I have been able to attach a file with the .attachment code but it only finds files that are one folder deep I.E desktop\folder\thingiwant. however like I said before I want it to go through specific subfolders.

let me know if you have any questions. Thanks in advance!

 

[zin1994]

Sub Mail_Range_Row1()

Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim SendItem As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object


Set Source = Nothing
On Error Resume Next
Set Source = Range("A1:L50").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, please try again.", vbOKOnly

Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

Source.Copy

With Dest.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial Paste:=xlPasteValues
    .Cells(1).PasteSpecial Paste:=xlPasteFormats
    .Cells(1).Select
    Application.CutCopyMode = False
    
    End With
    
    

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With Dest
    
    On Error Resume Next
    With OutMail
    
   
    
    .to = "zcalbri@gmail.com"
    .CC = "" & Range("J17")
    .BCC = ""
    .Subject = "" & Range("E17")
    .Body = "" & Range("L17")
    SendItem = (C:/desktop/customers/customername/2016/march/8/test.pdf
    .Attachments.Add (SendItem)
    
    
    .Display
    
    End With
    
    On Error GoTo 0
    .Close savechanges:=False
    
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    With Application
    
    .ScreenUpdating = True
    .EnableEvents = True
    
    End With
    End Sub[CODE][CODE]

[/CODE]
[/CODE]

this is the code I am working with if this will help you understand what I am working with.
The problem is that the .attachment can't look all way into each folder to find the "test.pdf" file.