Hi folx,

Perhaps u can help me?

I have a field C2 ").
With a click on a button, Outlook generates an email with Email-Adress,
Body, Subject, Attachment and Signatur.
But I want that the email-Adress for the To-Field is not given in the code,
I want for that the Email is taken from field C2.

Therefore I thought, that I can provide this field dynamic, so that VBA
takes the Name of the Button (in this Case C2) and after that it chooses the
field C2, lookes inside, takes the adress in C2 and puts it in the To-Field!

How can I get the name of the button if I have over 500 Buttons?
The Code should something like:
adress = Mebutton.name

But if I use "Me." it takes actually sheet1 and not my Button C2.

Do I want to much?
I know everything about that process but now idea how to query the the name
of the button in that button.
How do I get the name?

So I hope someone can help me.

Kindly
oener

Oener,
Without wanting to sound harsh, your "english" is a touch difficult to
understand!! If you are looking fro the value held in Cell "C2" on the
worksheet then you need something like this...

address = ThisWorkbook.Sheets(1).Range("C2").Value

If you want the button's Caption Value (ie what it says on the button)
then you need..

For a Forms button..

ThisWorkbook.Sheets(1).Shapes("But3").Select
address = Selection.Characters.Text

For a Control button...
address = ThisWorkbook.Sheets(1).CommandButton1.Caption

Hth,
OJ

Yeah I know.
My english in the morning is very bad.

But it seems that I wake up.
That's almost what I want, but not dynamic.
In the following code you are using the name of the button to get the name
of the button.:
address = ThisWorkbook.Sheets(1).CommandButton1.Name

But if I copy that code for each button I have to enter
address = ThisWorkbook.Sheets(1).CommandButton1.Name
address = ThisWorkbook.Sheets(1).CommandButton2.Name
address = ThisWorkbook.Sheets(1).CommandButton3.Name
and so on.

If you have more sheets and you want to copy that code for each sheet you
better use:
address = Me.CommandButton1.Name
In this case: Me is understood by VBA like "ThisWorkbook.Sheets(1)"

But what's the code if I don't want to enter each time the name of the button?
Is it something like that?:
address= Me.Mebutton.name

I hope my english improves ;)

"OJ" wrote:

Oener,
Without wanting to sound harsh, your "english" is a touch difficult to
understand!! If you are looking fro the value held in Cell "C2" on the
worksheet then you need something like this...

address = ThisWorkbook.Sheets(1).Range("C2").Value

If you want the button's Caption Value (ie what it says on the button)
then you need..

For a Forms button..

ThisWorkbook.Sheets(1).Shapes("But3").Select
address = Selection.Characters.Text

For a Control button...
address = ThisWorkbook.Sheets(1).CommandButton1.Caption

Hth,
OJ

Hi
your english is much better but I'm still not sure I follow.....could
you post some more of your code? Is this in a Commandbutton_Click
event?
Cheers,
OJ

Private Sub C2_Click()
Dim ool As Outlook.Application
Dim myattachments As Variant
Dim oMail As Outlook.mailitem
Dim myattach As Variant
Dim mydate As Variant
Dim mymonth As Variant
Dim myyear As Variant
Dim body As String
Dim oSignatur As String
Set fso = CreateObject("Scripting.FileSystemObject")
Dim sSourcePath As String, sDestPath As String
Dim ofile As Variant

oSignatur = "Kindly" & Chr(13) & "Oener "

body = "Ladies and Gentlemen" & Chr(13) & Chr(13) _
& "please find attached the new listing of our estimated requirements." +
Chr(13) + oSignatur

Set ool = CreateObject("Outlook.Application")
Set oMail = ool.CreateItem(olMailItem)
Set myattachments = oMail.Attachments

oMail.body = body

mymonth = "02"
myyear = Format(Date, "yyyy")
mydate = mymonth & "." & myyear
oMail.Subject = "Forecast: " & mydate

myattach = "\\nbbnig01\"

oMail.To = Me.Range("C2").Value 'here should stay:
Me.Range(Button.Name).Value
ofile = "file.xls"

