Attach files to email with changing name

[Dave88]

I want to be able to attach files to an email, (Outlook), that the file name changes weekly.

For example the file name may be "Weekly whatever we 4.17" one week then "Weekly whatever we 4.24" the next week. The file name changes with the week ending date.

I want the macro to choose the most rescent week and attach to an email.

 

[bensonsearch]

I want to be able to attach files to an email, (Outlook), that the file name changes weekly.

For example the file name may be "Weekly whatever we 4.17" one week then "Weekly whatever we 4.24" the next week. The file name changes with the week ending date.

I want the macro to choose the most rescent week and attach to an email.

Hi And welcome.

I personally would love some more info on the above to help better. 4.17 and 4.24 are the month/date ending correct? what calls the macro or what conditions does it start? when you push something?

 

[Dave88]

Below is the macro I am using. Basiclly what it does is once we complete our last worksheet (report) we press a send button which opens up outlook and attaches all of the files that need to be sent. what i would like the macro to do is reconize the the files with the most rescent date.

For example each week we complete a thermostat report, and we name it "thermostat mm.dd". Each week the mm.dd changes. Not sure if this is even possible.

Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "gm307"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
.Attachments.Add "C:\Documents and Settings\manager\Desktop\Thursday\ALD Report1.0.xls"

.Attachments.Add "C:\Documents and Settings\manager\Desktop\Thursday\thermostats.xls"
.Attachments.Add "C:\Documents and Settings\manager\Desktop\Thursday\weekly wci with spoilage.xls"
.Attachments.Add "C:\Documents and Settings\manager\Desktop\Thursday\lift inspection.doc"
.Attachments.Add "C:\Documents and Settings\manager\Desktop\Thursday\seat inspections.doc"

.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[bensonsearch]

i would use a variable to store names of sheets and search via the created date with below

Dim oFS As Object
Dim strFilename As String

strFilename = 'what your file is called before the we ending

'This creates an instance of the MS Scripting Runtime FileSystemObject class
Set oFS = CreateObject("Scripting.FileSystemObject")

MsgBox strFilename & " was created on " & oFS.GetFile(strFilename & "*").DateCreated



Set oFS = Nothing


then use it in an if statement to find the last 7....

the other idea would be to count or add 7 days from the last known file (stored in a text file then updated once it has been attached) and work from that