Insert FileName - TabName and Date() - using VBA
 

I found this great bit of code online. This automatically creates an email
and adds the selected worksheet (only the worksheet) as an attachment. What
I'd like to do is where is says FileName = "Temp.xls" change that to the
following:

"Weekly.Purchase.Summary_TABNAME_DATE().xls"

I'd like to inclulde the tab name and todays date (whever it's being emailed.)

Is this possible?

Thanks so much for your help; the code is pasted below.

Happy New Year!!!

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ; ;
"
'Uncomment the line below to hard code a subject
.Subject = "Weekly Purchase Summary"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

Insert FileName - TabName and Date() - using VBA
 

Have you tried

FileName = "Weekly.Purchase.Summary_TABNAME_DATE().xls"


--
__________________________________
HTH

Bob

"JK" <jasonk at necoffeeco dot com wrote in message
...
I found this great bit of code online. This automatically creates an email
and adds the selected worksheet (only the worksheet) as an attachment.
What
I'd like to do is where is says FileName = "Temp.xls" change that to the
following:

"Weekly.Purchase.Summary_TABNAME_DATE().xls"

I'd like to inclulde the tab name and todays date (whever it's being
emailed.)

Is this possible?

Thanks so much for your help; the code is pasted below.

Happy New Year!!!

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ; ;
"
'Uncomment the line below to hard code a subject
.Subject = "Weekly Purchase Summary"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

Insert FileName - TabName and Date() - using VBA
 

Maybe this...

FileName = "Weekly.Purchase.Summary_" & ActiveSheet.Name & "_" & Date$ &
".xls"

Note: The $ sign on the Date function call is important so don't remove it.
Using the $ sign forces the date to print out with dashes between its parts
instead of slashes... slashes are an illegal character in a file name, so
dashes should be used instead.

--
Rick (MVP - Excel)


"JK" <jasonk at necoffeeco dot com wrote in message
...
I found this great bit of code online. This automatically creates an email
and adds the selected worksheet (only the worksheet) as an attachment.
What
I'd like to do is where is says FileName = "Temp.xls" change that to the
following:

"Weekly.Purchase.Summary_TABNAME_DATE().xls"

I'd like to inclulde the tab name and todays date (whever it's being
emailed.)

Is this possible?

Thanks so much for your help; the code is pasted below.

Happy New Year!!!

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ; ;
"
'Uncomment the line below to hard code a subject
.Subject = "Weekly Purchase Summary"
.Attachments.Add WB.FullName
.Display
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub