Send Email when updates are made

[flipcube]

Working on a macro that sends email when cells in a range reach 75%...no issue there.

here's the part I cannot figure out...Since this table will be updated weekly, different cells will reach 75% every week. how do i send an email to only reflect those items that change week over week.

I do not want the prior weeks items that are already at 75% or greater to be sent as part of the email....which sadly, the current macro does...it finds every instance of 75% (new or old)

i figure I can use a timestamp, but would I have to timestamp every cell in the range or just the worksheet?

...so many options, just can't choose the best solution.

Thanks in advance.

 

[circledchicken]

Hi,

Not sure I completely understand your problem but maybe use some indicator e.g.

• First week: After sending, colour the just sent cells blue
• Next week: Tell your code to ignore blue cells, and use the remaining +75% cells. Clear the existing blue cells and colour the just sent cells blue.
• Keep repeating 'Next week' pattern

 

[flipcube]

Hi CircledChicken,

I like your suggestion however, one of the requirements is that the code must highlight the rows yellow when the value of the cell (in the same row) is 75% or greater and stay yellow until the cell value reaches 100%. At which point the rows color changes to green. This code is already written and working.

After the sheet is updated, my manager sends an email out to all parties identifying the items that are at 75% or greater for that week. I have been asked to also automate this portion. I'm thinking with a command button.

The more I think about it, the more it seems the only way to achieve this is with a timestamp. Have the code timestamp each cell when it reaches 75% and use a loop to ignore anything < Date. something like...

Private Sub Email()
Dim lastweek as Date
Dim r12 as integer

r12 = 1
lastweek = Sheet1.Cells(r12,20) 'timestamp value

Do while lastweek < Date
r12 = r12 + 1
lastweek = Sheet1.Cells(r12,20)

if lastweek = Date then

--Send email code--

r12 = r12 +1
lastweek = Sheet1.Cells(r12,20)

End if
Loop
End sub

(not even sure code above would work...still learning)

I can't see any other way around it - which was the reason for the post - to get the VBA juices flowing and see if there was another way.

let me know if you come up with anything else

flipcube