Consulting

Results 1 to 9 of 9

Thread: After starting the outgoing message in Outlook, add "most recent" file in folder

  1. #1
    VBAX Regular
    Joined
    Oct 2018
    Location
    Antwerp
    Posts
    28
    Location

    After starting the outgoing message in Outlook, add "most recent" file in folder

    When I composed an outgoing email message, THEN I want ta add a specific file that is LAST ADDED in a specific folder: most recent file (C:\path\folder\) to this message.
    Ho do I solve this issue?
    Most of the VBA codes uses a new message + add a file.
    I want to add 1 fixed file, that was most recent added.
    Thanks, Ward.

  2. #2
    That's relatively simple

    Sub SendMessage()
    Dim olItem As MailItem
        Set olItem = Application.CreateItem(olMailItem)
        With olItem
            .To = "someone@somewhere.com"
            .Subject = "This is the subject"
            .Attachments.Add LatestFile("C:\path\folder\")
            .Display
        End With
        Set olItem = Nothing
    End Sub
    
    
    Private Function LatestFile(strFolder As String, _
                        Optional strFilespec As String = "*.*") As String
    
    Dim strName As String
    Dim strRecent As String
    Dim dDate As Date
    
        Do Until Right(strFolder, 1) = Chr(92)
            strFolder = strFolder & Chr(92)
        Loop
        strName = Dir(strFolder & strFilespec)
        If strName <> "" Then
            strRecent = strName
            dDate = FileDateTime(strFolder & strName)
            Do While strName <> ""
                If FileDateTime(strFolder & strName) > dDate Then
                    strRecent = strName
                    dDate = FileDateTime(strFolder & strName)
                End If
                strName = Dir
            Loop
        End If
        LatestFile = strFolder & strRecent
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Oct 2018
    Location
    Antwerp
    Posts
    28
    Location
    Thanks you for your kind answer.
    The macro does not work.
    When I see the code, it has two parts.
    The first part is DIM etc ....(To...Subject...)
    The second part is a Private Function.
    Now, the issue is that I want to add (the most recent file of a folder), and add this in a Outlook message that has been composed.
    I am using VBA to launch Outlook from a WORD document, and AFTER the message has been build (To= automatic added by VBA in WORD, Subject = automatic added by VBA in WORD), last job is to add 1 file.
    Can you help me again?
    Thanks a lot.
    Ward (Belgium)

  4. #4
    The macro was for Outlook VBA. If you are creating a macro from Word then the code is similar, the LatestFile function gets the latest file and the code line
    .Attachments.Add LatestFile("C:\path\folder\")
    adds it to the message. Change "C:\path\folder" to the actual folder you wish to get the latest file from.
    The function must be in the same module as your macro.

    As you have not listed your macro code, I have created a simpe macro to create a message. The macro needs to start Outlook. To do that I recommend that you download the code from the link in the macro and copy it to a new module in the same project, to ensure that Outlook is started correctly.

    Sub Create_HTML_Mail()
    'Requires the code - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to either retrieve an open instance of Outlook or open Outlook if it is closed.
    Dim olApp As Object
    Dim oMail As Object
    
        Set olApp = OutlookApp()
        Set oMail = olApp.CreateItem(0)
        With oMail
            .BodyFormat = 2
            .To = "someone@somewhere.com"
            .Subject = "This is the subject"
            .Attachments.Add LatestFile("C:\path\folder\")
            .Display
        End With
    lbl_Exit:
        Set oMail = Nothing
        Set olApp = Nothing
        Exit Sub
    End Sub
    
    Private Function LatestFile(strFolder As String, _
                        Optional strFilespec As String = "*.*") As String
    Dim strName As String
    Dim strRecent As String
    Dim dDate As Date
    
        Do Until Right(strFolder, 1) = Chr(92)
            strFolder = strFolder & Chr(92)
        Loop
        strName = Dir(strFolder & strFilespec)
        If strName <> "" Then
            strRecent = strName
            dDate = FileDateTime(strFolder & strName)
            Do While strName <> ""
                If FileDateTime(strFolder & strName) > dDate Then
                    strRecent = strName
                    dDate = FileDateTime(strFolder & strName)
                End If
                strName = Dir
            Loop
        End If
        LatestFile = strFolder & strRecent
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Oct 2018
    Location
    Antwerp
    Posts
    28
    Location
    Thanks yo for your kind answer.
    .
    The Outlook-VBA code, which is needed, stops at line:
    .
    If FileDateTime(strFolder & strName) > dDate Then
    .
    I need (during my outgoing Outlook message), add the most recent file in a folder.
    This action has to be applied with the "outgoing outlook message" still open, as several files must be added.
    One of these files has te be (..).
    .
    Because of this working workflow, your VBA code was not so clear, as you coded
    .To = "someone@somewhere.com"
    . Subject = "This is teh subject"
    because the Outlook (new) message is already open in Outlook.
    .
    No need to add a WORD-VBA in ths stage. I was illustrating my workflow.
    .
    Thanks.
    Ward

  6. #6
    You said "I am using VBA to launch Outlook from a WORD document"
    Post your code that creates the message.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Regular
    Joined
    Oct 2018
    Location
    Antwerp
    Posts
    28
    Location
    No WORD VBA code is needed here.
    .
    The Outlook-VBA code, mensioned by your nice suggestion, which is needed, stops at line:
    .
    If FileDateTime(strFolder & strName) > dDate Then
    .
    I need (during composing my outgoing Outlook message), add the most recent file in a folder.
    This action has to be applied with the "outgoing outlook message" still open, as several files must be added.
    One of these files has te be (..).

  8. #8
    The function LatestFile works in either Outlook VBA and Word VBA to give you the last file in any named folder.

    You have said that you are creating a message from Word using VBA. The logical place to add the file is therefore to add the attachment using the macro that you use to create the message.
    If you want to add it to the open message from Outlook, then the Word macro must first have completed, and as you seem reluctant to post that macro code, it is not possible to determine that is the case.

    If that macro has finished, the following Outlook macro will add the last file from the named folder to the open message

    Sub AddLatestFile()
    'Graham Mayor - https://www.gmayor.com - Last updated - 28 May 2020 
    Dim olMsg As MailItem
        On Error Resume Next
        Select Case Outlook.Application.ActiveWindow.Class
            Case olInspector
                Set olMsg = ActiveInspector.currentItem
            Case olExplorer
                Set olMsg = Application.ActiveExplorer.Selection.Item(1)
        End Select
        olMsg.Attachments.Add LatestFile("C:\path\folder\") 'Change the path as appropriate
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Function LatestFile(strFolder As String, _
                        Optional strFilespec As String = "*.*") As String
    Dim strName As String
    Dim strRecent As String
    Dim dDate As Date
    
        Do Until Right(strFolder, 1) = Chr(92)
            strFolder = strFolder & Chr(92)
        Loop
        strName = Dir(strFolder & strFilespec)
        If strName <> "" Then
            strRecent = strName
            dDate = FileDateTime(strFolder & strName)
            Do While strName <> ""
                If FileDateTime(strFolder & strName) > dDate Then
                    strRecent = strName
                    dDate = FileDateTime(strFolder & strName)
                End If
                strName = Dir
            Loop
        End If
        LatestFile = strFolder & strRecent
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    VBAX Regular
    Joined
    Oct 2018
    Location
    Antwerp
    Posts
    28
    Location
    Indeed, very good. I was using this VBA code for some time; works 100%. Brilliant! Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •