Thread: Office 2010 is causing havoc with Macros..help!

I have a macro that makes a copy of every active workbook and emails it to the email address located in a specific location on each tab. We were using Office 97 but just upgraded to 2010. The problem is that now when my vendors receive the emails, the Excel sheet is a mess on their end. I tried using the macro to email a copy of the worksheet to myself and received the following message when trying to open it:

"The file you are trying to open 'xxxxx', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

I know there are all sorts of new file extensions and that seems to be part of the problem. Apparently, to save the file in the newest version of Excel and still retain the macro coding, I must save it as a "Macro-Enabled Workbook". When I do this and then try to run my macro I get a debuggin message.

Here is the macro code I'm currently using and I'll attached both the 97 and 2010 version of the Excel file.

'Written: April 07, 2010
'Updated: April 15, 2010
'Author:  Leith Ross
'Summary: Email All Visible Worksheets in a Workbook as an attachment using Outlook

Sub EmailAllVisibleSheetsAsAttachment()

  Dim FileName As String
  Dim olApp As Object
  Dim Recipient As Variant
  Dim shtName As String
  Dim TextMsg As String
  Dim TextFile As Object
  Dim Wks As Worksheet
  
    TextMsg = "Please see the attached RFQ." & vbLf & vbLf & vbLf & "James J. Bender" & vbLf & "Amrod Bridge & Iron"
     
     'Start Outlook
      Set olApp = CreateObject("Outlook.Application")
     
       'Email only the visible worksheets
        For Each Wks In Worksheets
        
          If Wks.Visible = xlSheetVisible Then
           'Make the worksheet the ActiveSheet
            Wks.Activate
            
           'Check that the recipient cell is not an empty string or formula error
            Recipient = Wks.Range("C16")
            If VarType(Recipient) <> 0 And VarType(Recipient) <> 10 Then
            
              FileName = "RFQ-" & Wks.Range("C17") & "-Ref" & Wks.Range("C18") & ".xls"

             'Make a new workbook from the worksheet
              ActiveSheet.Copy
              
             'The next 2 lines will remove any links in the copied workbook
             'by removing all formulas and validation from the copied worksheet
              ActiveSheet.UsedRange.Value = Wks.UsedRange.Value
              ActiveSheet.UsedRange.Validation.Delete
              
             'Save the copied workbook - This will be attached to the email
              ActiveWorkbook.SaveAs FileName
              
               'Email the Worksheet as an attachment
                  With olApp.CreateItem(0)
                    .To = Recipient
                    .Subject = "Request for Quote-" & Wks.Range("C17") & "-Ref" & Wks.Range("C18")
                    .Body = TextMsg
                    .Attachments.Add CurDir & "\" & FileName, 1
                    .Display
                  End With
               
             'Close the copied workbook and do not save changes
              ActiveWorkbook.Close False
              
             'Delete the copied workbook
              Kill FileName
  
            End If
          End If
          
        Next Wks
        
Cleanup:
   'Free the objects and memory
    Set olApp = Nothing
    
End Sub

Any help would be greatly appreciated

I suspect that changing this:

FileName = "RFQ-" & Wks.Range("C17") & "-Ref" & Wks.Range("C18") & ".xls"

to this:

FileName = "RFQ-" & Wks.Range("C17") & "-Ref" & Wks.Range("C18") & ".xlsx"

will address the "different format" problem.

Regards

Hi,

Without too much time spent on my part.

Try to change the ".xls" in your code above to ".xlsm" and see what happens.

It would be great if that is all it took. Let us know.

Great news!
One little letter made all the difference!!

The .xlsm would be if there was a macro in the file you are sending.

Hi,
To mark this as solved, Edit the original post (click the Edit button below the message).. Then click on Go Advanced and edit the Prefix of the Title.

There you go, 50-50 ... some you win, some you lose ;-)

I have not researched the rationale, but I suspect that Microsoft have purposely differentiated workbooks with code as opposed to those that don't ... just so you know what's in the box.

I'm pleased it worked ... thanks for the feedback.