Link to home
Start Free TrialLog in
Avatar of Carlandrewlewis
CarlandrewlewisFlag for United Kingdom of Great Britain and Northern Ireland

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-System-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
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Hi lewis,

What is the problem because i run both the macros in the workbook and they run without any problem...

Saurabh...
Avatar of Carlandrewlewis

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?
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???
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.Application")
    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