Send Mail Script Help

JBL · 06-21-2005, 02:05 PM

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

Bob Phillips · 06-21-2005, 04:05 PM

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
>

JBL · 06-21-2005, 05:05 PM

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

**jose luis** · 06-21-2005, 05:48 PM

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

Please Login or Register  to view this content.

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

Bob Phillips · 06-21-2005, 08:05 PM

.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
> > >
> >
> >
> >

Bob Phillips · 06-22-2005, 06:05 AM

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