Office 2010 is causing havoc with Macros..help!

**cheddarthief** · 03-10-2011, 04:12 PM

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.

Please Login or Register  to view this content.

Any help would be greatly appreciated

**TMS** · 03-10-2011, 04:20 PM

I suspect that changing this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

will address the "different format" problem.

Regards

**MarvinP** · 03-10-2011, 04:23 PM

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.

**cheddarthief** · 03-10-2011, 05:05 PM

MarvinP, I tried what you suggested first (.xls to .xlsm) and it didn't work. I hadn't however tried what TMShucks had suggested. I tried it and did a test to myself. No errors. Now, I've reloaded the updated macro on the file I was having issues with and resent the emails using the macro again to my vendors. All of them replied it was working now. Awesome!!! Who would have thought one little "x" was causing that problem? Thanks for all your help.

cheddarthief

**MarvinP** · 03-10-2011, 05:07 PM

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

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

**cheddarthief** · 03-10-2011, 05:11 PM

Most people do NOT want to receive an email that has a macro in it for fear that it might be a virus. Plus, some people have said they don't get them at all with the macro in them most likely because of a firewall so I've had to create PDF files for them seperately. This works best. Thanks for all your help. Now I'm back to being efficient. BTW, how do I show that the issue is solved?

cheddarthief

**MarvinP** · 03-10-2011, 05:37 PM

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.

**TMS** · 03-10-2011, 06:08 PM

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.