Please help with Array comparison to trigger email

[vjsteele4]

I have spent 8 hours today trying to write code to solve my problem. After completely losing my mind I have decided to beg this community for some help. I would appreciate any help someone could give.

Workbook setup:
Excel 2010 workbook with multiple worksheets. My data entry will be made on worksheet 3 “Vehicle PC-SPEC Summary”. All data will be entered in column “F” (header is “GALC Reference Number” starting in cell “F9” and below. Data will be entered at various dates. Typical data entered would be in the following format: `9144003 with the number digits changing.

My request:
I would like for an email to be sent to john.doe at xyzdotcom stating “Please check file for update” when the data entered in sheet(3), column “F” has been updated (i.e. does not match the original data).

My thinking way:


When the workbook opens an array is created with the current data from sheet(3), column “F”.
New data is entered into column “F” of sheet(3) and saved.
The workbook is closed which triggers another array of the new data to be created.
The two arrays are then compared to each other. If the arrays are equal to each other, then close the workbook. If the two arrays do not match then send the Outlook email I requested above.


I was able to get the email to prompt each time I made a data entry, but I could be entering 50 data sets and do not want to get 50 emails popping up.
Thanks for any help you can provide.

[MINCUS1308]

It sounds like you accomplished the toughest part of the macro.
Now we might need to sprinkle some 'Exit Sub' statements.
If you could post the code you already have I bet We could make some slight modifications and get you back on track.

Just plop you code inside [CODE] -here- [CODE] after pressing the '#' symbol on the ribbon above.

[vjsteele4]

Thanks MINCUS1308. I will be able to post what I have come Monday. My laptop is at the office. All I have now is the code for the email and a trigger to send the email once data is entered. It's not working how I hoped because it sends an email each time I input data in the specified range. A

[MINCUS1308]

you'll have to forgive the lack of professionalism.
I may have been drinking heavily.
13311


Private Sub Workbook_Open()
'I DONT LIKE PEOPLE WATCHING ME
Application.ScreenUpdating = False

'INCONSPICUOUSLY CREATE A WS TO HOLD ORIGINAL DATA
On Error Resume Next
Application.DisplayAlerts = False 'ITS GOING TO TRY AND PROMPT THE USER - **** THAT
Sheets("Inconspicuous").Delete 'GOOD BY WITH THE OLD AND IN WITH THE NEW
ThisWorkbook.Sheets.Add.Name = "Inconspicuous" 'A NEW WORKSHEET
Application.DisplayAlerts = True 'JIC SOMETHING STUPID HAPPENS

'I GOTTA MOVE THE DATA
On Error GoTo a 'JIC IT DOESNT WORK
Sheets("Vehicle PC-SPEC Summary").Columns(6).Copy Destination:=Sheets("Inconspicuous").Columns(1)

'I HAVE TO SHOW YOU WHAT I DID :/
Application.ScreenUpdating = True

Exit Sub
a:
MsgBox "YOU DIDN'T MOVE THE ****ING DATA."
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'NEED TO FIGURE OUT WHERE TO STOP
On Error GoTo b
vLastRowInF = Sheets("Vehicle PC-SPEC Summary").Cells(Rows.Count, "F").End(xlUp).Row

'LOOP THROUGH THAT **** CHECKING EACH CELL
On Error GoTo c
For I = 9 To vLastRowInF 'LOL, MAKE SURE YOUR **** ALWAYS STARTS IN ROW 9
If Sheets("Vehicle PC-SPEC Summary").Cells(I, 6).Value <> Sheets("Inconspicuous").Cells(I, 1).Value Then
'THIS IS GOING TO COMPLIE THE MESSAGE FOR THE EMAIL
vMyEmailString = vMyEmailString & vbNewLine & "Vehicle PC-SPEC Summary Cell: " & Cells(I, 6).Address
Else
'NOT A DAMN THING
End If
Next I


'IM GOING TO SEND AN EMAIL
If vMyEmailString <> "" Then
SubSendMail (vMyEmailString) 'CALL SOMEONE ELSES CODE
End If


Exit Sub
'I HAVE ERRORS - FML
b:
MsgBox "FAILED TO FIND LAST ROW IN COLUMN"
c:
MsgBox "LOOP WRECKED"
End Sub


Sub SubSendMail(vMyEmailString As String)
'I HOPE THEIR **** WORKS
'I DONT HAVE OUTLOOK SO I CANT TEST THEIR CODE
'IT LOOKS LIKE IT SHOULD WORK THOUGH
'THE ONLY THING YOU CARE ABOUT IS THE strbody VARIABLE
'IF THIS CODE DOESNT WORK JUST PLACE THAT STRING INTO YOUR CODE

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


strbody = "The list below shows changes made in column F:" & vbNewLine & vMyEmailString & vbNewLine & vbNewLine & "Thanks, That is all."


On Error Resume Next
With OutMail
.To = "Ron.Freaking.Burgundy@KVWNChannel4.com"
.CC = ""
.BCC = ""
.Subject = "hey, something went terribly wrong with your file."
.Body = strbody
.Send
End With
On Error GoTo 0
End Sub

[MINCUS1308]

haha they auto censored my code thats freaking great.
i wonder if they did the actual file as well

[vjsteele4]

MINCUS1308, I love it! I always try to apply humor to everything I do. It makes it that much more enjoyable. I can't wait to run it just so I can email Ron!
thanks so much. I'll give it a try first thing Monday and let you know how it does.
Thanks again and stay classy!

[vjsteele4]

MINCUS1308 - I plugged the code into my workbook and it appears to be working great. I really appreciate your expertise and want to thank you again.
BTW, the unedited version is funnier.

[MINCUS1308]

haha any time. best of luck in your future coding adventures.