Hello,
I'm working on a macro that should generate a multitude of emails each with a specific subject, BCC group, and attachments. My current macro requires the full file name for each attachment. I'm looking for a solution where the macro will search a specific folder, then grab and attach the file with only the first key words (since the name includes effective dates which change each month). Is there any way to program the Attachments.Add piece to grab a file from the first keywords (essentially having it search for Z1 DLVD (60-9) and pulling the file)?

Thank you in advance

Sub UMW_Pricelist_Distribution()
Dim emailApplication As Object
Dim emailItem As Object




Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
' Now build the email
emailItem.BCC = Worksheets("Pricelist").Range("G3").Value
emailItem.Subject = "Pricelist"
emailItem.Body = "Have a great weekend!"
' Attach any file from your computer. Use the full length location and name
emailItem.Attachments.Add ("F:\Pricing-Retail\Monthly\UMW\Current Month\BAXTER\Z1 DLVD (60-9) Effective 7-1 to 8-1.pdf")
' Send or display the email. Use .Send or .Display, NEVER use both
emailItem.Display