Sending active worksheet and renaming
 

I'd like to send the active worksheet via outlook and rename the sent
worksheet to whatever the value is in cell B4. Can this be done?

Sending active worksheet and renaming
 

Yes you can, try this

Sub Mail_ActiveSheet_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Qaspec" wrote in message ...
I'd like to send the active worksheet via outlook and rename the sent
worksheet to whatever the value is in cell B4. Can this be done?

Sending active worksheet and renaming
 

How about also naming the new workbook with the value of cell b4 on the sent
worksheet?

"Ron de Bruin" wrote:

Yes you can, try this

Sub Mail_ActiveSheet_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Qaspec" wrote in message ...
I'd like to send the active worksheet via outlook and rename the sent
worksheet to whatever the value is in cell B4. Can this be done?

Sending active worksheet and renaming
 

Try this then

.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs .Sheets(1).Range("B4").Value _
& " " & strdate & ".xls"


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Qaspec" wrote in message ...
How about also naming the new workbook with the value of cell b4 on the sent
worksheet?

"Ron de Bruin" wrote:

Yes you can, try this

Sub Mail_ActiveSheet_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.Sheets(1).Name = .Sheets(1).Range("B4").Value
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Qaspec" wrote in message ...
I'd like to send the active worksheet via outlook and rename the sent
worksheet to whatever the value is in cell B4. Can this be done?