• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Send e-mail on change in cell

senthil murugn

New Member
Hi,

Column B has integer values and column C has name(s).
If any change occurs in B (row cell) then the corresponding persons in C row should be intimated
by finding their email id from Sheet2 table.
Column C could have more than one name, in that case all members should be intimated.

When tried it is returning empty string. Could you please help me out.
 

Attachments

senthil murugn

New Member
Hi,

Modified the code, now it is working for only one name match, but if more than one name it is not working.
Any help would be appreciated.

Thanks
 

Attachments

Ok, here's what's happening:

1) Your program ignores the Target argument for WorksheetChange and assumes that ActiveCell contains the only cell that changed. This is normally true, but not always; you should take a look at the advantages of using the argument Range. Still, that's not what's causing your problem here.

2) Your program looks up the active cell, then checks the value in column 3 of that row. In the case we're looking at, that value is "Robert, Smith".

3) Your program splits that on the comma, creating an array that contains "Robert" and " Smith". Note the leading space in the second value of the array.

4) Your program looks up "Robert" in Sheet2 and finds the correct email address. So far, so good.

5) You didn't include a MsgBox function to display the address inside the loop, though, so you may not have realized what's happening here.

6) Next your program looks up " Smith" in Sheet2 and there's an error; " Smith" doesn't exist in the table.

Here you need a way to handle the error. You included an On Error statement, but it simply says "Resume Next", which means to go on to the next statement without doing anything about the error. So the variable named "Var" still has row 5 in it, left over from the previous iteration of the loop, and again your program looks up row 5 in Sheet2 and finds "robert@abc.com". Again it doesn't display the address it found inside the loop; if it had, you would have seen it display "robert@abc.com" twice, once for each name, and that might have shown you where to look.

7) After the loop is completed, you display the final values for the MATCH row and the email address, perhaps giving you the impression that it looked up the value only once.

Here's what you need to make this program work a little better:

a) Change you On Error statement. The way I usually do it is this:
Code:
  On Error Goto ErrHandle
  .
  .
  .
  Exit Sub

ErrHandle:
  Set oerr = Err
  Stop
  Resume

  End Sub
When your program encounters the error trying to look up " Smith", it jumps to that label at the end of the program, sets an object variable to the Err object, and stops. Your program doesn't continue but alerts you, therefore, that there's problem; at this point you can look at the value in To_ID and see what happened. The Resume statement (rather than Resume Next) then directs the program to go back to the same statement and execute it again; that way you can fix the error and continue.

b) After you Split the names in column 3, before you look up each individual name with the MATCH function use Trim on that string to eliminate leading and trailing spaces. Sure, as the operator you can try to remember not to include a space after the comma; but the Trim function is easy to put into your program and the result is the program will work even if the operator makes that small mistake.

c) If you write a routine for error handling at the end of the program, as I did, don't forget to include an Exit Sub statement right before it; otherwise the program will execute those statements at the end even if the rest of the program worked correctly.
 
LOL. One thing you should be aware of: Sometimes your Worksheet_Change routine can be triggered, and do its thing, even if no actual change was made to your data. Two examples I can think of that would do this:

1a) If the operator types "AAA" into a cell and hits <Enter>, and the cell already had "AAA" in it, the value isn't different but it still triggers the Worksheet_Change event.

1b) This is really just a variant of 1b: If the operator hits <F2> on a cell, then changes his mind and leaves the value unchanged but hits <Enter>, again, your Worksheet_Change routine will run.

2) If the operator hits <Delete>, even if it's on a cell that had nothing in it, again the value is still the same (empty) but the event fires. Actually this can be handy: I have a worksheet with hundreds of email addresses and filenames, and I can highlight a bunch of rows in the Date column and hit <Delete>, upon which the program will look through each row, set up an email with named file attached, and fill in today's date in the Date field.

Just be aware that your routine might do its job even if no actual change occurred.
 
Top