VBA macro to verify creation of new folder

excelnewbie2018

New Member
Joined
Jan 16, 2018
Messages
8
Dear community, I want to create a Macro to check everyday if a new new file has been added to a specific folder and notify me per email. I have seen some codes to check if a certain file given a specific name exists but none of them really answers my question.

I came up with the following, which anyway gives me a message box but not an email.

Sub LookForNew()<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> Dim n As String, msg As String, d As Date
msg
= ""
Set fso = CreateObject("Scripting.FileSystemObject")
Set fils = fso.GetFolder("C:\TestFolder").Files
For Each fil In fils
n
= fil.Name
d
= fil.DateCreated
If d >= Date - 1 Then
msg
= msg & n & vbTab & d & vbCrLf
End If
Next fil
If msg = "" Then
MsgBox
"No new files"
Else
MsgBox msg
End If
Set fso = Nothing
End Sub</code>I have also thought about creating a list of directories but I do not know what macro I could use to detect any modification. Can anybody help me with this?
Thank you in advance!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,071
whats your definition of New folder?
last hour?
yesterday?
last week? Its new, but made last week.
 
Last edited:

excelnewbie2018

New Member
Joined
Jan 16, 2018
Messages
8
whats your definition of New folder?
last hour?
yesterday?
last week? Its new, but made last week.

Dear Ranman256. I would like to be notificated everytime something is added to the folder I am monitoring.
I would say that everything created within the last hour would be considered new.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,844
Messages
5,766,744
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top