Macro to email image of current protected WSheet

[LaDdIe]

Hi,

Is it possible & how.

I would like to send a copy of the current single WS by email using a macro,
the sheet is protected, and the receiver only needs to have a image meaning
non fuctioning WSheet, if I send a fuctioning WS the receiver cant see all of
it due to some cells being linked to others in the Wbook.

any ideas welcome

Repectx

Laddie

 

[Norman Jones]

Hi Laddie

To produce a single sheet workbook containing a static copy of the original
worksheet, try something like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ThisWorkbook
With WB
Set SH = .Sheets("Sheet3") '<<==== CHANGE
SH.Copy After:=.Sheets(.Sheets.Count)
End With

With SH.UsedRange
.Value = .Value
End With
SH.Copy
End Sub
'<<=============

To email the new workbook, see Ron de Bruin's
extensive range of sample code at:

Example Code for sending mail from Excel
http://www.rondebruin.nl/sendmail.htm

 

[Norman Jones]

Hi Laddie,


Replace the suggested code to produce the single-sheet
workbook with the following version:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ThisWorkbook
With WB
Set SH = .Sheets("Sheet3") '<<==== CHANGE
SH.Copy After:=.Sheets(.Sheets.Count)
End With

SH.Copy

With ActiveSheet.UsedRange
.Value = .Value
End With

Application.DisplayAlerts = False
With WB
.Sheets(.Sheets.Count).Delete
Application.DisplayAlerts = True
End With
End Sub
'<<=============

 

[LaDdIe]

Thank Norman,