Send most recent file in folder from Excel

Question

I am trying to send the most recent PDF files in a folder from Excel using VBA.

I have managed to do it in Outlook VBA - I am not sure what needs to change to do it in Excel. The reason is because the Outlook macro conflicts with Excel macros that are running periodically.

My code at the moment just attaches all the files in a folder that have been created in the last 30 secs - only ever one PDF.

Please note that the code works perfectly in Outlook.

Sub SendFiles()
 Dim objMail As Outlook.MailItem
 Dim fso As Object
 Dim strFile As String
 Dim fsoFile
 Dim fsoFldr
 Dim dtNew As Date, sNew As String

Set fso = CreateObject("Scripting.FileSystemObject")

 strFile = "C:\temp\" 'path to folder

 Set fsoFldr = fso.GetFolder(strFile)
 dtNew = Now - TimeValue(00:00:30) '30 seconds ago

For Each fsoFile In fsoFldr.Files

If fsoFile.DateCreated > dtNew Then

sNew = fsoFile.Path

Set objMail = Application.CreateItem(olMailItem)

 With objMail
 .To = "[email protected]"
 .Subject = "Example"
 .BodyFormat = olFormatPlain
 .Attachments.Add sNew
 .Importance = olImportanceHigh
 .Send
 End With

End If
Next fsoFile

End Sub

Did you turn on reference to Outlook object library in Excel VBA? — vacip
– vacip, Commented Mar 28, 2017 at 10:35

user3598756 · Accepted Answer · 2017-03-28 13:31:15Z

1

some flaws:

you're not instantiating any Outlook application object

in an Excel environment, Application is pointing at Excel Application
TimeValue(00:00:30) should be TimeValue("00:00:30")

and be sure you have added Outlook library to your VBA project references: 1) click Tools -> References 2) scroll list box till Microsoft Outlook X.XX Object Library entry and toggle its check mark to select it 3) click "OK" button

then you could try this little refactoring of your code:

Option Explicit

Sub SendFiles()
    Dim objOutLook As Object
    Dim fso As Object
    Dim strFile As String
    Dim fsoFile
    Dim fsoFldr
    Dim dtNew As Date, sNew As String
    Dim newOutlookInstance As Boolean

    Set fso = CreateObject("Scripting.FileSystemObject")

    If GetOutlook(objOutLook, newOutlookInstance) Then

        strFile = "C:\temp\" 'path to folder
        Set fsoFldr = fso.GetFolder(strFile)
        dtNew = Now() - TimeValue("00:00:30") '30 seconds ago

        For Each fsoFile In fsoFldr.Files
            If fsoFile.DateCreated > dtNew Then
                sNew = fsoFile.Path
                With objOutLook.CreateItem(olMailItem)
                    .To = "[email protected]"
                    .Subject = "Example"
                    .BodyFormat = olFormatPlain
                    .Attachments.Add sNew
                    .Importance = olImportanceHigh
                    .Send
                End With
            End If
        Next
        If newOutlookInstance Then objOutLook.Quit '<--| quit Outlook if an already running instance of it hasn't been found
        Set objOutLook = Nothing

    Else
        MsgBox "Sorry: couldn't get a valid Outlook instance running"
    End If

End Sub



Function GetOutlook(objOutLook As Object, newOutlookInstance As Boolean) As Boolean
    Set objOutLook = GetObject(, "Outlook.Application")
    If objOutLook Is Nothing Then
        Set objOutLook = New Outlook.Application
        newOutlookInstance = True
    End If
    GetOutlook = Not objOutLook Is Nothing
End Function

edited Mar 28, 2017 at 13:31

answered Mar 28, 2017 at 10:51

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LBishop Over a year ago

Is it possible to have it look in 2 folders and then pick the recipient/message details depending on which folder a file was found in?

user3598756 Over a year ago

Sure it is. But this would deserve a brand new question along with your code attempts

Collectives™ on Stack Overflow

Send most recent file in folder from Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related