VBA code to track changes in workbook

[spittingfire]

Hi All,

I am new to this forum and this is my first post. I have a (see attached file) and what I will like to be able to do is to be able to have an email sent on exit to the user(s) whose schedule I made changes to. So for example if on Wednesday May 14th I added a vacation day for an employee, once I save and exit the workbook an email will be sent to that employee stating that the schedule has been updated.

Any assistance will be greatly appreciated and thanks in advance.

[westconn1]

you could have a global variable, either a range or array, to store the employees whose record is edited
i think a range would be better

update the variable in worksheet_change event, when employee detail is edited

in the before_close event you can send emails to all that have been added to the variable

something like
in a module

Public edited As Range

in the code pane for worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
If edited Is Nothing Then
    edited = Target
    Else
    edited = Union(edited, Target)
End If
End Sub

else use offset from target to specify employee

in the thisworkbook code pane

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cel In edited
   'do stuff to find employee from cel and send email
Next
End Sub

you have not detailed how you want the emails formatted with what data, or where the employee email addresses are stored
also do you want to use outlook to send emails, cdo.message, mailto or some other method
you might also need to test it the same employee has been updated several times and only needs one notification

[spittingfire]

Thanks for the tips but I think this is still a bit over my head to tell the truth. With regard to the employee email addresses I will have those stored on a separate sheet and there all use MS Outlook. What will generate an email will be any changes with the exception of SICK or BRVT. In terms of information in the email it will read "Your requested change(s) has been approved and the schedule has been updated". Whatever changes were made on the current day will only generate an email. One other this does this site offers an option to pay to have things like this built? I am more than willing to pay to have it done.

[westconn1]

to write a code, more information would be required
format of email to send
format of sheet containing email addresses

One other this does this site offers an option to pay to have things like this built?

don't know, most of just help (you to learn) for free, but if you want a full code that maybe different
i real terms this is a fairly simple project

[spittingfire]

Thanks westconn1 for your reply

I have modified the workbook and added an email tab with dummy email addresses. Also we use MS outlook for sending and receiving emails.

Format will be HTML for the email, and subject line will be "Your schedule has been updated."

[westconn1]

Format will be HTML for the email,

not what i meant, what content do you want in the email body?

test this to see if it fits your requirement

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved Then
    Set ol = GetObject(, "outlook.application") ' assumes outlook is already open
    For Each cel In edited
        Set msg = ol.CreateItem(0)
        msg.Subject = "Your schedule has been updated."
        ' change the below line to your own email address for testing
        Set r = msg.Recipients.Add(cel.Offset(, -cel.Column + 2) & "@test.com")
        r.Type = 1
        msg.Body = cel.End(xlUp) & vbNewLine & cel.End(xlUp).Offset(1) & vbNewLine & cel & vbNewLine & "rgds management"
        msg.display   ' look at your email before sending, remove when tested enough
        msg.send
    Next
    Set ol = Nothing
End If

End Sub

fix to code posted before

Private Sub Worksheet_Change(ByVal Target As Range)
If Not LCase(Target) = "sick" And Not LCase(Target) = "brvt" Then
    If edited Is Nothing Then
        Set edited = Target
        Else
        Set edited = Union(edited, Target)
    End If
End If
End Sub

still need variable in module as postd before

you sample email list is just employeename@comanyserver.com, the code reflects this and does not need to look it up
should this vary at all you would need a table of emails,
employee name in col A, email address in col B and some changes to code lookup the value, the single column does not allow for lookup

if you need more detail in the body, you would need to be more explicit

if you edit the same employee several times they would receive an email for each edit
possibly some combining of data could be achieved to make a single email for multiple edits

[spittingfire]

Thank you so much westconn1.

In testing I ran into an issue - when I put in SICK or BRVT on exit I get a debug error at "For Each cel In edited" - anyway around this error?

Other than that it works great - one other question if I want to add a CC or BCC to the email how will I do that?

[spittingfire]

In addition to the above issues I also came across one other issue in testing.

If I make a change then click save and then the "x" to exit an email is generated which is correct

However if I click the "X" first then click on save excel just exists without generating an email. Is there a way to make it generate the email on both methods of exiting?

Honestly other than those issues this is exactly what I was looking for. Again thanks a million westconn1.

[westconn1]

anyway around this error?

of course, what error?

if I want to add a CC or BCC to the email how will I do that?

try like

        Set r = msg.Recipients.Add(cel.Offset(, -cel.Column + 2) & "@test.com")
        r.Type = 1
        set r = msg.Recipients.Add("me@myserver.com")   ' addd extra recipients like
        r.type = 3  ' bcc

However if I click the "X" first then click on save excel just exists without generating an email. Is there a way to make it generate the email on both methods of exiting?

you did specify if it was saved, try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set ol = GetObject(, "outlook.application") ' assumes outlook is already open
    For Each cel In edited
        Set msg = ol.CreateItem(0)
        msg.Subject = "Your schedule has been updated."
        ' change the below line to your own email address for testing
        Set r = msg.Recipients.Add(cel.Offset(, -cel.Column + 2) & "@test.com")
        r.Type = 1
        msg.Body = cel.End(xlUp) & vbNewLine & cel.End(xlUp).Offset(1) & vbNewLine & cel & vbNewLine & "rgds management"
        msg.display   ' look at your email before sending, remove when tested enough
'        msg.send
    Next
    Set ol = Nothing
    Set edited = Nothing
End Sub

[spittingfire]

Thanks again westconn1 that works beautifully.

This last issue that I am still having is if I enter BRVT or SICK and then click on the "X" I get a Run-time error '424': Object required. Then after clicking on Debug it goes to "ThisWorkbook" and points to "For Each cel In edited"

[westconn1]

i am guessing that no other edits had been made, so change to

If not edited is nothing Then
    Set ol = GetObject(, "outlook.application") ' assumes outlook is already open
    For Each cel In edited
        Set msg = ol.CreateItem(0)
        msg.Subject = "Your schedule has been updated."
        ' change the below line to your own email address for testing
        Set r = msg.Recipients.Add(cel.Offset(, -cel.Column + 2) & "@test.com")
        r.Type = 1
        msg.Body = cel.End(xlUp) & vbNewLine & cel.End(xlUp).Offset(1) & vbNewLine & cel & vbNewLine & "rgds management"
        msg.display   ' look at your email before sending, remove when tested enough
'        msg.send
    Next
    Set ol = Nothing
    Set edited = Nothing
End If

[spittingfire]

Thanks westconn1 that did it!! Thanks for all your help and for being patient with me. We can now close this thread as solved!

[spittingfire]

Hi westconn1 I know that for all intent and purposes this issue is resolved, however I have one final request and it surrounds the wording of the body of the email. Will it be too much trouble to have the body of the email worded as:
"Hi name , your shift for day has been updated from old value to new value, regards."

So for example ""Hi Test1 , your shift for 23-May has been updated from 06:00 - 14:00 to OFF, regards."

[westconn1]

try like
msg.Body = "Hi " & cel.Offset(, -cel.Column + 2) & ", your shift for " & cel.End(xlUp) & " has been updated to " & cel & " regards"

none of the suggested code above keeps track of the previous value of the cell, so no from time at this point

[spittingfire]

Thanks again westconn1. That will do. I was able to create an audit sheet that will keep an audit of my changes (old values and new values) just in case I need to look back on something. Again I can't thank you enough for your help and assistance with this.