PDA

View Full Version : Email most recent file in a folder



Lartk
03-14-2013, 09:50 AM
Is there a way to change the below code to attach the most recent file in a folder instead of referencing a specific file name?

Sub EmailTest()
Const FILE_ATTACH As String = _
"G:\KL\Test.xls"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim Lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("test@aol.com")
oRecipient.Type = 1

.Subject = "Test"


If Dir(FILE_ATTACH, vbNormal) <> "" Then

.Attachments.Add ("G:\KL\Test.xls")
.Save
End If

End With

End Sub

snb
03-14-2013, 04:31 PM
Sub M_snb()
with CreateObject("Outlook.Application").CreateItem(0)
.to="test@aol.com (test@aol.com)"
.Subject = "Test"
.Attachments.Add split(createobject("wscript.shell").exec("cmd /c Dir G:\KL\*.xls /b /o-d").stdout.readall,vbcrlf)(0)
.Send
End With
End Sub

Lartk
03-15-2013, 07:47 AM
I tired the below but it says the script is out of range for the .attachments.add row

Sub M_snb()
With CreateObject("Outlook.Application").CreateItem(0)
.to =test@aol.com
.Subject = "Test"
.Attachments.Add Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\Katherine Lartigue\*.xls /b /o-d").stdout.readall, vbCrLf)(0)
.Send
End With
End Sub

snb
03-15-2013, 10:14 AM
Avoid spaces in foldernames:

Sub M_snb()
With CreateObject("Outlook.Application").CreateItem(0)
.to =test@aol.com (=test@aol.com)
.Subject = "Test"
.Attachments.Add Split(CreateObject("wscript.shell").exec("cmd /c Dir 'G:\Katherine Lartigue\*.xls' /b /o-d").stdout.readall, vbCrLf)(0)
.Send
End With
End Sub