Let me preface this by saying I am merely a hack but at the same time this
script works great as it is. What I am trying to do is have the person's name
become part of the file name that is being emailed here. Right now it is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name would come
from cell 'C8' from the 'TermTrans' titled sheet. My original script is below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " & Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

You have to save it as such

.SaveAs ThisWorkbook.Name & _
Sheets("TermTrans").Range("c8") & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans"

--
HTH

Bob Phillips

"JBL" wrote in message
...
Let me preface this by saying I am merely a hack but at the same time this
script works great as it is. What I am trying to do is have the person's
name
become part of the file name that is being emailed here. Right now it is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name would
come
from cell 'C8' from the 'TermTrans' titled sheet. My original script is
below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " & Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

That is close but it removes the name from the subject of my email, which I
want to keep. Also, it puts the file name as TermTrans.xlsJoeBlow6-21-05.xls.
I would like to shorten it to TermTrans-JoeBlow6-21-05.xls.

Thanks

"Bob Phillips" wrote:

You have to save it as such

.SaveAs ThisWorkbook.Name & _
Sheets("TermTrans").Range("c8") & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans"

--
HTH

Bob Phillips

"JBL" wrote in message
...
Let me preface this by saying I am merely a hack but at the same time this
script works great as it is. What I am trying to do is have the person's
name
become part of the file name that is being emailed here. Right now it is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name would
come
from cell 'C8' from the 'TermTrans' titled sheet. My original script is
below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " & Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi,

This code may help you in the direction you want. I understand it's a
little bit different of what you got, but It worked very well for me.

Hope it's uselfull

Joe


Code:
--------------------
Sub Send_Range()
Dim nombre, Asunto, Intro As String
' Select the range of cells on the active worksheet.
ActiveSheet.Range("B2:G49").Select
name= DatosEmailCotzn.email.Text
Subject= DatosEmailCotzn.Asunto.Text
Intro = DatosEmailCotzn.Intro.Text
If DatosEmailCotzn.Ccmail.Text = "inserte e-mail adicional (opcional)" Then
ConCopia = ""
Else
ConCopia = DatosEmailCotzn.Ccmail.Text
End If
' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.

With ActiveSheet.MailEnvelope
.Introduction = Intro
.Item.To = nombre
.Item.Subject = Asunto
.Item.CC = ConCopia
.Item.Send
End With
End Sub
--------------------




JBL Wrote:
That is close but it removes the name from the subject of my email,
which I
want to keep. Also, it puts the file name as
TermTrans.xlsJoeBlow6-21-05.xls.
I would like to shorten it to TermTrans-JoeBlow6-21-05.xls.

Thanks

"Bob Phillips" wrote:

You have to save it as such

.SaveAs ThisWorkbook.Name & _
Sheets("TermTrans").Range("c8") & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans"

--
HTH

Bob Phillips

"JBL" wrote in message
...
Let me preface this by saying I am merely a hack but at the same
time this
script works great as it is. What I am trying to do is have the
person's
name
become part of the file name that is being emailed here. Right now
it is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name
would
come
from cell 'C8' from the 'TermTrans' titled sheet. My original
script is
below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " &
Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub






--
jose luis
------------------------------------------------------------------------
jose luis's Profile: http://www.excelforum.com/member.php...o&userid=13312
View this thread: http://www.excelforum.com/showthread...hreadid=380969

.SaveAs Left(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4) & _
" " & Sheets("TermTrans").Range("c8").Value & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " &
Sheets("TermTrans").Range("c8").Value

--
HTH

Bob Phillips

"JBL" wrote in message
...
That is close but it removes the name from the subject of my email, which
I
want to keep. Also, it puts the file name as
TermTrans.xlsJoeBlow6-21-05.xls.
I would like to shorten it to TermTrans-JoeBlow6-21-05.xls.

Thanks

"Bob Phillips" wrote:

You have to save it as such

.SaveAs ThisWorkbook.Name & _
Sheets("TermTrans").Range("c8") & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans"

--
HTH

Bob Phillips

"JBL" wrote in message
...
Let me preface this by saying I am merely a hack but at the same time
this
script works great as it is. What I am trying to do is have the
person's
name
become part of the file name that is being emailed here. Right now it
is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name would
come
from cell 'C8' from the 'TermTrans' titled sheet. My original script
is
below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " &
Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Watch wrap-around

.SaveAs Left(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4) & _
" " & Sheets("TermTrans").Range("c8").Value & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " & _
Sheets("TermTrans").Range("c8").Value


--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
.SaveAs Left(ThisWorkbook.Name,Len(ThisWorkbook.Name)-4) & _
" " & Sheets("TermTrans").Range("c8").Value & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " &
Sheets("TermTrans").Range("c8").Value

--
HTH

Bob Phillips

"JBL" wrote in message
...
That is close but it removes the name from the subject of my email,
which
I
want to keep. Also, it puts the file name as
TermTrans.xlsJoeBlow6-21-05.xls.
I would like to shorten it to TermTrans-JoeBlow6-21-05.xls.

Thanks

"Bob Phillips" wrote:

You have to save it as such

.SaveAs ThisWorkbook.Name & _
Sheets("TermTrans").Range("c8") & _
" " & strdate & ".xls"
.SendMail MyArr, "Term/Trans"

--
HTH

Bob Phillips

"JBL" wrote in message
...
Let me preface this by saying I am merely a hack but at the same
time
this
script works great as it is. What I am trying to do is have the
person's
name
become part of the file name that is being emailed here. Right now
it
is
TermTrans.xls 06-21-05.xls. I want to change it to something like
TermTrans_JoeBlow_6-21-05.xls but haven't had any luck. The name
would
come
from cell 'C8' from the 'TermTrans' titled sheet. My original script
is
below.
Thanks!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
MyArr = Sheets("EmailAddresses").Range("a2:a25")
strdate = Format(Now, "mm-dd-yy")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, "Term/Trans - " &
Sheets("TermTrans").Range("c8")
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub