VBA-Open a specific file when the file name keeps changing with the date-CodePudding

I have a macro in Excel 2016 that currently works to open a specific email template. That template requires a form added as an attachment. My current code can open the file's location, but then the user has to find/select the correct file.

How do I get VBA to open the required form instead of just the folder? I know the file name & path, but the file name keeps changing with the version. All of the threads I could find involved known file names and unknown path. This is a known path and partially known file name due to the periodical nature of the file.

Ex. Today is October, but the most recent version is C:\filepath\Form_Sept_2021.pdf with previous dated versions already moved to an archive folder.

Sub Open_Template()

Dim myolapp As Object
Dim myitem As Object
Dim answer As Integer

answer = MsgBox("Do you still need to create the required form?", vbQuestion   vbYesNo   vbDefaultButton1, "Form Required")

Set myolapp = CreateObject("Outlook.Application")
myolapp.Session.Logon

'This is the email that requires a form attached

Set myitem = myolapp.CreateItemFromTemplate("C:\\filepath\email.oft")
myitem.Display

'This part needs modified to open the pdf file from the above example

If answer = vbYes Then
    Call Shell("explorer.exe" & " " & "C:\\filepath\" vbNormalFocus)
End If

End Sub

CodePudding user response：

Im thinking you could use a variable in your file name as suggested above. You could assign the variable to be the month (“Mmmm”) and then plug it in. You could use an if statement to test for current month then try going back a month.

CodePudding user response：

It could be something like this:

Dim LastMonth   As Date
Dim Period      As String
Dim FileName    As String

' Other code.

If answer = vbYes Then
    LastMonth = DateAdd("m", -1, Date)
    Period = "Form_" & Left(Format(LastMonth, "mmmm"), 4) & Format(LastMonth, "_yyyy")
    FileName = "C:\\filepath\\" & Period & ".pdf"
    Call Shell("explorer.exe" & " " & FileName, vbNormalFocus)
End If

Not sure about the double backslashes.