I have a macro that will send emails to a list of people in Column A (Name) and Column B (Email address) with attachments that are listed in the same row, Column C:Z (FilePath & FileName with extension). This process uses code from Ron DeBruin (Mail a different file(s) to each person in a range) and works great.
My Sheet would look something like this:
What I need help with is updating the list of files in Sheet 1 using a partial match on the FilePath/FileName_LatestFile.....with the LatestFile being the one with the most recent date (YYYYMMDD) or latest CreationDate. So if file C:\FolderZ\SubFolder1\TestFile2_20221031.xlsx has a more recent version in the folder/subfolder (ie C:\FolderZ\SubFolder1\TestFile2_20221101.xlsx), then insert that in the cell (C2 in this case). Hope that makes sense.
I have found lots of vba to get all the files in a directory/subdirectory (or partial list by file extension), but nothing where I can update an existing 'list' using a partial match and most recent file. Is this possible?
Thanks,
Don
My Sheet would look something like this:
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | D | D@abc.com | C:\FolderA\SubFolder1\TestFile_20221101.xlsx | C:\FolderZ\SubFolder1\TestFile2_20221031.xlsx | C:\FolderA\SubFolder2\TestFile3_20221103.xlsx | ||
2 | J | J@abc.com | C:\FolderZ\SubFolder1\TestFile2_20221031.xlsx | C:\FolderA\SubFolder2\TestFile3_20221103.xlsx | |||
3 | J | J@abc.com | C:\FolderA\SubFolder1\TestFile_20221101.xlsx | ||||
4 | L | L@abc.com | C:\FolderA\SubFolder2\TestFile3_20221103.xlsx | ||||
5 | T | T@abc.com | |||||
6 | K | K@abc.com | |||||
7 | D | D@abc.com | |||||
8 | B | B@abc.com | |||||
9 | R | R@abc.com | |||||
Sheet1 |
What I need help with is updating the list of files in Sheet 1 using a partial match on the FilePath/FileName_LatestFile.....with the LatestFile being the one with the most recent date (YYYYMMDD) or latest CreationDate. So if file C:\FolderZ\SubFolder1\TestFile2_20221031.xlsx has a more recent version in the folder/subfolder (ie C:\FolderZ\SubFolder1\TestFile2_20221101.xlsx), then insert that in the cell (C2 in this case). Hope that makes sense.
I have found lots of vba to get all the files in a directory/subdirectory (or partial list by file extension), but nothing where I can update an existing 'list' using a partial match and most recent file. Is this possible?
Thanks,
Don