Pulling a file attachment with partial name

  • Hi!


    I'm using VBA to send out an email with an attached PDF file. Currently the setup works great, where I'm able to send an attachment to different account contacts using file locations that are in Excel columns F and G, with the inputs being in the format:


    "C:\account folder\account date.pdf"


    (the date is a reference to a date in a separate cell)


    The problem I have is that now I'm trying to use column H to reference files with partial file names. I've tried to use wildcards like an asterisk and editing the cell to:


    "C:\account folder\account date" & "*01*" & ".pdf"


    The 01 here would be an account number and on these file names there could be a combination of other account numbers too, i.e. 01 + 02 or 01 + 03, but i still would want the formula to pick up this file name if it contains the 01 that occurs after the date and before .pdf.


    I've tried searching for a solution and it seems I would have to use a DIR function, but I'm not sure how best to input this in my VBA or if there's a better solution.


    Any suggestions?


    Thanks