Hi Vee,

Instead of
strto = Sheets("HPP").Range("F2").Value
strsub = Sheets("HPP").Range("A2").Value
strbody = Sheets("HPP").Range("J2").Value

try something like:
strto=sheets("HPP").cells(target.row,target.column-2)

strsub=sheets("HPP").cells(target.row,target.colum n-7)

strbody=sheets("HPP").cells(taget.row,target.colum n+2)

Hope this will help..



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

zantor wrote in message ...
Hi Vee,

Instead of
strto = Sheets("HPP").Range("F2").Value
strsub = Sheets("HPP").Range("A2").Value
strbody = Sheets("HPP").Range("J2").Value

try something like:
strto=sheets("HPP").cells(target.row,target.column-2)

strsub=sheets("HPP").cells(target.row,target.colum n-7)

strbody=sheets("HPP").cells(taget.row,target.colum n+2)

Hope this will help..



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/


hi
i don't understand please explain:

strto=sheets("HPP").cells(target.row,target.column-2)

strsub=sheets("HPP").cells(target.row,target.colum n-7)

strbody=sheets("HPP").cells(taget.row,target.colum n+2)

Hi Vee,

In your sub "Worksheet_Change(ByVal Target As Range)",
there is a parameter named 'Target', which has a 'Column' property as
well as a 'Row' property. So when you select a cell you can get the row
number as well as the column number of the selected cell.

If the current selected cell is "H2", then "Target.Row=2 " and
"Target.Column=8". If you want the email address in that row, then
"Target.Column-2" would give you column 6 or "F".

If "H2" is selected then:
Cells(Target.Row,Target.Column-2) means cells(2,6) or "F2"

Note that the 'Target' object is only valid in the sub where it is
declared, in this case "Worksheet_Change(ByVal Target As Range)"

Hope this helps..



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/