Easy Syntax Question - refer to current row & column to pull a val

[Lisab]

Although I am skilled at VB, I am still new to programming with Excel and am
having a problem figuring out the proper syntax for refering to a cell and
getting it's value to use in code.

1. I have a workbook with multiple worksheets.
2. The first worksheet is a list -> name, Email Address, and worksheet Name
3. each row has a command button that will run a macro to send that person
an email with the proper worksheet as an attachment. (code runs great - I got
it from here http://www.rondebruin.nl/mail/folder1/mail2.htm)

4. I want to customize this code by changing the hardcoding of the worksheet
name and email address

5. I need the syntax for referring to the current row (the row the command
button is being pressed from) and pulling the value from cell C (email
address)

I added the following dim statements:
Dim EmailAddress As String
Dim MailSheetName As String

and added the following statements: (tested and works fine)

EmailAddress = Sheets("EmailList").Range("C1").Value
MailSheetName = Sheets("EmailList").Range("D1").value

I would like to replace .Range("C1"). with the proper syntax to refer to the
current row column C. So if the command button in row 5 is pushed, the
values from C5 and D5 will be placed in the EmailAddress and MailSheetName.

 

[Tim Zych]

EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, "C").Value
or
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, 3).Value

 

[Ron de Bruin]

For the OP

See also the templates
http://www.rondebruin.nl/mail/templates.htm

 

[Lisab]

Perfect! Thank you both for your help.

For the OP

See also the templates
http://www.rondebruin.nl/mail/templates.htm

 

[Lisab]

Ron,

Thank you, I just happen to have three people on my list that would receive
the whole workbook and the link to your code is exactly what I need!

Thanks again.