Disable or remove macros for a workbook to be sent in email...

[manoj.tare]

I am sending an excel workbook by email by a macro in the same excel.
Now obviously, its copy is emailed. But that copy will also contain
macro and all the buttons or controls I have added. Can I remove these
additional things and just send plain text excel containing only data?
Even if I can not remove buttons or any control I have added to current
workbook, it's OK. But I don't want my users to get into my code.

 

[Tom Ogilvy]

copy the information you want to send to a new workbook and send that. Then
close it without saving changes.

for example, to send the current sheet

Activesheet.copy
Activeworkbook.Send . . .
Activeworkbook.Close SaveChanges:=False

Activesheet.copy makes a copy of the activesheet in a new workbook which is
then the activeworkbook.

buttons would be copied and sheet code as well, but you could remove those
in the copy

http://www.cpearson.com/excel/vbe.htm for sample code on deleting code.

for objects on the sheet

Activesheet.Shapes.SelectAll
Selection.Delete

so
Activesheet.copy
Activesheet.Shapes.Select
Selection.Delete
' code to delete sheet code
Activeworkbook.Send . . .
Activeworkbook.Close SaveChanges:=False

 

[Guest]

hi,
it is possible with code to make a new file and copy all
the data into it and email the new file which would not
contail macros and buttons.
you did not say how may sheets you have nor how much data
you have.
here is a sniplet of code i use to create a new file in
memory, copy a report to it and mail it to 1 person then
delete the new file after email.

Dim sorce As Range
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "mm-dd-yy")
Set sorce = Nothing
On Error Resume Next
Set sorce = Range("BA1:BK30")'this is report i mail
On Error GoTo 0
Application.ScreenUpdating = False
Set wb = Workbooks.Add()
sorce.Copy
With wb.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column widths in 2k up not 97
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With
With wb
.SaveAs "ThisWorkbook.Name" _
& " " & strdate & ".xls"
.SendMail "lastname, firstname", _
"Name of Report"
.ChangeFileAccess xlReadOnly
Kill .FullName 'deleles new wb after email
.Close False
End With
Application.ScreenUpdating = True
'-----------end sniplet-----------------
if you have more sheets, you may need to declare multiple
sources (source1, source1 ect. )you may need a with clause
for each sheet if more than one.

 

[Tom Ogilvy]

create a new file in memory

I guess if that were true, you wouldn't need to use the Kill command with
it. <g>