Macro with email and condition

[Rachel.S]

Hi, thanks for the help I've received on the forum before.

I've got a new task...

What I have: a macro that extract information from several workbooks and collates the information in a new tab. (Thanks goes to http://www.rondebruin.nl/fso.htm for the code that has been already slightly amended)

What I need: People are inputting information into the spreadsheets and I'd like a macro to send an email to people if they haven't updated their spreadsheet for two weeks.

The possible code for sending it(we use outlook) that I've found is:
Sub SendEmail()

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

' Check to see if there's an explorer window open
' If not then open up a new one
OutOpen = True
Set myExplorer = OlApp.ActiveExplorer
If TypeName(myExplorer) = "Nothing" Then
OutOpen = False
Set myNameSp = OlApp.GetNamespace("MAPI")
Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
Set myExplorer = myInbox.GetExplorer
End If
'myExplorer.Display ' You don't have to show Outlook to use it

' Create a new mail message item.
Set NewMail = OlApp.CreateItem(olMailItem)
With NewMail
'.Display ' You don't have to show the e-mail to send it
.Subject = "Look at this!"
.To = "name@wherever.com"
.Body = "This is a demonstration"
End With

'NewMail.Send
If Not OutOpen Then OlApp.Quit

'Release memory.
Set OlApp = Nothing
Set myNameSp = Nothing
Set myInbox = Nothing
Set myExplorer = Nothing
Set NewMail = Nothing

End Sub


(Thankstohttp://p2p.wrox.com/excel-vba/36992-send-mail-thru-excel-macro.html)
------------------

Could I use and put in a function so that if when using the macro a sheet has not been updated/ opened/ etc. then the email is sent out? or eqiv.

Current Code:

Dim FirstCell As String
Sub MergeAllWorkbooks()

Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long

' Change this to the path\folder location of your files.
MyPath = "F:\Services\Organic Search Services\Organic Folder\Human Resources\TimeSheets\MASTER TIMESHEET\STAFF TIMESHEETS"

' Add a slash at the end of the path if needed.
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

' If there are no Excel files in the folder, exit.
FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' Fill the myFiles array with the list of Excel files
' in the search folder.
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

' Set various application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

' Add a new workbook with one sheet.
Set BaseWks = ActiveWorkbook.Worksheets(1)
rnum = 2

' Loop through all files in the myFiles array.
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MyPath & MyFiles(FNum))
On Error GoTo 0

If Not mybook Is Nothing Then
On Error Resume Next

' Change this range to fit your own needs.
With mybook.Worksheets(1)
FirstCell = "A2"
Set sourceRange = .Range(FirstCell & ":" & RDB_Last(3, .Cells))
' Test if the row of the last cell is equal to or greater than the row of the first cell.
If RDB_Last(1, .Cells) < .Range(FirstCell).Row Then
Set sourceRange = Nothing
End If
End With

If Err.Number > 0 Then
Err.Clear
Set sourceRange = Nothing
Else
' If source range uses all columns then
' skip this file.
If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
Set sourceRange = Nothing
End If
End If
On Error GoTo 0

If Not sourceRange Is Nothing Then

SourceRcount = sourceRange.Rows.Count

If rnum + SourceRcount >= BaseWks.Rows.Count Then
MsgBox "There are not enough rows in the target worksheet."
BaseWks.Columns.AutoFit
mybook.Close savechanges:=False
GoTo ExitTheSub
Else

' Copy the file name in column A.
With sourceRange
BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).Value = Replace(MyFiles(FNum), ".xlsm", "")
End With

' Set the destination range.
Set destrange = BaseWks.Range("B" & rnum)

' Copy the values from the source range
' to the destination range.
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

rnum = rnum + SourceRcount
End If
End If
mybook.Close savechanges:=False
End If

Next FNum
BaseWks.Columns.AutoFit
End If



ExitTheSub:
' Restore the application properties.
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub


-------------------

Cheers
Rachel

 

[Rachel.S]

*Bump*

 

[ScottR]

Hi Rachel

You really need to wrap the CODE tags around your example code in your post to make it easier to read (looks like the hash character when composing).

That aside is there anything recorded in the target workbooks that indicates the last time data was entered or will you be using file properties - ie date modified?

 

[Rachel.S]

Hi Rachel

You really need to wrap the CODE tags around your example code in your post to make it easier to read (looks like the hash character when composing).

That aside is there anything recorded in the target workbooks that indicates the last time data was entered or will you be using file properties - ie date modified?

Ah, cheers, I shall do that next time,

I don't think there is, though I can always enter something if that will help... I was planning on using the date modified, I know its not the most accutate method but its only to gently remind people once in a white to update their timesheets...

cheers

 

[ScottR]

Then the easiest thing would be to record the date modified at the same time you are creating and importing the file list.

So add the holding variable modifying the line to read

Dim MyFiles() As String, MyDates() as Date

and your fill loop becomes

FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
ReDim Preserve MDate(1 To FNum)
MyFiles(FNum) = FilesInPath
MDate(FNum) = FileDateTime(MyPath & FilesInPath)
FilesInPath = Dir()
Loop

So you could check the detail and send the email if appropriate, I would recommend doing so just after closing the workbook in each case. Ie below this line

mybook.Close savechanges:=False

Do you have an interval in mind?

 

[Rachel.S]

Then the easiest thing would be to record the date modified at the same time you are creating and importing the file list.

So add the holding variable modifying the line to read

Dim MyFiles() As String, MyDates() as Date

and your fill loop becomes

FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
ReDim Preserve MDate(1 To FNum)
MyFiles(FNum) = FilesInPath
MDate(FNum) = FileDateTime(MyPath & FilesInPath)
FilesInPath = Dir()
Loop

So you could check the detail and send the email if appropriate, I would recommend doing so just after closing the workbook in each case. Ie below this line

mybook.Close savechanges:=False

Do you have an interval in mind?

cool. shall add that, the interval would be, say every two weeks...?