On Error Resume Next
myattachments.Add myattach & "\actuel\" & ofile
oMail.Recipients.ResolveAll
oMail.Display
sSourcePath = myattach & "\" & "\actuel\" & ofile
sDestPath = myattach & "\2005\February\" & ofile
fso.MoveFile sSourcePath, sDestPath
End SubOK here is the Code:

So you see here, neraly everthing is dynamic. only the Name of the field is
not.

So I hope that helps.

"OJ" wrote:

Hi
your english is much better but I'm still not sure I follow.....could
you post some more of your code? Is this in a Commandbutton_Click
event?
Cheers,
OJ

Hi

The syntax

ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1 ).CommandButton1.Name).Val
ue

should in theory do. You can however not name a button the same as a cell,
so "C2" is out of the question.

500 commandbuttons means 500 separate Commandbutton_click events. That is
not efficient. You should use buttons from the Forms toolbar, assign them to
the very same single macro and use Application.Caller to decide which button
that was clicked.

HTH. Best wishes Harald


"Oener" skrev i melding
...
Yeah I know.
My english in the morning is very bad.

But it seems that I wake up.
That's almost what I want, but not dynamic.
In the following code you are using the name of the button to get the name
of the button.:
address = ThisWorkbook.Sheets(1).CommandButton1.Name

But if I copy that code for each button I have to enter
address = ThisWorkbook.Sheets(1).CommandButton1.Name
address = ThisWorkbook.Sheets(1).CommandButton2.Name
address = ThisWorkbook.Sheets(1).CommandButton3.Name
and so on.

If you have more sheets and you want to copy that code for each sheet you
better use:
address = Me.CommandButton1.Name
In this case: Me is understood by VBA like "ThisWorkbook.Sheets(1)"

But what's the code if I don't want to enter each time the name of the
button?
Is it something like that?:
address= Me.Mebutton.name

I hope my english improves ;)

"OJ" wrote:

Oener,
Without wanting to sound harsh, your "english" is a touch difficult to
understand!! If you are looking fro the value held in Cell "C2" on the
worksheet then you need something like this...

address = ThisWorkbook.Sheets(1).Range("C2").Value

If you want the button's Caption Value (ie what it says on the button)
then you need..

For a Forms button..

ThisWorkbook.Sheets(1).Shapes("But3").Select
address = Selection.Characters.Text

For a Control button...
address = ThisWorkbook.Sheets(1).CommandButton1.Caption

Hth,
OJ

I know I know.
It's not efficient. But sometimes I think it's my work not to do efficient
stuff. ;)

Anyway I can name my Button C2. The button works.
But which param can I use to mention noit the name of the button?

If I don't want to mention the name of my worksheet I use "Me." instead of
"ThisWorkbook.Sheets(1).".

But how do I do that for a button instead of for the sheet?


"Harald Staff" wrote:

Hi

The syntax

ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1 ).CommandButton1.Name).Val
ue

should in theory do. You can however not name a button the same as a cell,
so "C2" is out of the question.

500 commandbuttons means 500 separate Commandbutton_click events. That is
not efficient. You should use buttons from the Forms toolbar, assign them to
the very same single macro and use Application.Caller to decide which button
that was clicked.

HTH. Best wishes Harald


"Oener" skrev i melding
...
Yeah I know.
My english in the morning is very bad.

But it seems that I wake up.
That's almost what I want, but not dynamic.
In the following code you are using the name of the button to get the name
of the button.:
address = ThisWorkbook.Sheets(1).CommandButton1.Name

But if I copy that code for each button I have to enter
address = ThisWorkbook.Sheets(1).CommandButton1.Name
address = ThisWorkbook.Sheets(1).CommandButton2.Name
address = ThisWorkbook.Sheets(1).CommandButton3.Name
and so on.

If you have more sheets and you want to copy that code for each sheet you
better use:
address = Me.CommandButton1.Name
In this case: Me is understood by VBA like "ThisWorkbook.Sheets(1)"

But what's the code if I don't want to enter each time the name of the
button?
Is it something like that?:
address= Me.Mebutton.name

I hope my english improves ;)

"OJ" wrote:

Oener,
Without wanting to sound harsh, your "english" is a touch difficult to
understand!! If you are looking fro the value held in Cell "C2" on the
worksheet then you need something like this...

address = ThisWorkbook.Sheets(1).Range("C2").Value

