Mail with VBA from Excel with Mac Outlook (also PDF)


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/


1: Copy the RDBMacOutlook(2).scpt script file in the correct location

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 Office


2: Create a new workbook for testing

With 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.


3: Copy the Custom VBA functions in your test workbook

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.

vbe1

You will end up with this if you have done it correct :
vbe2
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.



4: Add another Module in your workbook for your mail macro's

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.


5: The arguments in the function call in the macro's

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 ,


6: Example macro's to create a mail with only text

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.



7: Example macro to create a mail with a Range or Selection as attachment

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.



8: Example macro to create a mail with the ActiveSheet or more then one worksheet as attachment

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.



9: Example macro to create a mail with the whole workbook

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.



10: Mail row data to each person in a range

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.



11: Mail range in the body(HTML) of the mail

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.



12: Mail whole Workbook, ActiveSheet or Range as PDF

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.

06/12/2024
Web design by Will Woodgate