Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add a variable to this code 1

Status
Not open for further replies.

JrClown

Technical User
Oct 18, 2000
393
US
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 Cl[atom]wn


 
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

 
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 Cl[atom]wn


 
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

 
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 Cl[atom]wn


 
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")
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

 
[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 Cl[atom]wn


 
[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 Cl[atom]wn


 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top