PDA

View Full Version : Solved: Email Workbook without macros



UmbilicalNET
06-06-2004, 12:50 PM
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


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

Anne Troy
06-06-2004, 12:57 PM
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. :)

UmbilicalNET
06-06-2004, 01:03 PM
... making it more legible! Thanks.

herilane
06-06-2004, 01:39 PM
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 (http://www.cpearson.com/excel/vbe.htm).

TonyJollans
06-06-2004, 01:58 PM
I'm with herilane; just replace ActiveSheet.Copy with ActiveSheet.Cells.Copy
Workbooks.Add
ActiveSheet.Paste

herilane
06-06-2004, 02:05 PM
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.

Ivan F Moala
06-06-2004, 03:16 PM
Have a look @ DK's Site

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

UmbilicalNET
06-06-2004, 03:59 PM
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.

Anne Troy
06-06-2004, 04:05 PM
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:

ActiveSheet.PasteSpecial Paste:=xlValues

TonyJollans
06-06-2004, 04:20 PM
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?

UmbilicalNET
06-06-2004, 04:40 PM
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.

UmbilicalNET
06-06-2004, 05:27 PM
I got it.

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

Thanks for all your help.

Anne Troy
06-06-2004, 05:33 PM
UNET: I'm going to mark this solved. Let me know if I'm wrong! :)

UmbilicalNET
06-06-2004, 05:40 PM
VBA:

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


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> (commlink2@charter.net)", _
"Daily Sales Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

End Sub

So, a very (very, very) basic macro to send a worksheet sans all embedded macros or links.

Thanks again to a great board!

Anne Troy
06-06-2004, 05:41 PM
Glad you like it!
We're not even a month old!
Don't forget to play Pacman, hee hee.

r_darling
11-28-2005, 02:49 PM
Could someone tell me how to format multiple recipients using sendmail from script? - have tried many methods without success. thanks.

Richard