+ Reply to Thread
Results 1 to 15 of 15

Find pdf file within subdirectory to attach to email

  1. #1
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Find pdf file within subdirectory to attach to email

    I have completed a macro in Excel that will go to a subdirectory, locate a specific pdf file from data in column A, and attach the pdf to an email. The files are kept in folders depending on the content, i.e., some of the files are in the INVOICES folder, some are in the SERVICES folder, some are in the INTERCOMPANY folder. The file will never be located in more than 1 folder.

    What I would like the macro to do is find the file and attach it to an email. The macro works if I provide the complete path but I would like to find the file REGARDLESS to the subfolder name within the 2021 INVOICES folder.

    Following is the path for the Services directory. There are also monthly subdirectories for INVOICES and INTERCOMPANY.

    C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\04-2021 Service Invoices

    I would like the macro to look in the subdirectories in the 2021 Invoices folder.
    Attached Files Attached Files
    Last edited by clorodet20607; 04-09-2021 at 10:53 PM.

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,804

    Re: Find pdf file within subdirectory to attach to email

    Couldn't look at your file due to restrictions my end, but maybe something like this?

    PHP Code: 
    Sub FindAndAttachFile()
    Dim OutApp As Object
    Dim OutMail 
    As Object
    Dim fso 
    As Object
    Dim FoundFile 
    As String
    Dim searchFile 
    As String
    Const SearchPath As String "C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\04-2021 Service Invoices"

    searchFile Sheet1.Range("A1").Value
    Set fso 
    CreateObject("Scripting.FileSystemObject")
    SearchFolders fso.GetFolder(SearchPath), searchFileFoundFile

    If FoundFile "" Then
        MsgBox 
    "File not found."
    Else
        
    Set OutApp CreateObject("Outlook.Application")
        
    Set OutMail OutApp.createitem(0)
        
    With OutMail
        
    .to "[email protected]"
        
    .Subject "File"
        
    .Body "Test!"
        
    .attachments.Add FoundFile
        
    .display
        End With
    End 
    If
    Set OutMail Nothing
    Set OutApp 
    Nothing
    Set fso 
    nothing
    End Sub

    Function SearchFolders(Folder As VariantsearchFile As StringFoundFile As String)
        
    Dim oFile As Object
        Dim SubFolder 
    As Object
        
        
    For Each oFile In Folder.Files
            
    If oFile.Name Like searchFile Then
                    FoundFile 
    Folder "\" & oFile.Name
                    Exit Function
            End If
        Next

        For Each SubFolder In Folder.SubFolders
            If FoundFile <> "" Then Exit Function
            SearchFolders SubFolder, searchFile, FoundFile
        Next
    End Function 
    Last edited by ByteMarks; 04-10-2021 at 08:32 AM.

  3. #3
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    Thank you ByteMarks can you please help me debug.
    I'm getting an error at ln 9, col 38 -- searchFile = Sheet1.Range("A1").Value
    I tried changing it to my sheet name -- searchFile = select email recipients.range("A2").value
    same debug error
    Last edited by clorodet20607; 04-10-2021 at 09:02 AM.

  4. #4
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,804

    Re: Find pdf file within subdirectory to attach to email

    Try changing it to
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    That worked but now --
    ln 11, col 63 -- SearchFolders fso.GetFolder(SearchPath), searchFile, FoundFile

  6. #6
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,804

    Re: Find pdf file within subdirectory to attach to email

    What happens then?

  7. #7
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    My apologies but I am really a novice vb user. The error message was "path not found". I double checked and corrected the path. Thank you
    The macro goes through now but returns "file not found". I double checked that each file is in one of the subfolders; however, the invoice # on line 1 is NOT in the SERVICE folder, it is in the INTERCOMPANY folder.

    Tried the following edit --
    Const SearchPath As String = "C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices\2021 Invoices\ & "*" -- that didn't work either.

    I'm trying to find the file regardless of it's location within the 2021 Invoices folder (a higher level in the directory tree).
    Last edited by clorodet20607; 04-10-2021 at 09:57 AM.

  8. #8
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,804

    Re: Find pdf file within subdirectory to attach to email

    Is the invoice number a valid file name with an extension?

  9. #9
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    yes, and all of the files are pdf's, i.e., 123456.pdf, 234568.pdf, 384013.pdf, ...

    I just saw your message that you couldn't access the excel file I attached. If you would like me to email it to you, let me know.
    Last edited by clorodet20607; 04-10-2021 at 11:14 AM.

  10. #10
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,804

    Re: Find pdf file within subdirectory to attach to email

    Maybe start further back in the directory?

    Const SearchPath As String = "C:\Users\christma-2\OurYear2Win\Documents\Clorodet\Accounts Receivable\SAP Invoices"

  11. #11
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Find pdf file within subdirectory to attach to email

    Due to possible sensitive data in your file, I will delete this solution that used your workbook and data. I tried posting the code but got the Access Denied message twice. This is the only forum that I have that issue with.

    Please let me know when you have downloaded so that I can remove the file for your security.

    https://www.dropbox.com/s/jsv9tzpq6y...mail.xlsm?dl=1

    In mMain module, run CreateEmail2().
    Last edited by Kenneth Hobson; 04-10-2021 at 08:46 PM.

  12. #12
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    Thank you so much ByteMarks and Kenneth.

    Kenneth, I can't figure out why the signature block in the email cuts off after 3 lines (the signature block has 6 lines). I compared it to the html body in my macro and it looks identical. I appreciate any assistance you can provide.

  13. #13
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Find pdf file within subdirectory to attach to email

    I was going to post shortened version and delete the commented .htmlBody line but got the Access error. Just uncomment the line above the short .htmlBody and delete the short version.

  14. #14
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Find pdf file within subdirectory to attach to email

    For those that want to try my method, here is a short test for the first file found part. See the commented thread for the aFFs() routine and supporting routine.

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-06-2021
    Location
    Accokeek, MD
    MS-Off Ver
    365
    Posts
    30

    Re: Find pdf file within subdirectory to attach to email

    I didn't realize the signature block was in 2 places. I uncommented as instructed. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. can't find file with macro in current subdirectory
    By xi603 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-28-2017, 06:47 PM
  2. Browse for a file edit the file then attach it to an email
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2016, 11:50 PM
  3. Attach a file that is open to an email
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-06-2016, 11:35 PM
  4. I need VBA to save file and attach file to email based on conditional statements
    By jcrowe31 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2014, 09:41 AM
  5. [SOLVED] Trying to create a .pdf and attach it to email, but get .xls file instead
    By sbrnard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2013, 01:21 PM
  6. [SOLVED] Attach a file and email it using Excel.
    By Paul Morgan in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2012, 09:32 AM
  7. Email workbook AND attach a separate file to the email
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2009, 06:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1