VBA Attach a file to an email, where file name contains

[rudogg]

I'm trying to attach a file that is date and timestamped in the file name with this code.

    Dim dt As String, wbNam As String, Destfolder As String
    
    wbNam = "Fender_Local_Inventory_"
    dt = Format(CStr(Now), "yyyy-mm-dd-hh-mm-ss")
    Destfolder = "C:\email"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=Destfolder & "\" & wbNam & dt & ".csv", FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Set wkbk = Nothing
    
    MsgBox ("Hey Jerk! Your Fender Local Inventory File Has Been Saved In C:\email")

I'm trying to use this code or a form of it to attach the file that was created, since the text before the timestamp stays static.

.AddAttachment Dir("C:\email\" & "Fender_Local_Inventory_" & "*" & ".csv")

Any suggestions would super helpful..

 

[DanteAmor]

If it is within the same macro:

    Destfolder = "C:\email"
    wbNam = "Fender_Local_Inventory_"
    dt = Format(CStr(Now), "yyyy-mm-dd-hh-mm-ss")
    fName = Destfolder & "\" & wbNam & dt & ".csv"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    Set wkbk = Nothing
    
    .AddAttachment fName

If you are using another macro, the following will include the first file that matches the text::

  Dim fName As String
  
  fName = Dir("C:\email\" & "Fender_Local_Inventory_" & "*" & ".csv")
  If fName <> "" Then
    .AddAttachment fName
  End If

 

[rudogg]

If it is within the same macro:

    Destfolder = "C:\email"
    wbNam = "Fender_Local_Inventory_"
    dt = Format(CStr(Now), "yyyy-mm-dd-hh-mm-ss")
    fName = Destfolder & "\" & wbNam & dt & ".csv"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlCSV
    ActiveWorkbook.Close SaveChanges:=False
    Application.DisplayAlerts = True
   
    Set wkbk = Nothing
   
    .AddAttachment fName

If you are using another macro, the following will include the first file that matches the text::

  Dim fName As String
 
  fName = Dir("C:\email\" & "Fender_Local_Inventory_" & "*" & ".csv")
  If fName <> "" Then
    .AddAttachment fName
  End If

This is excellent. Works like a charm in both scenarios!