How to add a variable to this code 1

[JrClown]

I have a cell where anyone can type an email address, and I want that email address to be the one used in this code. Any help will be appreciated.

Sub emailme()
ActiveSheet.Copy
ActiveWorkbook.SendMail "me@myisp.com ", "Hi this is a test"
ActiveWorkbook.Close False
End Sub

"The reward of one duty done is the power to fulfill another"
Jr Clwn
​

 

[petersdaniel]

Modify the function emailme() to be

Sub emailme(emailName)

ActiveSheet.Copy
ActiveWorkbook.SendMail """ & emailName & """, "Hi this is a test"
ActiveWorkbook.Close False
End Sub


When you call the function, you call it by sending it with the function...emailMe(A1).. assuming A1 is where your email address is located on the worksheet
petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[JrClown]

I'm getting a "Wrong number of arguments or invalid property assignment" error

"The reward of one duty done is the power to fulfill another"
Jr Clwn
​

 

[petersdaniel]

Try

Sub emailme(emailName as String)

ActiveSheet.Copy
ActiveWorkbook.SendMail emailName, "Hi this is a test"
ActiveWorkbook.Close False
End Sub
petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[JrClown]

I'm still getting the same error bro. Could you please type the code I need to use so I can try it? Thanks

"The reward of one duty done is the power to fulfill another"
Jr Clwn
​

 

[petersdaniel]

Ok,

I am not sure how you are laying out your spreadsheet or how everything is to work, but here is a simple example.

1. New excel workbook. On sheet1, put email address in cell A1.

2. In cell A2, put a CommandButton (from your Form toolbox). Right click on this and do View Code.

3. Write the following subroutine.

Private Sub CommandButton1_Click()

emailName = ActiveSheet.Range("A1&quot
ActiveSheet.Copy
ActiveWorkbook.SendMail emailName, "Hello world", False
ActiveWorkbook.Close False

End Sub



petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[JrClown]

[tt]Thank you very much petersdaniel. I appreciate the help.

It worked great but I used the control toolbox button.
[/tt]

"The reward of one duty done is the power to fulfill another"
Jr Clwn
​

 

[JrClown]

[tt]You know, I had to remove the ActiveSheet.Copy line because it kept crashing.

"I keep getting the "Copy method of worksheet class failed"
and I don't know if it's because I'm sharing the workbook.
[/tt]

"The reward of one duty done is the power to fulfill another"
Jr Clwn
​

 

[petersdaniel]

Don't know..worked fine here... petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein