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