Worksheet Change

[jennuhw]

I have a script set up to email a person when a column is changed. This email consists of various information in the row of the changed cell. It works fine for one change to the worksheet, but when multiple changes are done it only sends one email. It sends multiple emails if there has been enough time elasped between changes. Why would it do this? Is there a way around it?

 

[HowieG]

Could we see the code you're using?

 

[jennuhw]

Sure! Here it is!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Range("Y5", "Y5000&quot
For Each t In Target
If t <> &quot;&quot; Then
If UCase(t.Value) = &quot;X&quot; Then
Set objOutLook = CreateObject(&quot;Outlook.Application&quot
Set dn = t.Offset(0, -19)
Set espn = t.Offset(0, -21)
Set cc = t.Offset(0, -22)
Set objOutlookMsg = objOutLook.CreateItem(olMailItem)
With objOutlookMsg
.To = &quot;email@email_address.com&quot;
.Subject = &quot;Part Number &quot; + espn.FormulaR1C1 + &quot; Drawing Number &quot; + dn.FormulaR1C1 + &quot; Customer &quot; + cc.FormulaR1C1
.Body = &quot;Samples coming in soon.&quot;
.send
End With
Set objOMail = Nothing
Set objOLook = Nothing
t.Value = &quot;X &quot;
End If
End If
Next
End Sub

I had to make it change the value of 't' because it was sending an email for every X in column 'Y'. Let me know if you need more info. thanks!

 

[HowieG]

A couple observations:

1) It appears you have a couple typo's on your object names. You are setting two objects to Nothing at the end that don't actually exist in your code. I would strongly suggest you use Option Explicit at the beginning of your module and declare all objects are variables.

2) If you're having the routine loop through 4995 rows looking for X's, why have it trigger based on a record being changed. If you want to loop through them all, why not let the user enter as many X's as they want, then have a push button on the form that activates this code?

3) An alternative to 2) would be to check only the cell(s) that changed, checking if they are withing the Y... range and that they are an &quot;X&quot;, then executing your code.

Unfortunately, I don't have Outlook, so I can't test your code completely...

 

[jennuhw]

If I change the range to &quot;Y:Y&quot;, does that just check the cells with values? How would I go about only checking the cells that change? If I do that, would it end up checking every cell if a row is inserted? Thanks.

 

[HowieG]

The onchange event for the worksheet passes a range of cells that were changed.

Private Sub Worksheet_Change(ByVal Target As Range)

It can either be one cell or a range (inserting row, deleting 10 cells, etc.).

Rather than starting your routine with overriding this with the Y:Y or Y5:Y5000 range, just use the one the event is passing. Cycle through each of those cells, and check:
1) Is it in column Y?
and
2) Does it now have an &quot;X&quot; in it?

I would imagine using the .Column property of the range object would get you the answer to #1. You already have the code to check for #2.

This should be more efficient than checking 4995 rows each time, but I don't know if it will solve your initial problem. I personally would have chosen to have a push button to check for all X's and send all emails at one time, but I don't know enough about what you're trying to acomplish.

 

[SkipVought]

Hi,
This is not very hard. Your basic loop is thus...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
'assign the right column
    iTheRightColumn = Range("Y1").Column
    For Each t In Target
        With t
            If .Column = iTheRightColumn Then
                If UCase(.Value) = "X" Then
                    'do your eMail thing
                
                    'now get out and go home
                    Exit For
                End If
            End If
        End With
    Next
End Sub

VOLA Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

That works great, but it still isn't sending an email for each 'X' in column 'Y'. It sends the first one fine, but if I do one immediately afterwards, it won't send it. Skip, is there to generate each email then send them all at once (like at close)?

 

[SkipVought]

I do not understand.

are you saying that if you enter an X in Y5, it generates an eMail OK.

But then if you enter an X in Y6, it does not? Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

If I enter an X in Y5, it sends the email. Then, if I enter an X in Y6 or any other cell, it won't send. If I wait about 30 seconds and enter an X in Y6, then it will send. I am not sure if it is waiting for code to finish or what. Thanks.

 

[SkipVought]

I don't know if there is some sort of delay occuring. If the code is executing, you should not be able to update the spreadsheet.

I do know that starting Outlook takes time.

Try putting a BREAK in your Worksheet_Change code to see if you are getting into it the second time. Maybe you need a timing loop before calling Outlook.

Maybe you ought to think about starting Outlook in the background when the workbook opens and closing it when the workbook closes. I can't tell you how to do it. If you do, you might also want to check to see if Outlook is already open. There is a way to make an entry in the Registry that yours is another application using Outlook and NOT to close it in that event. If YOU are the only one to use this application, then this sort of trap is not really necessary.

Just throwing out some ideas. Skip,
metzgsk@voughtaircraft.com