Saving and Emailing

[SDRBIRD]

I am new to all this and am trying to create a macro that will copy tab to new sheet rename and email. No having much luck.

So far I have got this

Sub email()
'
' email Macro
' Copy and Email Daily Sales Sheet
'
' Keyboard Shortcut: Ctrl+e
'

'Copy to new sheet

Sheets("Daily").Select
Sheets("Daily").Copy
Cells.Select
Range("E3").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Save Function
Dim savename As String
savename = Range("M1") 'This is the cell with the info I need in the file 'name

ActiveWorkbook.SaveAs "C:\daily Sales\" & "Week " & "savename" & ".xls"

'Email Function
ActiveWorkbook.SendMail Recipients:="sale@domain.com", Subject:="Order", ReturnReceipt:=False
MsgBox "Your Request Has Been Sent", , "Daily Sales Week "




End Sub

The problem I am having is with the savename. Instead to the file saving as Week 1, it is saving as Week savename.

Any help would be great

Thanks

 

[ajetrumpet]

'Save Function
Dim savename As String
savename = Range("M1") 'This is the cell with the info I need in the file 'name

ActiveWorkbook.SaveAs "C:\daily Sales\" & "Week " & "savename" & ".xls"


The problem I am having is with the savename. Instead to the file saving as Week 1, it is saving as Week savename.

you do not need quotation marks around a string variable:

ActiveWorkbook.SaveAs "C:\daily Sales\" & "Week " & savename & ".xls"

 

[SDRBIRD]

Thanks. Work great.

 

[SDRBIRD]

I need to email this file in excel 2003 format.

but when using the code below I get a Method error. If i remove the Application.Default line the file save but when opening I get a file corruption error "file not save in extension format"

Application.AlertBeforeOverwriting = False
Application.DefaultSaveFormat = xlExcel12
ActiveWorkbook.SaveAs "C:\Daily Sales\" & "Daily Sales Week" & "_" & savename & ".xls"

 

[georgedwilkinson]

How do you know that "xlExcel12" is the correct format for Excel 2003? My current installation is 2002 and it doesn't come anywhere close to version 12.

Look up "FileFormat" in your Excel version's help and click on "XlFileFormat" to find what constants are available. Or right click on "xlExcel12" and select "Definition" to see if it is defined.

 

[DreamGenius]

FWIW, Version 12 is 2007. 11 is 2003 and 10 is 2002/XP, which is what I'd use as a default if you're wanting to make sure that whoever you're mailing it to can open it. You should use xlNormal instead of xlExcel12.

 

[SDRBIRD]

Thank you.