VBA Code to Search Filename with Wildcard

[*shudder*]

Hi Guys,

I am using the following macro to print my selection and attach to an email as a PDF document:

Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String
    Set Source = Nothing
    On Error Resume Next
 
    Const CoC   As String = "Currently on Cover"
 
    If Range("pQtr").Value <> CoC Then Range("pQtr").Value = CoC
 
    Application.ActivePrinter = "PDFCreator on Ne00:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "PDFCreator on Ne00:", Collate:=True
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 2
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Filename = ActiveWorkbook.Path & Application.PathSeparator & "Schedule - " & ActiveSheet.Range("L1").Value
    SendKeys Filename & "{ENTER}", False
 
    For Each cell In ThisWorkbook.Sheets("Presentation Template").Range("O4:O50")
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, -2).Value) = "yes" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ThisWorkbook.Sheets("Qtr Periods").Range("D3").Value & " - Fleet Presentation"
        .Attachments.Add ThisWorkbook.Path & "\Schedule - 2009.pdf"
        .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I would like the attachment coding to use a wildcard to select the file, somtething like:

.Attachments.Add ThisWorkbook.Path & "\Schedule - *.pdf"

or

.Attachments.Add ThisWorkbook.Path & "\Schedule - ????.pdf"

or alternatively take the same code as used to write the file as to open:

Filename = ActiveWorkbook.Path & Application.PathSeparator & "Schedule - " & ActiveSheet.Range("L1").Value

If none of the above are possible, can a prompt be created to open the destination folder so users can select the file themselves?

Thanks in advance

 

[Andrew Poulsom]

Have you tried?

.Attachments.Add ActiveWorkbook.Path & Application.PathSeparator & "Schedule - " & ActiveSheet.Range("L1").Value & ".pdf"

 

[*shudder*]

Have you tried?

.Attachments.Add ActiveWorkbook.Path & Application.PathSeparator & "Schedule - " & ActiveSheet.Range("L1").Value & ".pdf"

Yes, doesn't include the attachment though (no errors either)

 

[*shudder*]

Yes, doesn't include the attachment though (no errors either)

My apologise, this does work.

The problem was in the time taken for the macro to execute, I added a delay of 5 seconds before the Outlook Create Object line and works fine now.

Thanks for that