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

rudogg

New Member
Joined
Mar 18, 2022
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I'm trying to attach a file that is date and timestamped in the file name with this code.
VBA 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..
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
If it is within the same macro:

Rich (BB code):
    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::

VBA Code:
  Dim fName As String
  
  fName = Dir("C:\email\" & "Fender_Local_Inventory_" & "*" & ".csv")
  If fName <> "" Then
    .AddAttachment fName
  End If
 
Upvote 1
Solution
If it is within the same macro:

Rich (BB code):
    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::

VBA Code:
  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!
 
Upvote 1

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top