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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
2,217
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.
 

Forum statistics

Threads
1,186,169
Messages
5,956,342
Members
438,247
Latest member
UZev

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