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
Bookmarks