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

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.

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

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


"Lisab" wrote in message
...
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.

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

For the OP

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tim Zych" <tzy---ch@NOSp@mE@RTHLINKDOTNET wrote in message ...
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, "C").Value
or
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, 3).Value


"Lisab" wrote in message
...
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.

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

Perfect! Thank you both for your help.

"Ron de Bruin" wrote:

For the OP

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tim Zych" <tzy---ch@NOSp@mE@RTHLINKDOTNET wrote in message ...
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, "C").Value
or
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, 3).Value


"Lisab" wrote in message
...
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.

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

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.

"Ron de Bruin" wrote:

For the OP

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tim Zych" <tzy---ch@NOSp@mE@RTHLINKDOTNET wrote in message ...
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, "C").Value
or
EmailAddress = Sheets("EmailList").Cells(ActiveCell.Row, 3).Value


"Lisab" wrote in message
...
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.