Hi all.

I am new to the world of macros - they're great though.
I have this emailing macro, that emails an active sheet of pivots. I want to
be set it to send as values, so that the pivots cannot be rearranged the
other end, as it retains the facility of beimg able to access the data and
rearrange them.

I have searched and found a few "paste as values" macros, but i am not fully
sure how to add them.
Can someone please alter the below, so the macro will copy as values.

Thanks
Matt
----------

Sub Mail_test()
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook


to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with


"Mattlynn" wrote:

Hi all.

I am new to the world of macros - they're great though.
I have this emailing macro, that emails an active sheet of pivots. I want to
be set it to send as values, so that the pivots cannot be rearranged the
other end, as it retains the facility of beimg able to access the data and
rearrange them.

I have searched and found a few "paste as values" macros, but i am not fully
sure how to add them.
Can someone please alter the below, so the macro will copy as values.

Thanks
Matt
----------

Sub Mail_test()
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Joel - ignore my last message - i am being blind again
Thanks
Matt


Mattlynn wrote:
Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

from
ThisWorkbook.Sheets(Shname(N)).Copy
[quoted text clipped - 14 lines]
End With
End Sub


--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Sub Mail_test()
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Hi Joel, is this line still needed?

ThisWorkbook.Sheets(Shname(N)).Copy

It looks like the beginning of a sheet copy statement.

"Joel" wrote:

Sub Mail_test()
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1

Hi Original code is here (Example 2)
http://www.rondebruin.nl/mail/folder1/mail2.htm

ThisWorkbook.Sheets(Shname(N)).Copy

Yes you need that

It will copy that sheet to a new workbook
Then it paste values
Save
mail
Delete the file

See also
http://www.rondebruin.nl/values.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JLGWhiz" wrote in message ...
Hi Joel, is this line still needed?

ThisWorkbook.Sheets(Shname(N)).Copy

It looks like the beginning of a sheet copy statement.

"Joel" wrote:

Sub Mail_test()
'Working in 97-2007
Dim wb As Workbook
Dim Shname As Variant
Dim Addr As Variant
Dim N As Integer
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Shname = Array("SP Analysis", "SP Analysis")
Addr = Array("Matthew.lynn@blablabla", "Matthew.lynn@blablabla")

If Val(Application.Version) = 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"

'Create the new workbooks/Mail it/Delete it
For N = LBound(Shname) To UBound(Shname)

TempFileName = "" & Shname(N) & " " & Format(Now, "dd-mmm-yy h-mm-ss")


ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormatNum
On Error Resume Next
.SendMail Addr(N), _
"SP Booked Meeting Data (Automated)"
On Error Resume Next
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

Next N

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


"Mattlynn via OfficeKB.com" wrote:

Thats Great Joel - but where abouts do i add this to the main macro
Cheers Mate
Matt

Joel wrote:
from
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook

to
ThisWorkbook.Sheets(Shname(N)).Copy
Set wb = ActiveWorkbook
with wb.activesheet

.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
end with

Hi all.

[quoted text clipped - 67 lines]
End With
End Sub

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1