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

employee1

employee2

employee3

employee4

employee5

I think you are asking for a lot of different things. first have you
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 the
find and findNext method like below

Sub GetEmployees()




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


ManagerEmail = "

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188874

http://www.thecodecage.com/forumz/chat.php