Macro when cell values change???

[jennuhw]

I am wanting to create a macro that will automatically know when cell values change (in 3 specific columns) and send an email with the cell values that changed. I am sure this is possible, but I have no idea where to start! Can someone please direct me in the right direction? Thanks!

 

[acron]

What you want is certainly possible, by using the Worksheet_Change event. However, for the best approach some addition data might help. Do you want to send a email any time a single cell changes value in the target columns?, or is there someother way of knowing when the email be sent. Also would need to know your email client, i.e Outlook etc.

AC

 

[jennuhw]

Anytime a cell in column G for instance has a value, I need a email sent to certain recipients in Outlook. If more than one cell gets changed in a session, I would only need one email. I just need it for a specified column not the whole worksheet. Is that possible with the worksheet_change event? Thanks for your help so far!

 

[acron]

Try the following as a start. This should cause Outlook to email the contents of any cell that changes in column G. As it is, it will only work with a single cell. If you need to wait until a number of cells are changed, a different approach might be needed.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim objOLook As New Outlook.Application
Dim objOMail As MailItem
If Not Intersect(Target, ActiveSheet.Range("G:G&quot) Is Nothing Then
Set objOLook = New Outlook.Application
Set objOMail = objOLook.CreateItem(olMailItem)
With objOMail
.To = "Addressee"
.Subject = "Subject"
.Body = "Message" & Target.Value
.Send
End With
Set objOMail = Nothing
Set objOLook = Nothing
End If
End Sub

You will need to include a valid recipient name in place of Addressee, and you might want to include some message in place of "Message"

AC

 

[jennuhw]

That works great! Thanks! This is what I ended up doing so it only sent an email everytime and 'x' was entered:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, ActiveSheet.Range("Y:Y&quot) Is Nothing Then
If Target <> &quot;&quot; Then
If Target.Value = &quot;x&quot; Or Target.Value = &quot;X&quot; Then
Set objOutLook = CreateObject(&quot;Outlook.Application&quot
Set dn = Range(Target.Address).Offset(0, -19)
Set espn = Range(Target.Address).Offset(0, -21)
Set cc = Range(Target.Address).Offset(0, -22)
Set objOutlookMsg = objOutLook.CreateItem(olMailItem)
With objOutlookMsg
.To = &quot;address&quot;
.Subject = &quot;Part Number &quot; + espn + &quot; Drawing Number &quot; + dn + &quot; Customer &quot; + cc
.Body = &quot;Samples coming in soon.&quot;
.send
End With
Set objOMail = Nothing
Set objOLook = Nothing
End If
End If
End If
End Sub

 

[jennuhw]

With the code I used above, everytime an entire row is deleted I get an error &quot;Type Mismatch.&quot; The line
If Target <> &quot;&quot; Then
causes the error. Is there a way around this?

 

[SkipVought]

Hi,
The problem is that the code you have assumes that Target is a single cell. Target is a range and can have multiple values.

What you need to say is ....

Dim t as range
For Each t in Target
   If t <> &quot;&quot; then
      'I have data - do my thing
       If UCase(t.Value) = &quot;X&quot; Then
.....
      Exit For
   end if
Next

Hope this helps ;-)
Next Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

Hey, thanks! I will try that when I get a chance next week. One question, I am assuming that the UCase(t.value) takes the uppercase value of t, right? Does it matter if t is already in uppercase? If not, that is great!! You are so smart that you amaze me!!

 

[acron]

You are correct regarding UCase, and it does not matter if it is already in uppercase.

AC

 

[jennuhw]

That works great! A big thanks to the both of you!!

 

[jennuhw]

I have implemented the code into my spread sheet, but now whenever any change happens it kicks out an email for ever 'X' in column Y. I only want it to do it for the new 'X' that is entered. Is that possible?