Sending email via Mail from Excel Mac automatically

5 min readApr 24, 2020

Motivation

This tutorial will teach you how to write the VBA script to automate your email from Excel. It is adapted and simplified from Ron De Bruin’s example. With a click of a button in Excel, we pick up information such as recipient email address and header from the Excel Sheet and compose multiple emails in Mail.

Other applications of this script might be:

Send invoices to stakeholders regularly
Send excel file as attachment to multiple customers
Automate IFFT triggers

The following will be quite technical! If you are looking for a fuss free way of customizing this for your use case, feel free to contact us at hello@cognaticslab.com.

How the macro works

Trigger macro with button
Macro pick up details regarding email based on what user specified on Excel Sheet
Macro pass this details to AppleScript which commands Mail to create an email and send it

Step 1: Setting up Applescript

Applescript is a language used by Apple to interact with its software. From another perspective, we are actually controlling Mail with Applescript. The applescript can be found in Ron De Bruin’s website. Don’t worry about the security! We looked through it and there is nothing malicious about the script.

What you really need to do is this:

Press command + space on your keyboard. This will bring up a Spotlight search
Search for “com.microsoft.Excel” by keying it in the search and pressing enter. Note the capital E. Create the folder If it does not exist
Paste the file macMail.scpt in the folder

Once the applescript is inside, we can then get the macro to run it.

What the AppleScript does

The main function we are interacting with is CreateMailInMailSierra which takes a string argument.

Split the string argument by ; . There should be 7 components after splitting.
Determine if we want to send the message (draftFirst). If draftFirst is not “yes”, applescript will create the message and send it.
Set content, recipient, cc and bcc

Step 2: Setting up Excel VBA

Use Columns A to E for email, cc recipient, bcc recipient, body text and title. Also, set a button which can trigger a mail macro.

Step 3: Mail subroutine

The mail subroutine reads information from excel sheet. First, let’s do the admin stuff — declaration and point where the columns are. We hardcoded which column points to which email field. You might want to set this dynamically instead!

Sub mail()
Dim senderDetails As String
Dim fullAttachmentPath As String
Dim strbody As String
Dim emailTitle As String
Dim recipient As String


' set this worksheet
Set mainWs = ThisWorkbook.Worksheets(1)


' Sender name
senderDetails = " "


' columns --- CHANGE THIS

emailCol = 1
ccCol = 2

bccCol = 3
bodyCol = 4
titleCol = 5

startRow = 2

lastRow = mainWs.Range("A1000000").End(xlUp).Row

Next, we loop through each entry in our worksheet to extract email fields.

' loop through lines to email

For r = startRow To lastRow
   
 
    
    ' Vendor email
    
    recipient = mainWs.Cells(r, emailCol).Value
    
    
    
    ' cc and bcc
    
    cc = CStr(mainWs.Cells(r, ccCol).Value)
    
    bcc = CStr(mainWs.Cells(r, bccCol).Value)
    
    
    
    ' body text
    
    strbody = CStr(mainWs.Cells(r, bodyCol).Value) & vbNewLine   
    
    
    ' title
    
    emailTitle = CStr(mainWs.Cells(r, titleCol).Value)

We have the email fields yay. However, we still need to prepare it in the format that our AppleScript wants. As such, we call another subroutine — MacExcel2016WithMacMailSierra. The displaymail argument correspond to draftFirst in our AppleScript. If yes, we display the mail as draft. Otherwise, we send the mail without displaying it.

MacExcel2016WithMacMailSierra subject:=emailTitle, _
            mailbody:=strbody, toaddress:=recipient, _
            ccaddress:=CStr(cc), bccaddress:=CStr(bcc), _
            displaymail:="yes", thesender:=senderDetails, _
        FileFormat:=ThisWorkbook.FileFormat

Next r

End Sub

Step 4: MacExcel2016WithMacMailSierra subroutine

Again, do the admin stuff — Declaration of function of variables.

Function MacExcel2016WithMacMailSierra(subject As String, mailbody As String, toaddress As String, ccaddress As String, bccaddress As String, displaymail As String, thesender As String, FileFormat As Long)


Dim FileExtStr As String, FileFormatNum As Long
Dim TempFilePath As String, fileattachment As String
Dim ScriptStr As String, RunMyScript As String

Next, find the file format.

'if version 16 it will follow the Win Excel formats numbers
 
If Val(Application.Version) >= 16 Then
   
  Select Case FileFormat
            
    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51           
    Case 52:
                
        If ActiveWorkbook.HasVBProject Then       
              FileExtStr = ".xlsm": FileFormatNum = 52
        Else      
              FileExtStr = ".xlsx": FileFormatNum = 51   
        End If
            
    Case 56: FileExtStr = ".xls": FileFormatNum = 56
           
    Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
    
   End Select
Else  'Version 15 of Excel 2016 and Excel 2011 = +1    
  Select Case FileFormat
        Case 52: FileExtStr = ".xlsx": FileFormatNum = 52
        Case 53:    
          If ActiveWorkbook.HasVBProject Then       
                 FileExtStr = ".xlsm": FileFormatNum = 53
           Else           
                FileExtStr = ".xlsx": FileFormatNum = 52
           End If   
        
         Case 57: FileExtStr = ".xls": FileFormatNum = 57
        Case Else: FileExtStr = ".xlsb": FileFormatNum = 51
    
  End Select
  
End If

What a long script! Let us know if you can optimize this. We also need to prepare the string argument for our AppleScript. This is simply concatenating all the email fields by ; as a delimiter.

' Build the AppleScriptTask parameter string
    

ScriptStr = subject & ";" & mailbody & ";" & toaddress & ";" & ccaddress & ";" & bccaddress & ";" & displaymail & ";" & attachment & ";" & thesender

Finally, call our AppleScript by using an Excel built-in subroutine AppleScriptTask. Note that the first argument is our AppleScript file name.

'Call the RDBMacmail.scpt script file with the AppleScriptTask function
    

RunMyScript = AppleScriptTask("macMail.scpt", "CreateMailInMailSierra", CStr(ScriptStr))


End Function

Final Thoughts

Running macro on Mac is always harder and there are less resources. Hopefully this article clarifies how to email using Mail! The full file can be download here.

Remember to subscribe to us for more tutorials and find out about Cognatics Lab. We would love to assist in customizing this script for your use case too （：

Cognatics Lab is a machine learning consultancy that implements data driven solution to business problems.

hello@cognaticslab.com

LinkedIn | Medium | Github