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