Send Mail Script Help

[Guest]

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]

You have to save it as such

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

 

[Guest]

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

 

[jose luis]

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

 

[Bob Phillips]

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

 

[Bob Phillips]

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