Saving Workbook 2007 Format in Send Mail Q
 

I've just converted to Office 2007 and have an error when I try to use
Send Mail, with the message "The following Feature cannot be saved in
a macro free workbook" .VB Project. It debugs on the line below

.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum

However part of my code prior to this allocates the FileFormat number
to it for Excel 2007, see part below, all via Ron de Bruin. Any ideas?

With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a 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

Saving Workbook 2007 Format in Send Mail Q
 

Hi Seanie

Can you send me the link to the macro that you use and
where you copy the code

--

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


"Seanie" wrote in message ...
I've just converted to Office 2007 and have an error when I try to use
Send Mail, with the message "The following Feature cannot be saved in
a macro free workbook" .VB Project. It debugs on the line below

.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum

However part of my code prior to this allocates the FileFormat number
to it for Excel 2007, see part below, all via Ron de Bruin. Any ideas?

With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a 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

Saving Workbook 2007 Format in Send Mail Q
 

Ron

Its a pretty big file with a lot of code, which worked fantastic until
I upgraded yesterday and am only seeing the issues with files from
today. I added a line "application.displayalerts=false" to the
beginning of the code and hey presto it seems to have worked a treat,
although I used this becase I was getting the "compatibility Checker"
dialog box appear, thats gone now, perhaps they are linked issues?
Full Mail code is below

Sub Mail_New_Version()
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 OutApp As Object
Dim OutMail As Object
Dim Sh As Worksheet
Dim strbody As String

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

Application.displayalerts=false

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("E-Total","E-Current")).Copy
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
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a 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")

ActiveWindow.TabRatio = 0.908




Sheets("E-Current").Activate
Range("C6").Select
ActiveWindow.FreezePanes = True
ActiveSheet.EnableSelection = xlNoSelection
ActiveSheet.Protect Password:="123"

Range("A1").Select


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

For Each cell In ThisWorkbook.Sheets("E-Current").Range
("BF2:BF25")
strbody = strbody & cell.Value & vbNewLine
Next

For Each cell In ThisWorkbook.Sheets("E-Current") _
.Columns("BA").Cells.SpecialCells(xlCellTypeConsta nts)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = strto
.Subject = ThisWorkbook.Sheets("E-Current").Range
("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = True
If Sheets("E-Current").Range("D188").Value 0 Then
.Importance = 2
Else
.Importance = 1
End If
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

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

Saving Workbook 2007 Format in Send Mail Q
 

Post back if you still have problems this week

--

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


"Seanie" wrote in message ...
Ron

Its a pretty big file with a lot of code, which worked fantastic until
I upgraded yesterday and am only seeing the issues with files from
today. I added a line "application.displayalerts=false" to the
beginning of the code and hey presto it seems to have worked a treat,
although I used this becase I was getting the "compatibility Checker"
dialog box appear, thats gone now, perhaps they are linked issues?
Full Mail code is below

Sub Mail_New_Version()
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 OutApp As Object
Dim OutMail As Object
Dim Sh As Worksheet
Dim strbody As String

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

Application.displayalerts=false

Set Sourcewb = ActiveWorkbook

'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("E-Total","E-Current")).Copy
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
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a 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")

ActiveWindow.TabRatio = 0.908




Sheets("E-Current").Activate
Range("C6").Select
ActiveWindow.FreezePanes = True
ActiveSheet.EnableSelection = xlNoSelection
ActiveSheet.Protect Password:="123"

Range("A1").Select


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

For Each cell In ThisWorkbook.Sheets("E-Current").Range
("BF2:BF25")
strbody = strbody & cell.Value & vbNewLine
Next

For Each cell In ThisWorkbook.Sheets("E-Current") _
.Columns("BA").Cells.SpecialCells(xlCellTypeConsta nts)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = strto
.Subject = ThisWorkbook.Sheets("E-Current").Range
("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
.ReadReceiptRequested = True
If Sheets("E-Current").Range("D188").Value 0 Then
.Importance = 2
Else
.Importance = 1
End If
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

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

Saving Workbook 2007 Format in Send Mail Q
 

Ron, XLSM format as the original document and mailing out as an XLSX
seems fine, although the line "Application.DisplayAlerts=False" see
necessary at the start of the code

Saving Workbook 2007 Format in Send Mail Q
 

This is very strange

What is your default Save format in Excel 2007 ?



--

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


"Seanie" wrote in message ...
Ron, XLSM format as the original document and mailing out as an XLSX
seems fine, although the line "Application.DisplayAlerts=False" see
necessary at the start of the code

Saving Workbook 2007 Format in Send Mail Q
 

Default is "Excel Workbook". SHould this be Excel Enabled Macro?

I haven't converted any existing files to 2007 yet, still in the 2003
formate as most of my users have only 2003

Saving Workbook 2007 Format in Send Mail Q
 

No that is not the problem

Do you have the same problem when you use a exact macro from my site?

If you have, send me that workbook private and I look at it



--

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


"Seanie" wrote in message ...
Default is "Excel Workbook". SHould this be Excel Enabled Macro?

I haven't converted any existing files to 2007 yet, still in the 2003
formate as most of my users have only 2003

Saving Workbook 2007 Format in Send Mail Q
 

Ron, I have a lot of sensitive data within the file, so I'm reluctant.

Part of my problem is that with DisplayAlerts = True, I get the Yes/No
Macro enabled dialog box, I want to full automate the process so the
answer should be automated to No and saved then as an XLSM file. Can
this be done? Also I populate ThisWorkbook via VBA and I assume I have
to have an XLSM file in order to do this

Saving Workbook 2007 Format in Send Mail Q
 

Ron, I have a lot of sensitive data within the file, so I'm reluctant
Try to create another workbook with this problem

--

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


"Seanie" wrote in message ...
Ron, I have a lot of sensitive data within the file, so I'm reluctant.

Part of my problem is that with DisplayAlerts = True, I get the Yes/No
Macro enabled dialog box, I want to full automate the process so the
answer should be automated to No and saved then as an XLSM file. Can
this be done? Also I populate ThisWorkbook via VBA and I assume I have
to have an XLSM file in order to do this

Saving Workbook 2007 Format in Send Mail Q
 

Ron, just sent a mail to you

Saving Workbook 2007 Format in Send Mail Q
 

I got it

I think it will be tomorrow when I have time to look at it

--

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


"Seanie" wrote in message ...
Ron, just sent a mail to you