Solved: Email Workbook without macros

With the following code, does anyone have a solution to emailing the workbook without the embedded macro? There must be code to "SaveAs" without it.

I'm not a fan of presenting the recipients with an "enable or disable macros" on receipt. TIA

[vba]
Dim strDate As String
ActiveSheet.Copy
strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
ActiveWorkbook.SendMail "<recipient>", _
"Daily Sales Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
[/vba]

Hi, UNET!
Welcome to VBAX!

When you put code, you can type [VBA] at the beginning of it, and again at the end, only this time with /VBA, and it'll format your code really cool, like it is now that I've edited it.

As for the technical question, I'll let someone else answer that. :)

... making it more legible! Thanks.

Since you're only copying one sheet into the new workbook you're attaching, I assume the code you're trying to rid of is in that sheet's class module (event procedures and such)?

I can think of two basic approaches.
Either create a new empty workbook with just one sheet, and then copy over the worksheet's contents and formatting only, instead of copying the whole sheet.
Or copy the sheet as you're doing now, and then delete the contents of its code module - examples here.

I'm with herilane; just replace [vba]ActiveSheet.Copy[/vba] with [vba]ActiveSheet.Cells.Copy
Workbooks.Add
ActiveSheet.Paste[/vba]

Yes, the first one is definitely simpler, but I mentioned the second alternative in case there are charts or other objects in the worksheet, or if you want to make sure the the page setup or print area settings etc also get copied properly.

Have a look @ DK's Site

http://www.danielklann.com/excel/strip_macros.htm

ActiveSheet.Cells.Copy
Workbooks.Add
ActiveSheet.Paste

That worked great with the macro (which is all I ever asked about :eek: ). How 'bout the link info though?

Is there a line of code that will remove any imbedded links on the paste also?

Thanks for your assistance.

A link is something that is not embedded.
Something that is embedded is not linked.

But, presumably, you mean that some of your cells are linked to other worksheets or workbooks?

Then, I think:

[VBA]ActiveSheet.PasteSpecial Paste:=xlValues[/vba]

Pasting values might have some other effects as well. Can you give us a bit more detail? Do you have hyperlinks, or formulae dependent on other workbooks, or what?

VBA:

I get a user defined error on "ActiveSheet.PasteSpecial Paste:=xlValues"

As far as the links; It's a fairly simple spreadsheet that does a weekly tally based on daily date, so worksheet 7 eventually (week-end) contains data from worksheets 1-6.

I got it.

"Selection.PasteSpecial Paste:=xlValues", instead of "ActiveSheet.PasteSpecial Paste:=xlValues"

Thanks for all your help.

UNET: I'm going to mark this solved. Let me know if I'm wrong! :)

VBA:

Not wrong but this is even better (for my purpose anyway) - " Selection.PasteSpecial Links = False"

[vba]
Private Sub CommandButton1_Click()

Dim strDate As String
ActiveSheet.Cells.Copy
Workbooks.Add
Selection.PasteSpecial Links = False

strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
ActiveWorkbook.SendMail "<recipient>", _
"Daily Sales Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End Sub
[/vba]
So, a very (very, very) basic macro to send a worksheet sans all embedded macros or links.

Thanks again to a great board!

Glad you like it!
We're not even a month old!
Don't forget to play Pacman, hee hee.

Could someone tell me how to format multiple recipients using sendmail from script? - have tried many methods without success. thanks.

Richard