Text from a cell to a macro

[Keith.Jackson]

How can I pull text from a cell in a worksheet and use that text to name a file in a sub routine? For example , the name Bill Smith is in a cell in a worksheet but sometimes that cell is another name. I want to set up a macro to e mail a file to someone with the name in that cell as the file being e mailed.

Thanks

 

[tactps]

You will have some syntax that says "Sendto" or similar, like this:

.sendto "Bill Smith"

replace "Bill Smith" with [a1].text, where [a1] contains the name

eg

.sendto [a1].text

 

[Keith.Jackson]

How would you include the sheet name in the brackets using this method?

Thanks

 

[tactps]

Change it to:
.sendto sheets("Sheet1").range("a1").text

 

[Keith.Jackson]

How can I rename a file created in a macro based on a name in text in a cell on a worksheet?

 

[tactps]

Record a macro to "save as" a name:

The look at the macro and replace the name in quotes with:
sheets("Sheet1").range("a1").text
Leave the ".xls" in brackets and use ampersands

You'll end up with seomething like this:

ActiveWorkbook.SaveAs "c:\" & .sheets("Sheet1").range("a1").text & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Hope that helps