Emailing print area

T

TexMas

Any ideas how I can email only the print area of a worksheet? I also need to
be able to save only the print area into another file. Both of these have to
be done with absolutely no technical expertise, in other words just push a
button, no copy and paste or anything else so technical!
 
R

Ron de Bruin

Hi Texmas

Try this one for the activesheet
It will save the file in C:\
Change the e-mail address

More info on my site
http://www.rondebruin.nl/sendmail.htm

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Sub Mail_Range()
Dim source As Range
Dim dest As Workbook
Dim strdate As String

Set source = Nothing
On Error Resume Next
Set source = Range(ActiveSheet.PageSetup.PrintArea).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If source Is Nothing Then
MsgBox "The source is not a range or the sheet is protect, please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set dest = Workbooks.Add(xlWBATWorksheet)
source.Copy
With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With

strdate = Format(Now, "dd-mm-yy h-mm-ss")
With dest
.SaveAs "C:\Selection of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "[email protected]", _
"This is the Subject line"
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Top