Excel 2003 VBA problem - extracting and emailing worksheets

[BJ&theBear]

The macro/VBA coding below is an adaptation of Ron de Bruins macro for
extracting certain sheets from a workbook and creating another
workbook which is then emailed to an address from Outlook Contact list
or to specified addresses.

My problem is that one of the sheets I wish to email has many
thousands of entries and all I really need is a blank worksheet with
the correct columns and headings. This worksheet will subsequently be
completed by employees and then emailed back to be imported back into
the main workbook. The other worksheets need to retain all their
information as they are lists for a series of dropdown selection
lists.

I do not want to "blank" the worksheet before it is exported/copied to
the new sheet as there is a fear that if the macro crashes then all
data in the main workbook will be lost.

Can anyone advise how I can just export the sheet
"HoursWorkedexpenses" still as part of the array but with no
information other than the column headings.

I cannot get my head round this problem

Thanks for any advice

Brian
Scotland




Sub Mail_Sheets_Array_single()

'Macro Courtesy of Ron de Bruin

'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window
Dim I As Long

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

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
'We add a temporary Window to avoid the Copy problem
'if there is a List or Table in one of the sheets and
'if the sheets are grouped
With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
.Sheets(Array("UserList", "HoursWorkedexpenses",
"ProjectList", "CLSstageList", "Input")).Copy
End With

'Close temporary Window
TempWindow.Close

Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010, we exit the sub when your answer
is
'NO in the security dialog that you only see when you
copy
'an sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheets to values if you want
' For Each sh In Destwb.Worksheets
' sh.Select
' With sh.UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Destwb.Worksheets(1).Select
' Next sh

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name _
& " " & Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail "", _
"Latest Timesheet"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0



' only add this line if you do not want to send email
' .Close SaveChanges:=False
End With

'Delete the file you have send
' This line removes all trace of the email
' Kill TempFilePath & TempFileName & FileExtStr

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


Sub Mail_Sheets_Array_group()

'Macro Courtesy of Ron de Bruin

'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window
Dim I As Long

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

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
'We add a temporary Window to avoid the Copy problem
'if there is a List or Table in one of the sheets and
'if the sheets are grouped
With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
.Sheets(Array("UserList", "HoursWorkedexpenses",
"ProjectList", "CLSstageList", "Input")).Copy
End With

'Close temporary Window
TempWindow.Close

Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010, we exit the sub when your answer
is
'NO in the security dialog that you only see when you
copy
'an sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheets to values if you want
' For Each sh In Destwb.Worksheets
' sh.Select
' With sh.UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Destwb.Worksheets(1).Select
' Next sh

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name _
& " " & Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail Array("(e-mail address removed)", "(e-mail address removed)",
"(e-mail address removed)"), "Latest Timesheet"
' "Latest Timesheet"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0



' only add this line if you do not want to send email
' .Close SaveChanges:=False
End With

'Delete the file you have send
' This line removes all trace of the email
' Kill TempFilePath & TempFileName & FileExtStr

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

 

[Jim Cone]

Add another sheet to your workbook.
Name it something like HourWorkedexpenses_new. Put your headings on it.
Send that sheet instead of HourWorkedexpenses...

With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
..Sheets(Array("UserList", "HoursWorkedexpenses_new", "ProjectList", "CLSstageList", "Input")).Copy
End With

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
http://www.contextures.com/excel-sort-addin.html




"BJ&theBear" <[email protected]>
wrote in message The macro/VBA coding below is an adaptation of Ron de Bruins macro for
extracting certain sheets from a workbook and creating another
workbook which is then emailed to an address from Outlook Contact list
or to specified addresses.

My problem is that one of the sheets I wish to email has many
thousands of entries and all I really need is a blank worksheet with
the correct columns and headings. This worksheet will subsequently be
completed by employees and then emailed back to be imported back into
the main workbook. The other worksheets need to retain all their
information as they are lists for a series of dropdown selection
lists.

I do not want to "blank" the worksheet before it is exported/copied to
the new sheet as there is a fear that if the macro crashes then all
data in the main workbook will be lost.

Can anyone advise how I can just export the sheet
"HoursWorkedexpenses" still as part of the array but with no
information other than the column headings.

I cannot get my head round this problem
Thanks for any advice
Brian
Scotland

-snip-

 

[BJ&theBear]

Add another sheet to your workbook.
Name it something like HourWorkedexpenses_new.  Put your headings on it..
Send that sheet instead of HourWorkedexpenses...

With Sourcewb
 Set TheActiveWindow = ActiveWindow
 Set TempWindow = .NewWindow
.Sheets(Array("UserList", "HoursWorkedexpenses_new", "ProjectList", "CLSstageList", "Input")).Copy
End With

--
Jim Cone
Portland, Oregon  USAhttp://www.mediafire.com/PrimitiveSoftwarehttp://www.contextures.com/excel-sort-addin.html

"BJ&theBear" <[email protected]>
wrote in messageThe macro/VBA coding below is an adaptation of Ron de Bruins macro for
extracting certain sheets from a workbook and creating another
workbook which is then emailed to an address from Outlook Contact list
or to specified addresses.

My problem is that one of the sheets I wish to email has many
thousands of entries and all I really need is a blank worksheet with
the correct columns and headings.  This worksheet will subsequently be
completed by employees and then emailed back to be imported back into
the main workbook.  The other worksheets need to retain all their
information as they are lists for a series of dropdown selection
lists.

I do not want to "blank" the worksheet before it is exported/copied to
the new sheet as there is a fear that if the macro crashes then all
data in the main workbook will be lost.

Can anyone advise how I can just export the sheet
"HoursWorkedexpenses" still as part of the array but with no
information other than the column headings.

I cannot get my head round this problem
Thanks for any advice
Brian
Scotland

-snip-

Jim

You are a gem??!!

I have been pondering how to do this for 24 hours and never thought
that something could be so simple.

I really have been having a bad blockage the last few days

Thanks once again for the time and effort

BJ