Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet renaming | Excel Discussion (Misc queries) | |||
sending the active worksheet and an additional new sheet | Excel Programming | |||
Renaming the Worksheet through VBA | Excel Programming | |||
renaming the active sheet | Excel Programming | |||
Help sending active sheet to email list | Excel Programming |