create email when the cell value changes

[KevinM]

I want to create an email when the value in column A changes. In column A I
have the email addresses of managers. When the email address changes I want
the VBA to compose the email and get the employees names in column B
associated to that email address. I don't want it 1 for 1. I want to take
only that one email address and bring in that range of employees in to the
email. So in the example below I want to email (e-mail address removed) and have
employees 1,2,and 3 in thebody of the email. I don't want seperate emails
for each employee going to the manager. Thanks



Example:

Column A Column B

(e-mail address removed) employee1

(e-mail address removed) employee2

(e-mail address removed) employee3

(e-mail address removed) employee4

(e-mail address removed) employee5

 

[joel]

I think you are asking for a lot of different things. first have yo
visited Ron Debruin's web article(s) on E-Mail?


'Ron's Excel Tips' (http://www.rondebruin.nl/tips.htm)


for looping through the employees who work for the same manager se th
find and findNext method like below

Sub GetEmployees()




VBA Code:
--------------------


ManagerEmail = "(e-mail address removed)"

Set c = Columns("A").Find(what:=ManagerEmail, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Manager : " & ManagerEmail)
Exit Sub
Else
FirstAddr = c.Address
Do
MsgBox ("Employee : " & c.Offset(0, 1))
Set c = Columns("A").FindNext(after:=c)
Loop While Not c Is Nothing And c.Address <> FirstAddr

End If


End Sub
--------------------