Send Mail Script Help

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" <JBL@discussions.microsoft.com> wrote in message
news:CE66FB7C-4DCE-404A-9CED-C65AA17DAE27@microsoft.com...
> 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" <JBL@discussions.microsoft.com> wrote in message
> news:CE66FB7C-4DCE-404A-9CED-C65AA17DAE27@microsoft.com...
> > 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

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

Originally Posted by JBL

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" <JBL@discussions.microsoft.com> wrote in message
> news:CE66FB7C-4DCE-404A-9CED-C65AA17DAE27@microsoft.com...
> > 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
> >
>
>
>

.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" <JBL@discussions.microsoft.com> wrote in message
news:6A96EB1A-830F-4A3C-9462-A145D8BBE273@microsoft.com...
> 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" <JBL@discussions.microsoft.com> wrote in message
> > news:CE66FB7C-4DCE-404A-9CED-C65AA17DAE27@microsoft.com...
> > > 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" <phillips@tiscali.co.uk> wrote in message
news:uHE00prdFHA.620@TK2MSFTNGP10.phx.gbl...
> .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" <JBL@discussions.microsoft.com> wrote in message
> news:6A96EB1A-830F-4A3C-9462-A145D8BBE273@microsoft.com...
> > 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" <JBL@discussions.microsoft.com> wrote in message
> > > news:CE66FB7C-4DCE-404A-9CED-C65AA17DAE27@microsoft.com...
> > > > 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
> > > >
> > >
> > >
> > >
>
>