M
Mattlynn
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
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
