create email when the cell value changes

K

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 [email protected] 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

[email protected] employee1

[email protected] employee2

[email protected] employee3

[email protected] employee4

[email protected] employee5
 
J

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 = "[email protected]"

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
--------------------
 
Top