automatic email sent dependant upon cell change

[sdhutty]

Hi there,

I was wondering if its at all possible to automatically send an Email from Microsoft Outlook when a cell has changed.

The columns that apply to the cells are stated below. An email should only be generated if a cell change occurs from row 7 and below.

If column L changes then email send to Rob/Adrian indicating change in spreadsheet
If column N changes then email to send to Roger/Adrian indicating change in spreadsheet
If column P changes then email to send to Roger indicating all 3 parties have completed their approval.

If column S changes then email send to Rob/Adrian indicating change in spreadsheet
If column U changes then email to send to A Vale/Adrian indicating change in spreadsheet
If column W changes then email to send to A Vale indicating all 3 parties have completed their approval.

If column Z changes then email send to Rob/Adrian indicating change in spreadsheet
If column AB changes then email to send to M Redfern/Adrian indicating change in spreadsheet
If column AD changes then email to send to M Redfern indicating all 3 parties have completed their approval.

If column AG changes then email send to Rob/Adrian indicating change in spreadsheet
If column AI changes then email to send to A Buchanan/Adrian indicating change in spreadsheet
If column AK changes then email to send to A Buchanan indicating all 3 parties have completed their approval.

If column AN changes then email send to Rob/Adrian indicating change in spreadsheet
If column AP changes then email to send to R Court/Adrian indicating change in spreadsheet
If column AR changes then email to send to R Court indicating all 3 parties have completed their approval.


Also where would I enter the coding? the worksheet? the module?


The subject of the Email should read:

"Data Access spreadsheet change"

Body of email:

"All change has been made, please edit accordingly"

Thanks

Sam.

 

[portews]

Here's the code for determining the recipients. How you send the email depends on your email program, typically Outlook or Lotus Notes. I have Lotus Notes and am not familiar with Outlook programming (yet).

What else do you ant in the email? Do you want to attach the file, show a screenshot, put in a link?


Put this in the code page for the worksheet that will change:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
Dim recipient As String
[COLOR=#00ff00]'check for row 7 and above[/COLOR]
If Target.Row > 6 Then
    [COLOR=#00ff00]'see what column you're in and set the recipient[/COLOR]
    x = Target.Column
    Select Case x
    Case 12, 19, 26, 33, 40
        recipient = "Rob, Adrian"
    Case 14, 16
        recipient = "Roger"
    Case 21, 23
        recipient = "Vale"
    Case 28, 30
        recipient = "Redfern"
    Case 35, 37
        recipient = "Buchanan"
    Case 42, 44
        recipient = "Court"
    End Select
    
[COLOR=#00ff00]    'add Adrian for the middle columns[/COLOR]
    Select Case x
    Case 14, 21, 28, 35, 42
        recipient = recipient & ", Adrian"
    End Select


[COLOR=#00ff00]    'put the call to the email code here[/COLOR]

End If
End Sub