EXCEL 2011 Mac Macro

**johnkubik** · 02-24-2011, 05:40 PM

Hey guys,

Quite annoyingly someone at the office requested that I port a Macro that works seamlessly on Windows Excel to the Mac Office 2011 Excel now that it supports VBA.

The Macro I am porting essentially creates an email (using outlook in windows) copies some data out of the current workbook to the emails body, and CREATES AND SAVES a new modified workbook in the same directory as the current workbook attaches the new workbook to the email.

There are 3 things I am having trouble with and would be very happy if someone could help me with:
1. How should I deal with the saving aspect? Mac OSX has a way different directory structure than windows, so creating and saving has been breaking now matter how I have tried it?

2. How do I get data out of the workbook and into the body of email. Right now I just have a Sheets("Name").Range("XX") that just gets implanted into the body using the Excel VBA outlook mail handler?

3. How do I get the Mac default Mail Handler to open a new message with the required information?

I am posting my code below so you can pick it apart and tell me how you think I should adapt it Mac Office 2011 VBA!!

I had to trim the code up because of the character limits. If you have any questions please let me know!

Please Login or Register  to view this content.

**johnkubik** · 02-25-2011, 11:49 AM

You guys are my last hope for resolving this. I have search all over the internet, and tried looking at the feeble MAC help files to no avail.

Did I detail my problem correctly, or is this just an unknown territory?

**romperstomper** · 02-25-2011, 12:08 PM

Have you actually tried it on a Mac? Other than replacing backslashes in filepaths with Application.Pathseparator you may not have a problem, especially as Office 2011 includes Outlook now.

**johnkubik** · 02-25-2011, 12:12 PM

Thank you for the response.

I have tried it. and it doesnt work

It creates the new workbook, but does not copy any of the cells (I used a watch and it doesnt even get the variables right). It also does not create a new email.

I am curious as to how to create the email in either Mac Mail, Entourage, or Outlook but there is no documentation I can find to do this. Do you know of a site that would list the calls for mailhandler?

**romperstomper** · 02-25-2011, 12:17 PM

No, I don't. I don't use a Mac for work, but I would be (slightly) surprised if you can't automate Outlook with VBA. I'd be quite surprised if you can automate either of the others using anything but applescript.

**johnkubik** · 02-25-2011, 12:34 PM

Do you think it would be possible to automate using apple script? I looked at the automator functions roughly and am drawing a blank.

thanks though!

**romperstomper** · 02-25-2011, 12:37 PM

Again, not a clue. I don't use it for work, so have never needed to learn AS. Anyone using 2011 should have Outlook though, I think, so why not automate that?

**johnkubik** · 02-25-2011, 12:42 PM

You are right... the problem is finding the mailhandler function for it. In windows it is

Please Login or Register  to view this content.

Mac outlook/mail does not have an object that I can find.

**Jim Gordon Mac MVP** · 04-09-2012, 10:14 AM

To send a workbook as an email attachment using the default email application on the Mac you can use the following:

Please Login or Register  to view this content.

At the moment, you can only have a single email recipient in the To field (this bug has been reported to Microsoft). In the example above MyRecipients would be an email address as text. You could also use "someone@someplace.com"with quotation marks in place of MyRecipients. Although the Subject command is valid and allowed, the subject will not be populated. I'm hoping this gets fixed in SP2, but I'm not holding my breath.