Send Mail from Mac Excel with Mac Mail

What is changed between Office 2011 and Office 2016 and up

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 the first time.

See this page if you want to learn more : How to use AppleScriptTask in Mac Office
If you use Mac Outlook check out this page : Send Mail from Mac Excel with Mac Outlook

Examples for Excel 2016 and up in macOS Catalina and higher

Examples to mail text or/and one or more sheets with Mac Mail as workbook or PDF, they finally fixed the signature bug so it is easy to set the signature and the sender in the VBA code now. You can also add existing files now to the mail if you want if you use the files from 1-Feb-2021.

Download example files and script file (File date : 1-Feb-2021)

Note: There is a example workbook to send as workbook and as PDF, and a script file that must be in the correct location, follow the steps below :

Where must I copy the file(RDBMacMail.scpt) with the script :
  • 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 script file from the download inside this folder

RDBAppleMail Add-in to send the ActiveSheet as Workbook or PDF in macOS Catalina and higher

I create this add-in to make it easy to send a workbook or pdf of the ActiveSheet with Apple Mail, You can set the sender and signature in the add-in and save the ones you want to use. I am not able to set the width of the two drop-downs in the add-in so this not looks very nice, I hope Microsoft will fix this bug. Please give feedback, good or bad so I can make it better.

Note: the add-in use the same script file as the code examples above so be sure you have it in the correct location, after you install the add-in you can use the example workbook that show you how to use named ranges to fill in the information for the mail.

Download version 2.0
File date 1-Feb-2021

AppleMailAdd-in


Mail row data to each person in a range

I got a few mails from teachers that want an easy way to mail grades to their students, so I add a code example to this page that you can try. Easiest way for testing is to open the MacMailWithExcel.xlsm workbook from the download above and copy the macro in a module of this workbook and insert a worksheet with :

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

Note: If you want to use this macro after testing in your own workbook you must copy the functions from the Function module from the MacMailWithExcel.xlsm file also in your workbook, do not forget to do this because it will not work without them.

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.

Sub Send_Student_Data_In_Row()
'Ron de Bruin,15-Feb-2021
'Note: This macro use the Functions in the Functions module of the MacMailWithExcel.xlsm
'file in the download on this page : https://macexcel.com/examples/mailpdf/macmail/
'Do not forget to copy the functions also in your own workbook or test this macro in this workbook.
Dim Ash As Worksheet
Dim Mailsubject As String
Dim cell As Range
Dim strbody As String

'Check if the AppleScriptTaskExcelScriptFile is in the correct location
If CheckAppleScriptTaskExcelScriptFile(ScriptFileName:="RDBMacMail.scpt") = False Then
MsgBox "Sorry the RDBMacMail.scpt file is not in the correct location, " & _
"Visit https://macexcel.com/examples/mailpdf/macmail/ for more information."
Exit Sub
End If

'Set a reference to the sheet where your data is, you can also use Sheets("YourSheetName")
Set Ash = ActiveSheet

'Enter the mail subject for all mails
Mailsubject = "Grades 15 Feb Test"

'Disable Events and ScreenUpdating
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

'Loop through all cells in the B column of the ActiveSheet in this example.
'We check if there is a mail address in the B column and if there is not the word "no" in the C column.
'It will create a mail with information from that row if the above is correct, see how I build the strBody string.
For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" _
And LCase(Cells(cell.Row, "C").Value) <> "no" Then

'We build the mail body string with the values from the row
strbody = "Hi " & Cells(cell.Row, "A").Value & vbNewLine & vbNewLine & _
"Your Grade is : " & Cells(cell.Row, "D").Value & vbNewLine & _
Cells(cell.Row, "E").Value & vbNewLine & vbNewLine & _
"Regards Ron"

'We call the mail function to create the mail in Mac mail
MacExcelWithMacMailCatalinaAndUp subject:=Mailsubject, _
mailbody:=strbody, _
toaddress:=cell.Value, _
ccaddress:="", _
bccaddress:="", _
displaymail:="yes", _
attachment:="", _
otherattachments:="", _
thesignature:="", _
thesender:=""

End If
Next cell

'Enable Events and ScreenUpdating
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Examples for Excel 2016 and up in macOS versions before Catalina

Download this example if you run macOS Mojave or High Sierra or Sierra, I not update this old code examples anymore.

Examples to mail one or more sheets with Mac Mail as workbook or PDF
Download example files and script file (File date : 1-Feb-2019)
7-March-2021
Web design by Will Woodgate