VBA is the acronym for Visual Basic for Applications. It is a programming language that Excel uses to record your steps as you perform routine tasks. You do not need to be a programmer or a very technical person to enjoy the benefits of macros in Excel. Excel has features that automatically generated the source code for you. To help prevent macros that contain viruses from contaminating your system, by default Office for Mac displays a warning message whenever you try to open a document that contains a macro. This message appears regardless of whether the macro actually contains a virus. I have a client who wants to use an Excel sheet with Macros on his Macbook Pro with OS X and Office 365 for Mac. When he opens the file, the buttons and dropdowns are converted to images. Macros—automated actions or sets of actions in Excel—can spare you some of the tedium associated with repetitious command sequences and data manipulation tasks. In this course, learn how to create and use macros to automate tasks in Excel 2016 for Mac.
Make and Mail PDF files with VBA code on your Mac
Below you find examples for Excel 2011 and Excel 2016 for the Mac, if you are a Windows user check out the code in the Win Tips section of my site.
Excel 2016 for the Mac
Save as PDF in Mac Excel 2016 (29-7-2017)
Below you can find example VBA code to save as PDF in Mac Excel 2016. Microsoft fixed a few bugs but there are still a lot of bugs to fix, so I will update the code when needed. The code will create a folder in the Office folder to save the PDF files in, read the info on this page why I use a folder in that location. Check out this page if you want an easy way to open this folder in finder : Setup your Mac for Mac Office 2016
Save as PDF and attach to mail in Mac Excel 2016
Mac Outlook 2016 with Mac Excel 2016:
For Mac Outlook 2016 mail code visit this page for examples files and Add-in (1-Feb-2019)
Mac Mail with Mac Excel 2016:
For Apple Maill code visit this page for examples files (1-Feb-2019)
Excel 2011 for the Mac
Download the example workbook below with a few example macro's to create and mail PDF's in Excel 2011. Please test it and give feedback, good or bad. There are Mail examples in the workbook for Apple Mail and Outlook 2011.
Download example file : 29 Nov 2015
Note: Fixed a bug that it not attach the pdf to the mail when you use El Capitan as OS X.
Double-click the dmg file if it does not mount automatically. Drag the file to the folder you want on your Mac. Tip: Use one folder for all your example files (easy to backup your files this way)
Note: When you add new worksheets to the workbook the example code to make a PDF of the whole workbook do not include that sheet or sheets until you Close/Reopen the file.
There are a few problems(bugs) If you use VBA SaveAs or ExportAsFixedFormat or save a workbook manual as PDF. When you save the workbook as PDF it wil create a seperate PDF for each sheet in the workbook and it also add a sheet name to the file name of the PDF when you do it manual or with code.
My workeround is this :
because creating a pdf of each sheet only happens the first time when you run the code I do this :
1: Create the PDF in a Temporary folder (code will create that for you)
2: Delete all files in this folder
3: Create the pdf again
4: Rename the PDF file (remove the sheet name)
5: Move the file to the folder you want
More Mail Examples
For more information and examples about mailing with VBA code from Mac Excel visit : Send Mail from Mac Excel
Applies to Excel templates: 'Georges Excel Checkbook for Mac'
(back to All Support Topics)
How to enable macros on Excel for Mac 2016?
While Excel 2016 for Mac and Excel 2016 for Windows are more similar in features than ever before, there are still differences including differences in Excel settings and also the VBA macros and how to enable macros. To enable macros in Excel 2016 for Mac, please view the Microsoft help article on how to enable macros in Excel 2016 for Mac.
Useful Macros For Excel
Below you can find example VBA code to save as PDF in Mac Excel 2016. Microsoft fixed a few bugs but there are still a lot of bugs to fix, so I will update the code when needed. The code will create a folder in the Office folder to save the PDF files in, read the info on this page why I use a folder in that location. Check out this page if you want an easy way to open this folder in finder : Setup your Mac for Mac Office 2016
Save as PDF and attach to mail in Mac Excel 2016
Mac Outlook 2016 with Mac Excel 2016:
For Mac Outlook 2016 mail code visit this page for examples files and Add-in (1-Feb-2019)
Mac Mail with Mac Excel 2016:
For Apple Maill code visit this page for examples files (1-Feb-2019)
Excel 2011 for the Mac
Download the example workbook below with a few example macro's to create and mail PDF's in Excel 2011. Please test it and give feedback, good or bad. There are Mail examples in the workbook for Apple Mail and Outlook 2011.
Download example file : 29 Nov 2015
Note: Fixed a bug that it not attach the pdf to the mail when you use El Capitan as OS X.
Double-click the dmg file if it does not mount automatically. Drag the file to the folder you want on your Mac. Tip: Use one folder for all your example files (easy to backup your files this way)
Note: When you add new worksheets to the workbook the example code to make a PDF of the whole workbook do not include that sheet or sheets until you Close/Reopen the file.
There are a few problems(bugs) If you use VBA SaveAs or ExportAsFixedFormat or save a workbook manual as PDF. When you save the workbook as PDF it wil create a seperate PDF for each sheet in the workbook and it also add a sheet name to the file name of the PDF when you do it manual or with code.
My workeround is this :
because creating a pdf of each sheet only happens the first time when you run the code I do this :
1: Create the PDF in a Temporary folder (code will create that for you)
2: Delete all files in this folder
3: Create the pdf again
4: Rename the PDF file (remove the sheet name)
5: Move the file to the folder you want
More Mail Examples
For more information and examples about mailing with VBA code from Mac Excel visit : Send Mail from Mac Excel
Applies to Excel templates: 'Georges Excel Checkbook for Mac'
(back to All Support Topics)
How to enable macros on Excel for Mac 2016?
While Excel 2016 for Mac and Excel 2016 for Windows are more similar in features than ever before, there are still differences including differences in Excel settings and also the VBA macros and how to enable macros. To enable macros in Excel 2016 for Mac, please view the Microsoft help article on how to enable macros in Excel 2016 for Mac.
Useful Macros For Excel
Macros Run on Open and Closing of File
When you enable macros and 'Georges Excel Checkbook for Mac' opens and properly runs those macros, certain Excel default settings will be changed. These changed settings will apply to other Excel files you open if 'Georges Excel Checkbook for Mac' is open, unless you open those files in a different instance/session of Excel. Excels default settings will be changed to the following settings each time 'Georges Excel Checkbook for Mac' is opened and its macros enabled.
- The drag and drop feature and fill handle will be turned off.
- Background error-checking will be disabled.
- Include new rows and columns in table will be disabled.
- Fill Formulas in Tables to create calculated columns will be disabled.
- Show paste options buttons will be disabled.
- AutoRecover for this workbook only ('Georges Excel Checkbook for Mac') will be disabled.
- Display AutoCorrect options will be disabled.
- After pressing Enter move selection to the Right (as opposed to down)
If 'Georges Excel Checkbook for Mac' macros have been enabled and they properly execute and you then close 'Georges Excel Checkbook for Mac', the above changed settings will be restored to Excels default settings with exception of the AutoRecover for this workbook only ('Georges Excel Checkbook for Mac').
Macros In Excel For Mac
(back to All Support Topics)
