Carlandrewlewis
asked on
Excel - Email Notifications
I have a Training Schedule that EE has helped me put together, the problem i have is that whilst it was being designed and email notification added the sheet unbeknown to myself was being updated. So the problem i have is the fully working sheet (Training-Management-Syste m-REV08) has out dated and also deleted information in it and the updated information sheet (Training Management System.REV03) does not have the fully working email notifications. What i would like to do rather than cross referencing all the information is copy all the information from REV-03 into REV-08. However when i do this something happens along the way and the notifications doesn't work....Please advise!!
Training-Management-System-REV08.xls
Training-Management-System.REV03.xls
Training-Management-System-REV08.xls
Training-Management-System.REV03.xls
ASKER
When i run the Macro on my office pc it comes up with an error:
"Microsoft Office Excel is waiting for another application to complete an OLE action."
Any Ideas?
"Microsoft Office Excel is waiting for another application to complete an OLE action."
Any Ideas?
ASKER
Saurabh, i think i have sorted it...... The macro wasn't assigned correctly.
I have one question though!!! Is there a possibility to ammend the VBS so that when it sends a refresher email concerning one of the employees within the 'Wales Office' worksheet, can i send this to an additional email address???
I have one question though!!! Is there a possibility to ammend the VBS so that when it sends a refresher email concerning one of the employees within the 'Wales Office' worksheet, can i send this to an additional email address???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry to be a pain Saurabh, i have sorted it.... I have pasted the new script below to send the email to the other office and c.c. in our office:
Sub sendReminders()
sendRemindersForWorksheet Worksheets("Stevenage Office")
sendRemindersForWorksheet Worksheets("Wales Office")
End Sub
Sub sendRemindersForWorksheet( ByRef ws As Worksheet)
Dim row As Integer
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim toAddy As String
Dim ccAddy As String
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
If ws.Name = "Stevenage Office" Then
toAddy = "j.wilson@caswell-group.co .uk"
ccAddy = ""
Else
toAddy = "c.lewis@caswell-group.co. uk"
ccAddy = "j.wilson@caswell-group.co .uk"
End If
On Error GoTo cleanup
row = 6
Do
Dim col
For Each col In Array(6, 10, 14, 18, 22, 27)
If IsDate(ws.Cells(row, col).Value) Then
If DateDiff("m", Date, CDate(ws.Cells(row, col).Value)) <= 1 Then
If ws.Cells(row, col + 2).Value = "" Then ' 2nd Reminder already sent?
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = toAddy
.cc = ccAddy
.Subject = "Last Reminder for " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " for fresh-up training in " & ws.Cells(5, col)
.Body = "Dear Trainer" & vbNewLine & vbNewLine & _
"Please note that " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " is due for a fresh-up course in " & ws.Cells(5, col) & " on " & CStr(CDate(ws.Cells(row, col))) & "."
.Send
End With
ws.Cells(row, col + 2).Value = Date ' mark as sent
Set OutMail = Nothing
End If
ElseIf DateDiff("m", Date, ws.Cells(row, col)) <= 2 Then
If ws.Cells(row, col + 1).Value = "" Then ' 1st Reminder already sent?
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = toAddy
.cc = ccAddy
.Subject = "Reminder for " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " for fresh-up training in " & ws.Cells(5, col)
.Body = "Dear Trainer" & vbNewLine & vbNewLine & _
"Please note that " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " is due for a fresh-up course in " & ws.Cells(5, col) & " on " & CStr(CDate(ws.Cells(row, col))) & "."
.Send
End With
ws.Cells(row, col + 1).Value = Date ' mark as sent
Set OutMail = Nothing
End If
End If
End If
Next col
row = row + 1
Loop Until ws.Cells(row, 1) = ""
cleanup:
Set OutApp = Nothing
End Sub
Sub sendReminders()
sendRemindersForWorksheet Worksheets("Stevenage Office")
sendRemindersForWorksheet Worksheets("Wales Office")
End Sub
Sub sendRemindersForWorksheet(
Dim row As Integer
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim toAddy As String
Dim ccAddy As String
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
If ws.Name = "Stevenage Office" Then
toAddy = "j.wilson@caswell-group.co
ccAddy = ""
Else
toAddy = "c.lewis@caswell-group.co.
ccAddy = "j.wilson@caswell-group.co
End If
On Error GoTo cleanup
row = 6
Do
Dim col
For Each col In Array(6, 10, 14, 18, 22, 27)
If IsDate(ws.Cells(row, col).Value) Then
If DateDiff("m", Date, CDate(ws.Cells(row, col).Value)) <= 1 Then
If ws.Cells(row, col + 2).Value = "" Then ' 2nd Reminder already sent?
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = toAddy
.cc = ccAddy
.Subject = "Last Reminder for " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " for fresh-up training in " & ws.Cells(5, col)
.Body = "Dear Trainer" & vbNewLine & vbNewLine & _
"Please note that " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " is due for a fresh-up course in " & ws.Cells(5, col) & " on " & CStr(CDate(ws.Cells(row, col))) & "."
.Send
End With
ws.Cells(row, col + 2).Value = Date ' mark as sent
Set OutMail = Nothing
End If
ElseIf DateDiff("m", Date, ws.Cells(row, col)) <= 2 Then
If ws.Cells(row, col + 1).Value = "" Then ' 1st Reminder already sent?
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = toAddy
.cc = ccAddy
.Subject = "Reminder for " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " for fresh-up training in " & ws.Cells(5, col)
.Body = "Dear Trainer" & vbNewLine & vbNewLine & _
"Please note that " & ws.Cells(row, 1) & " " & ws.Cells(row, 2) & " is due for a fresh-up course in " & ws.Cells(5, col) & " on " & CStr(CDate(ws.Cells(row, col))) & "."
.Send
End With
ws.Cells(row, col + 1).Value = Date ' mark as sent
Set OutMail = Nothing
End If
End If
End If
Next col
row = row + 1
Loop Until ws.Cells(row, 1) = ""
cleanup:
Set OutApp = Nothing
End Sub
What is the problem because i run both the macros in the workbook and they run without any problem...
Saurabh...