Important : The first 5 steps below are very important to read and follow before you can test the first mail macro in step 6. I hope I make it easy for you with the information on this page to add the mail code (Functions and macros) to your own workbook. It is not so easy as in Excel for windows but I got it working. Suggestions are welcome and also mail me if you got problems. Good luck.
Note: You can use the old Outlook and the new Outlook on the Mac, you can click on the
New Outlook menu item below About Outlook in the Outlook menu to change it. On this moment both the old and new Outlook have some problems, read about it below.
1: If you use the
New Outlook you are not able to use to set the mail account in the code because AppleScript not see the mail accounts. If you not set the account it will use the default mail account. When you not use the New Outlook you can set every account you want. I hope In Dec-2024 they rolling out support for AppleScript so I hope the account problem is fixed then in the New Outlook.
2:
if you use the
Old Outlook, when my examples code display the mail and you want to close the mail you not get an option to save a or delete the mail, it will always be saved in the Draft folder. If you click on the Send button or change my code that it will send directly you not have this problem. No problem if you use the New Outlook.
Note: Mailing with VBA code with Mac Mail not have the problems above :
https://macexcel.com/examples/mailpdf/macmail/There is no object model in VBA to talk to Outlook and the legacy "MacScript" VBA Command is severely limited by Apple’s sandbox requirements: it will not work correctly in most situations in Office 2016 and up. Instead, Microsoft added a new VB command "AppleScriptTask" that accesses and runs an AppleScript file located outside the sandboxed app. This new approach is not as convenient: with the MacScript function you could have the script in the file itself, while with the AppleScriptTask method you need to distribute an extra file containing the script, and it must be placed in the specified location on the user’s system to have permission to run. This requires some user interaction only the first time.
Download the script file :
RDBMacOutlook(2).scpt
- Open a Finder Window
- Hold the Alt key when you press on Go in the Finder menu bar
- Click on Library
- Click on Application Scripts (if it exists; if not create this folder)
- Click on com.microsoft.Excel (if it exists; if not create this folder) note: Capital letter E
- Copy the RDBMacOutlook(2).scpt script file from the download inside this folder
There is another easy way to create the folders without following the steps above, check out this page if you are interested.
Setup your Mac for Mac OfficeWith Excel open create an new workbook and save it as an xlsm file (Macro Enabled workbook) with the name you want. In my example I name the file TestFileMail.xlsm. To create a new file you can use in the menu File>New or you can use the shortcut Command n.
With your test workbook active press the shortcut Command F11 to open the VBE editor, now right click your VBAProject(TestFileMail.xlsm) and choose Insert > Module to create a module.
You will end up with this if you have done it correct :
Copy the 9 custom functions below that the macros will use inside Module1, click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module1 with Command v. The custom functions will be used by the mail macros, the macro's will not work if they are not in the workbook. Not every macro example use every function but it is no problem to have them in your function module.
I show you above how to add a module to your workbook and we have copy the custom functions inside it, now we will add another module (Module2) that we will use for the macro's. Always important to organize your code so it is easier to find the things you need. You will notice that you will not have to edit the custom functions so it is easier that they are in a separate module. You can create a separate module for each example section on this page so it is easier to find and to run the macro's to test, and if you want you can also change the names of the Modules.
In the macro's below you see that we call the custom function named MailWithMacOutlookFunction, that we copy inside Module1 with 8 other functions that we use to create the mails. I will explain you now what the arguments are and what you can do with them. For the PDF code and the HTML body example we use two different functions but the arguments are almost the same so the info below you can use also in the examples for the HTML body and PDF.
MailWithMacOutlookFunction _
subject:="This is a test macro", _
mailbody:=strbody, _
toaddress:="ron@debruin.nl", _
ccaddress:="", _
bccaddress:="", _
displaymail:="yes", _
accounttype:="", _
accountname:="", _
attachment:=AttachmentPath, _
otherattachments:=""
subject : String for the subject
mailbody : We build the strbody string inside the macro, see the macros examples below.
toaddress, ccaddress, bccaddress : With more mail addresses separate them with a ,
displaymail : Change yes to no or leave it empty in the displaymail argument to send directly
accounttype : Look in Outlook>Settings>Accounts for the account type and mail address of the account that you want to use, If accounttype is empty it will use the default mail account, accounttype can be "exchange", "pop" or "imap". Note: It will use the signature of the account that you choose.
account name : Fill in the mail address of the account, see Outlook>Settings>Accounts for account information. If account name is empty is will use the default mail account
attachment : path of the attchment, is used for one file
otherattachments : Attach other existing files in this format "/Users/rondebruin/Desktop/MyFile.xlsx" with more files separate them with a ,
Below are three examples to add text to your mail :
- Text is in the macro code
- Cell reference
- Loop through range
Click in the code block below and all code is selected and you can use
Command c to copy and paste it inside
Module2 with
Command v. Be sure there is a sheet named "
Sheet1" with some data in The
A column and you can test the three macro's without changing them.
This macro create and display a mail with a range or the selection as attachment, be sure there is a sheet named "Sheet1" with some data and you can run it without changing the code to test it. Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module3 with Command v.
This macro create and display a mail with the active sheet as attachment, you can run it without changing the code to test it. Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module4 with Command v.
This macro create and display a mail with attached the last saved version of your workbook or a copy of your workbook with the name you want. You can run it without changing the code to test it. Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module5 with Command v.
The macro below will work on the ActiveSheet, so create a new worksheet in your test workbook and add some information in it like I show you below:
In column A : Names of the students
In column B : E-mail addresses
In column C : empty or no ( if the value is "no" it will not create a mail)
In column D : Grades Score like 80 or so
In column E : Comment line like Good Job
Test the code with two lines of data or so, it display the mail so you can check out the result. You can change the displaymail argument in the function call to no if you want to send it directly.
Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module6 with Command v.
This macro create and display a mail with in the Body the used range if the active sheet, see the commented code in the macro for other examples. You can run it without changing the code to test it. Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module7 with Command v.
Below you find three macro's that create a mail with a PDF of the ActiveSheet, whole workbook or Range, you can test them without changing. See the text mail examples above for examples how to change the mail body string named strbody. Click in the code block below and all code is selected and you can use Command c to copy and paste it inside Module8 with Command v.