Using Excel macro to attach files in Outlook (without using full file name)

[Michael_Dowd]

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

[snb]

Use Dir + joker characters.

[Logit]

See if this helps : https://www.ozgrid.com/forum/index.php?thread/1227192-pulling-a-file-attachment-with-partial-name/

[Paul_Hossler]

Only finds the first one

Returns empty string if none found




Option Explicit


Sub drv()


'prefix + star + ext
MsgBox GetPathFromPartial("d:\testing", "Z1 DLVD (60-9)*.xlsm")


End Sub




Function GetPathFromPartial(P As String, F As String) As String
If Right(P, 1) <> Application.PathSeparator Then P = P & Application.PathSeparator


GetPathFromPartial = Dir(P & F)
End Function