Using VBA to get a File’s Last Modified Date

It is sometimes useful to know the date and time a file was last modified.  this is particularly useful if you have a folder full of similar files and need to use the latest copy.

Today the code I’m going to share below enables you to pass a file address into a function which will return the date and time it was last saved.  The example below passes a file address to the function, which returns the modified date, this is then displayed in a message box.

The function requires a reference to Microsoft Scripting Runtime within the VBE.

Sub MyMacroRoutine()
    Dim ModDate As Date
    ModDate = FileLastModDate("C:\YourFileAddressGoesHere")
    MsgBox ModDate
End Sub

Function FileLastModDate(strFileAddress As String) As Date
' In the VBE, set a reference to Microsoft Scripting runtime

    Dim fso As Scripting.FileSystemObject
    Dim fsof As Scripting.File
    Dim strPath As String

    Set fso = New FileSystemObject
    strPath = strFileAddress
    Set fsof = fso.GetFile(strPath)
    With fsof
        FileLastModDate = .DateLastModified
    End With

    Set fso = Nothing
    Set fsof = Nothing

End Function

As always, if you have any questions do let us know via the comments section.

Outlook Email using VBA

The code I’m sharing with you today will enable you to send an email using VBA through  Outlook.  It uses early binding so you will need to use a reference to Outlook.  You may need to look into what version(s) Outlook will be available to your users. 

As well as the subject and message body the routine allows you to specify the To, Cc and Bcc fields within a standard email format. 

The example below shows the code to attach four files using four different formats: Insert the file, insert a shortcut to the file, Embed and OLE.

The code could easily be changed to loop through arrays or collections of values for many of these fields.

Sub SendAnEmailWithOutlook()
'-- Requires a reference to the Microsoft Outlook 8.0 Object Library or higher --

    ' Creates and sends a new e-mail message with Outlook
    Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
    Dim ToContact As Outlook.Recipient

    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set olMailItem = OLF.Items.Add ' creates a new e-mail message
    With olMailItem
        ' message subject
        .Subject = "This where the subject text goes..."

        ' add a recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as CC
        ToContact.Type = olCC
        ' add another recipient
        Set ToContact = .Recipients.Add("name@org.net")
        ' set latest recipient as BCC
        ToContact.Type = olBCC

        ' the message text with a line break
        .Body = "email message"

        ' insert attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , "filename goes here"
        ' insert shortcut
        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , "Shortcut to Attachment"
        ' embedded attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddeditem, , "Embedded Attachment"
        ' OLE attachment
        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , "OLE Attachment"

        .OriginatorDeliveryReportRequested = False ' True would be delivery confirmation
        .ReadReceiptRequested = False ' True would be read confirmation
        '.Save      ' saves the message for later editing
        '.Display   ' displays the email message for editing
        .Send       ' sends the e-mail message (puts it in the Outbox)

    End With

    Set ToContact = Nothing
    Set olMailItem = Nothing
    Set OLF = Nothing

End Sub

Hope the text wrapping doesn’t make it too awkward to read.
As always, if you have any questions do let us know via the comments section.

Edit (18-Dec-2010): Due to a ‘technical’ error this blog entry has changed from it’s original version.  The original explained how to send an email through Outlook using late binding, this post will be re-written and posted again soon.  Apologies for any inconvenience this may cause.