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.
That's relatively simple
Code: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
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)
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
adds it to the message. Change "C:\path\folder" to the actual folder you wish to get the latest file from.Code:.Attachments.Add LatestFile("C:\path\folder\")
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.
Code: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
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
You said "I am using VBA to launch Outlook from a WORD document"
Post your code that creates the message.
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 (..).
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
Code: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
Indeed, very good. I was using this VBA code for some time; works 100%. Brilliant! Thanks a lot!