If you want the button's Caption Value (ie what it says on the button)
then you need..

For a Forms button..

ThisWorkbook.Sheets(1).Shapes("But3").Select
address = Selection.Characters.Text

For a Control button...
address = ThisWorkbook.Sheets(1).CommandButton1.Caption

Hth,
OJ

"Oener" skrev i melding
...

If I don't want to mention the name of my worksheet I use "Me." instead of
"ThisWorkbook.Sheets(1).".

But how do I do that for a button instead of for the sheet?

As I said, use buttons from the Forms toolbar. Commandbuttons can't do what
you want as far as I know.

If you insist on writing awkward but still lazy code; create a macro that
use a "for i = 1 to 500" loop and write the same code 500 times to a
textfile, incrementing only the button number, possibly also the cell
address. Then paste that text into the VB editor.

What you should do, using one solution or another, is to have a single
"generate email" code and pass the recipient address to it from your click
macro(s). 500+ items of what you already have may be too big for a module,
it's a huge waste of space and impossible to do maintenance on.

HTH. Best wishes Harald

since you are using commandbuttons and you have to write 500 separate
events, one for each of the 500 buttons, then you know what the name of the
button is in each event procedure. in fact, you don't need to name your
button anything special. You know when it is clicked you have to get the
address from range C2 or whatever is the correct cell for that button.
There is no reason to make anything dynamic since your event code is
specific to the button clicked and therefore aligns with a single cell.
For this very reason, there is not object which holds the identity of the
commandbutton that was clicked; since it fires its own event.

If you replaced you buttons with buttons from the forms toolbar, then you
can write a single procedure and have it handled by a single routine.

There is a procedure for utilizing a class module to have a single event
fire for multiple buttons. This is documented by John Walkenbach and might
be the best solution to your problem:

go to
http://www.j-walk.com/ss/excel
on the left side select developer tips
on the next screen select userform tips
you will see an article for using a single procedure for multiple
commandbuttons.

--
Regards,
Tom Ogilvy



"Oener" wrote in message
...
I know I know.
It's not efficient. But sometimes I think it's my work not to do efficient
stuff. ;)

Anyway I can name my Button C2. The button works.
But which param can I use to mention noit the name of the button?

If I don't want to mention the name of my worksheet I use "Me." instead of
"ThisWorkbook.Sheets(1).".

But how do I do that for a button instead of for the sheet?


"Harald Staff" wrote:

Hi

The syntax


ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1 ).CommandButton1.Name).Val
ue

should in theory do. You can however not name a button the same as a
cell,
so "C2" is out of the question.

500 commandbuttons means 500 separate Commandbutton_click events. That
is
not efficient. You should use buttons from the Forms toolbar, assign
them to
the very same single macro and use Application.Caller to decide which
button
that was clicked.

HTH. Best wishes Harald


"Oener" skrev i melding
...
Yeah I know.
My english in the morning is very bad.

But it seems that I wake up.
That's almost what I want, but not dynamic.
In the following code you are using the name of the button to get the
name
of the button.:
address = ThisWorkbook.Sheets(1).CommandButton1.Name

But if I copy that code for each button I have to enter
address = ThisWorkbook.Sheets(1).CommandButton1.Name
address = ThisWorkbook.Sheets(1).CommandButton2.Name
address = ThisWorkbook.Sheets(1).CommandButton3.Name
and so on.

If you have more sheets and you want to copy that code for each sheet
you
better use:
address = Me.CommandButton1.Name
In this case: Me is understood by VBA like "ThisWorkbook.Sheets(1)"

But what's the code if I don't want to enter each time the name of the
button?
Is it something like that?:
address= Me.Mebutton.name

I hope my english improves ;)

"OJ" wrote:

Oener,
Without wanting to sound harsh, your "english" is a touch difficult
to
understand!! If you are looking fro the value held in Cell "C2" on
the
worksheet then you need something like this...

address = ThisWorkbook.Sheets(1).Range("C2").Value

If you want the button's Caption Value (ie what it says on the
button)
then you need..

For a Forms button..

ThisWorkbook.Sheets(1).Shapes("But3").Select
address = Selection.Characters.Text

For a Control button...
address = ThisWorkbook.Sheets(1).CommandButton1.Caption

Hth,
OJ