Export (0) Print
Expand All

Sending Mail from Excel 2011 by Using Apple Mail or Outlook 2011

Office 2010

Summary:   Learn how to send mail from Microsoft Excel 2011 for the Mac with Apple Mail and Microsoft Outlook 2011.

Last modified: March 08, 2012

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

Published:   February 2012

Provided by:  Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation

Contents

Introduction to Mail for the Apple Macintosh

Apple Mail is the powerful and easy to use email program built into the Macintosh operating system. With support for multiple POP, IMAP, Exchange and MobileMe accounts, versatile mail filters and a smart conversation view, Apple Mail is flexible enough for most needs.

Microsoft Outlook 2011 offers powerful ways to integrate your email, calendar, task lists, and address book, and many options for organizing your email. It also provides flexible, secure ways to enable other people to manage your email and calendar.

Additionally, it is now easier than ever to send mail from Microsoft Excel 2011. However, because there are a few problems in the Microsoft Visual Basic for Applications (VBA) SendMail method in Excel 2011 and there is no Outlook object model in Outlook 2011 like there is in the Outlook versions for Microsoft Windows, you must run an AppleScript string with the built-in VBA MacScript function. This technique is the subject of the remainder of this article. You will see code samples using Apple Mail and Outlook 2011.

Note Note

AppleScript is a scripting language created for the Apple computer and is built into the Macintosh operating systems.

Creating the Sample Workbook

In this section, you first create a sample workbook that is used in the samples. You then add three functions that are common to Apple Mail and Outlook 2011.

To create and populate the sample workbook

  1. Create an empty Excel 2011 workbook.

  2. Save the file with the .xlsm (macro-enabled file in Excel 2011) file name extension.

  3. Open the Visual Basic Editor by clicking Tools, Macro, and then click Visual Basic Editor.

  4. Insert a standard module by clicking Insert and then clicking Module.

  5. Paste or type the following three functions into the module. There is one function for Apple Mail (MailFromMacWithMail), one for Outlook 2011 (MailFromMacwithOutlook), and one (KillFileOnMac) to delete the temporary files created in the other examples described later.

  6. Save the workbook.

    Note Note

    Do not change anything in the following three functions. The macro examples discussed later in this article will use them so they must be in the workbook as is.

    Function MailFromMacwithOutlook(bodycontent As String, mailsubject As String, _
                toaddress As String, ccaddress As String, bccaddress As String, _
                attachment As String, displaymail As Boolean)
    
    
    
        Dim scriptToRun As String
    
        scriptToRun = scriptToRun & "tell application " & _
                      Chr(34) & "Microsoft Outlook" & Chr(34) & Chr(13)
    
        scriptToRun = scriptToRun & _
         "set NewMail to make new outgoing message with properties" & _
           "{content:""" & bodycontent & """, subject:""" & mailsubject & """}" & Chr(13)
    
        If toaddress <> "" Then scriptToRun = scriptToRun & _
           "make new to recipient at NewMail with properties" & _
           "{email address:{address:""" & toaddress & """}}" & Chr(13)
    
        If ccaddress <> "" Then scriptToRun = scriptToRun & _
           "make new cc recipient at NewMail with properties" & _
           "{email address:{address:""" & ccaddress & """}}" & Chr(13)
    
        If bccaddress <> "" Then scriptToRun = scriptToRun & _
           "make new bcc recipient at NewMail with properties" & _
           "{email address:{address:""" & bccaddress & """}}" & Chr(13)
    
        If attachment <> "" Then
         scriptToRun = scriptToRun & "make new attachment at NewMail with properties" & _
                  "{file:""" & attachment & """ as alias}" & Chr(13)
        End If
    
        If displaymail = False Then
            scriptToRun = scriptToRun & "send NewMail" & Chr(13)
        Else
            scriptToRun = scriptToRun & "open NewMail" & Chr(13)
        End If
        scriptToRun = scriptToRun & "end tell" & Chr(13)
    
        If Len(toaddress) + Len(ccaddress) + Len(bccaddress) = 0 Or mailsubject = "" Then
            MsgBox "There is no To, CC or BCC address or Subject for this mail"
            Exit Function
        Else
            On Error Resume Next
            MacScript (scriptToRun)
            On Error GoTo 0
        End If
    End Function
    
    Function MailFromMacWithMail(bodycontent As String, mailsubject As String, _
               toaddress As String, ccaddress As String, bccaddress As String, _
                    attachment As String, displaymail As Boolean)
    
    
    
        Dim scriptToRun As String
    
        scriptToRun = scriptToRun & "tell application " & _
                      Chr(34) & "Mail" & Chr(34) & Chr(13)
    
        scriptToRun = scriptToRun & _
             "set NewMail to make new outgoing message with properties " & _
                "{content:""" & bodycontent & """, subject:""" & _
                   mailsubject & """ , visible:true}" & Chr(13)
    
        scriptToRun = scriptToRun & "tell NewMail" & Chr(13)
    
        If toaddress <> "" Then scriptToRun = scriptToRun & _
           "make new to recipient at end of to recipients with properties " & _
           "{address:""" & toaddress & """}" & Chr(13)
    
        If ccaddress <> "" Then scriptToRun = scriptToRun & _
           "make new cc recipient at end of cc recipients with properties " & _
           "{address:""" & ccaddress & """}" & Chr(13)
    
        If bccaddress <> "" Then scriptToRun = scriptToRun & _
           "make new bcc recipient at end of bcc recipients with properties " & _
           "{address:""" & bccaddress & """}" & Chr(13)
    
        If attachment <> "" Then
            scriptToRun = scriptToRun & "tell content" & Chr(13)
            scriptToRun = scriptToRun & "make new attachment with properties " & _
                          "{file name:""" & attachment & """ as alias} " & _
                          "at after the last paragraph" & Chr(13)
            scriptToRun = scriptToRun & "end tell" & Chr(13)
        End If
    
        If displaymail = False Then scriptToRun = scriptToRun & "send" & Chr(13)
        scriptToRun = scriptToRun & "end tell" & Chr(13)
        scriptToRun = scriptToRun & "end tell"
    
        If Len(toaddress) + Len(ccaddress) + Len(bccaddress) = 0 Or mailsubject = "" Then
            MsgBox "There is no To, CC or BCC address or Subject for this mail"
            Exit Function
        Else
            On Error Resume Next
            MacScript (scriptToRun)
            On Error GoTo 0
        End If
    End Function
    
    Function KillFileOnMac(Filestr As String)
    
    
    'The VBA Kill command on a Mac will not work with long file names(28+ characters)
        Dim ScriptToKillFile As String
        ScriptToKillFile = ScriptToKillFile & "tell application " & Chr(34) & _
                           "Finder" & Chr(34) & Chr(13)
        ScriptToKillFile = ScriptToKillFile & _
                           "do shell script ""rm "" & quoted form of posix path of " & _
                           Chr(34) & Filestr & Chr(34) & Chr(13)
        ScriptToKillFile = ScriptToKillFile & "end tell"
    
        On Error Resume Next
        MacScript (ScriptToKillFile)
        On Error GoTo 0
    End Function
    

Sending the Entire Workbook

In this section, you add VBA code to the workbook to mail the entire workbook. Two examples of doing this are shown. To organize the code samples in this article, you need to create a container module by clicking the Insert menu, and then clicking Module.

To send the entire workbook (Example 1)

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine sends the last saved version of the active workbook in an email message in Apple Mail.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_workbook_Excel2011_1()
    'For Excel 2011 for the Mac and Apple Mail
    'Note: The workbook must be saved once
        Dim wb As Workbook
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        Set wb = ActiveWorkbook
        With wb
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Whole workbook 1", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=.FullName, _
                        displaymail:=False
        End With
        Set wb = Nothing
    
    End Sub
    
  3. To send a workbook other than the active workbook, change the assignment to the wb variable. For example: Set wb = ThisWorkbook.

    Note Note

    It doesn't have to be the active workbook in use at the time the subroutine is called.

  4. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  5. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  6. Select the Mail_workbook_Excel2011_1 subroutine and then click Run.

To send the entire workbook (Example 2)

  1. Paste or type the following subroutine into the module created previously. This subroutine sends a newly created workbook (copy of the ActiveWorkbook) in an email message in Apple Mail. It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook is deleted from your hard disk.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_Workbook_Excel2011_2()
    'For Excel 2011 for the Mac and Apple Mail
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        Set wb = ActiveWorkbook
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        ''Save the new workbook, mail it, and then delete it.
        'If you want to change the file name then change only TempFileName
        TempFilePath = MacScript("return (path to documents folder) as string")
        TempFileName = "Copy of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
        FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1)))
    
        With wb
            .SaveCopyAs TempFilePath & TempFileName & FileExtStr
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Whole workbook 2", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=TempFilePath & TempFileName & FileExtStr, _
                        displaymail:=False
        End With
        Set wb = Nothing
    
        KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  4. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  5. Select the Mail_Workbook_Excel2011_2 subroutine and then click Run.

Mailing a Single Worksheet

In this section, you add a subroutine that sends the newly created workbook with just the active sheet (ActiveSheet). It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook will be deleted from your hard disk.

To send a single worksheet

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine sends a worksheet in an email message in Apple Mail. It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook is deleted from your hard disk.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_ActiveSheet_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the ActiveSheet to a new workbook
        'You can also use Sheets("MySheetName").Copy
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
    
        'Determine file extension and format
        With Destwb
            Select Case Sourcewb.FileFormat
            Case 52: FileExtStr = ".xlsx": FileFormatNum = 52
            Case 53:
                If .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 With
    
        '    'Change all cells in the worksheet to values
        '    With Destwb.Sheets(1).UsedRange
        '        .Cells.Copy
        '        .Cells.PasteSpecial xlPasteValues
        '        .Cells(1).Select
        '    End With
        '    Application.CutCopyMode = False
    
        'Save the new workbook, mail it, and then delete it.
        'If you want to change the file name then change only TempFileName.
        TempFilePath = MacScript("return (path to documents folder) as string")
        TempFileName = "Part of " & Sourcewb.Name & " " _
                     & Format(Now, "dd-mmm-yy h-mm-ss")
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Mail activesheet test", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=.FullName, _
                        displaymail:=False
            .Close SaveChanges:=False
        End With
    
        Set Destwb = Nothing
    
        KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. You can also use the following line if you know the sheet you want to mail: Sheets("Sheet5").Copy. It doesn't have to be the active sheet in use at the time the subroutine is run.

  4. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel

  5. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  6. Select the Mail_ActiveSheet_In_Excel2011 subroutine and then click Run.

Mailing Multiple Worksheets

In this section, you add a subroutine to mail multiple worksheets.

To mail multiple worksheets

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine sends multiple worksheets in an email message in Apple Mail. It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook is deleted from your hard disk. In the example below Sheet1 and Sheet3 will be sent; change to fit that to your needs.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_Sheets_Array_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim sh As Worksheet
        Dim TheActiveWindow As Window
        Dim TempWindow As Window
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the sheets to a new workbook
        'We add a temporary Window to avoid the Copy problem
        'if there is a List or Table in one of the sheets and
        'if the sheets are grouped
        With Sourcewb
            Set TheActiveWindow = ActiveWindow
            Set TempWindow = .NewWindow
            .Sheets(Array("Sheet1", "Sheet3")).Copy
        End With
    
        'Close the temporary window
        TempWindow.Close
    
        Set Destwb = ActiveWorkbook
    
        'Determine the file extension and format
        With Destwb
            Select Case Sourcewb.FileFormat
            Case 52: FileExtStr = ".xlsx": FileFormatNum = 52
            Case 53:
                If .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 With
    
        '    'Change all cells in the worksheets to values if you want.
        '    For Each sh In Destwb.Worksheets
        '        sh.Select
        '        With sh.UsedRange
        '            .Cells.Copy
        '            .Cells.PasteSpecial xlPasteValues
        '            .Cells(1).Select
        '        End With
        '        Application.CutCopyMode = False
        '        Destwb.Worksheets(1).Select
        '    Next sh
    
        'Save the new workbook, mail it, and then delete it.
        'If you want to change the file name then change only TempFileName
        TempFilePath = MacScript("return (path to documents folder) as string")
        TempFileName = "Part of " & Sourcewb.Name & " " _
                     & Format(Now, "dd-mmm-yy h-mm-ss")
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Mail more than one sheet test", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=.FullName, _
                        displaymail:=False
            .Close SaveChanges:=False
        End With
    
        Set Destwb = Nothing
    
        KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  4. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  5. Select the Mail_Sheets_Array_In_Excel2011 subroutine and then click Run.

Mailing a Range or Selection

In this section, you mail a range or a selected portion of a range. The first subroutine sends a newly created workbook with just the visible cells in the Range("A1:K50").The cells will use PasteSpecial as values in the workbook you send.

The second subroutine sends a newly created workbook with just the visible cells in the Selection. The cells will also use PasteSpecial as values in the workbook you send.

To mail a range

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine sends a range of data in an email message in Apple Mail. It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook is deleted from your hard disk.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_Range_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim Source As Range
        Dim Destwb As Workbook
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        Set Source = Nothing
        On Error Resume Next
        Set Source = Range("A1:K50").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If Source Is Nothing Then
            MsgBox "The source is not a range or the sheet is protected, " & _
                   "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set wb = ActiveWorkbook
        Set Destwb = Workbooks.Add(xlWBATWorksheet)
    
        Source.Copy
        With Destwb.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial Paste:=xlPasteValues
            .Cells(1).PasteSpecial Paste:=xlPasteFormats
            .Cells(1).Select
            Application.CutCopyMode = False
        End With
    
        'Save format and extension
        FileExtStr = ".xlsx": FileFormatNum = 52
    
        'Or if you want it in xls format, use:
        'FileExtStr = ".xls": FileFormatNum = 57
    
    
        'Save the new workbook, mail it, and then delete it.
        'If you want to change the file name then change only TempFileName
        TempFilePath = MacScript("return (path to documents folder) as string")
        TempFileName = "Range of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Mail Range Test", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=.FullName, _
                        displaymail:=False
            .Close SaveChanges:=False
        End With
    
        KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  4. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  5. Select the Mail_Range_In_Excel2011 subroutine and then click Run.

To mail a selection

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine sends a selection of data with just the visible cells in an email message in Apple Mail. It saves the workbook with a date/time stamp before mailing it. After the file is sent, the workbook is deleted from your hard disk.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_Selection_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim Source As Range
        Dim Destwb As Workbook
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        Set Source = Nothing
        On Error Resume Next
        Set Source = Selection.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If Source Is Nothing Then
            MsgBox "The source is not a range or the sheet is protected, " & _
                   "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        If ActiveWindow.SelectedSheets.Count > 1 Or _
           Selection.Cells.Count = 1 Or _
           Selection.Areas.Count > 1 Then
            MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
                   "You have more than one sheet selected." & vbNewLine & _
                   "You only selected one cell." & vbNewLine & _
                   "You selected more than one area." & vbNewLine & vbNewLine & _
                   "Please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set wb = ActiveWorkbook
        Set Destwb = Workbooks.Add(xlWBATWorksheet)
    
        Source.Copy
        With Destwb.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial Paste:=xlPasteValues
            .Cells(1).PasteSpecial Paste:=xlPasteFormats
            .Cells(1).Select
            Application.CutCopyMode = False
        End With
    
        'Save format and extension
        FileExtStr = ".xlsx": FileFormatNum = 52
    
        'Or if you want it in xls format, use:
        'FileExtStr = ".xls": FileFormatNum = 57
    
    
        'Save the new workbook, mail it, and delete it.
        'If you want to change the file name then change only TempFileName
        TempFilePath = MacScript("return (path to documents folder) as string")
        TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            MailFromMacWithMail bodycontent:="Hi there", _
                        mailsubject:="Mail Selection Test", _
                        toaddress:="ron@debruin.nl", _
                        ccaddress:="", _
                        bccaddress:="", _
                        attachment:=.FullName, _
                        displaymail:=False
            .Close SaveChanges:=False
        End With
    
        KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  4. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  5. Select the Mail_Selection_In_Excel2011 subroutine and then click Run.

Mailing Specific Worksheets

The subroutine in this section mails every worksheet with an address in cell A1.

To mail specific worksheets

  1. Paste or type the following subroutine into the module created at the beginning of this article. This subroutine works by cycling through each worksheet in the workbook and checking cell A1 for the @ character. If the character is found, a copy of the worksheet is made and saved with a date/time stamp, and then sent to the address in cell A1. And finally, the file is deleted from your hard disk.

  2. Change the mail address and subject in the macro to suit your needs. If you want to display the mail item for testing, change the last argument in the function to True.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Mail_Every_Worksheet_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim sh As Worksheet
        Dim wb As Workbook
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim TempFilePath As String
        Dim TempFileName As String
    
        If Val(Application.Version) < 14 Then Exit Sub
    
        TempFilePath = MacScript("return (path to documents folder) as string")
    
        'Determine the file extension and format
        Select Case ThisWorkbook.FileFormat
        Case 52: FileExtStr = ".xlsx": FileFormatNum = 52
        Case 53: FileExtStr = ".xlsm": FileFormatNum = 53
        Case 57: FileExtStr = ".xls": FileFormatNum = 57
        Case Else: FileExtStr = ".xlsb": FileFormatNum = 51
        End Select
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        For Each sh In ThisWorkbook.Worksheets
            If sh.Range("A1").Value Like "?*@?*.?*" Then
    
                sh.Copy
                Set wb = ActiveWorkbook
    
                '    'Change all cells in the worksheet to values if you want
                '    With wb.Sheets(1).UsedRange
                '        .Cells.Copy
                '        .Cells.PasteSpecial xlPasteValues
                '        .Cells(1).Select
                '    End With
                '    Application.CutCopyMode = False
    
                TempFileName = "Sheet " & sh.Name & " of " _
                        & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
                With wb
                    .SaveAs TempFilePath & TempFileName & FileExtStr, _
                        FileFormat:=FileFormatNum
                    MailFromMacWithMail bodycontent:="Hi there", _
                                mailsubject:="Every sheet with mail address in A1", _
                                toaddress:=sh.Range("A1").Value, _
                                ccaddress:="", _
                                bccaddress:="", _
                                attachment:=.FullName, _
                                displaymail:=False
                    .Close SaveChanges:=False
                End With
    
                KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
            End If
        Next sh
    
        Set wb = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    
  3. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  4. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  5. Select the Mail_Every_Worksheet_In_Excel2011 subroutine and then click Run.

Mail One or More Rows to Each Person in a Range

The subroutines in this section send mail to one or more email addresses found in a workbook. The primary difference between the subroutines is that one uses the VLookup function to look up the email addresses in a separate worksheet. The other subroutine sends the data to the addresses found on the same sheet as the data.

Important note Important

For both subroutines, the following conditions apply:

  • The code does not work if your data is in a table.

  • The first row in the range must have headers.

  • Turn off AutoFilter before you use the code.

  • Make sure that the sheet with the data is the active worksheet.

In the first subroutine, the worksheet must have the following defined:

  • In column A: the names of the recipient

  • In columns B:H: information about the recipient

The range A1:H? is filtered for every unique name in the name column. For every unique name, a new file is created with only the data for that person, which is then sent to the email address find with the VLookup function in the worksheet Mailinfo.

Important note Important

You must create this worksheet manually and add the names and email addresses one time. Add a worksheet named Mailinfo to your workbook with a person’s name in column A and their email addresses in column B.

Note Note

In this example the filter range is A1:H? (to use all of the rows on the worksheet). You can change the filter range and filter column by replacing the existing lines specifying the FilterRange with the following two lines in the subroutine:

Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)

FieldNum = 1 'Filter column = A because the filter range starts in column A

To mail rows to each person in a range by using VLookup

  1. Paste or type the following subroutine into the module created at the beginning of this article.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Send_Row_Or_Rows_Attachment_In_Excel2011()
    'For Excel 2011 for the Mac and Apple Mail
        Dim rng As Range
        Dim Ash As Worksheet
        Dim Cws As Worksheet
        Dim Rcount As Long
        Dim Rnum As Long
        Dim FilterRange As Range
        Dim FieldNum As Integer
        Dim mailAddress As String
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
    
        On Error GoTo cleanup
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        'Set filter sheet. You can also use Sheets("MySheet")
        Set Ash = ActiveSheet
    
        'Set filter range and filter column (column with names)
        Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
        FieldNum = 1    'Filter column = A, first column in the FilterRange
    
        'Add a worksheet for the unique name list and copy the unique list in A1
        Set Cws = Worksheets.Add
        FilterRange.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=Cws.Range("A1"), _
                CriteriaRange:="", Unique:=True
    
        'Count of the unique values + the header cell
        Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
    
        'If there are unique values start the loop
        If Rcount >= 2 Then
            'Set tempory file path
            TempFilePath = MacScript("return (path to documents folder) as string")
            For Rnum = 2 To Rcount
    
                'Look for the mail address in the MailInfo worksheet
                mailAddress = ""
                On Error Resume Next
                mailAddress = Application.WorksheetFunction. _
                       VLookup(Cws.Cells(Rnum, 1).Value, _
                              Worksheets("Mailinfo").Range("A1:B" & _
                                  Worksheets("Mailinfo").Rows.Count), 2, False)
                On Error GoTo 0
    
                If mailAddress <> "" Then
    
                    'Filter the FilterRange on the FieldNum column
                    FilterRange.AutoFilter Field:=FieldNum, _
                                           Criteria1:=Cws.Cells(Rnum, 1).Value
    
                    'Copy the visible data in a new workbook
                    With Ash.AutoFilter.Range
                        On Error Resume Next
                        Set rng = .SpecialCells(xlCellTypeVisible)
                        On Error GoTo 0
                    End With
    
                    Set Destwb = Workbooks.Add(xlWBATWorksheet)
    
                    rng.Copy
                    With Destwb.Sheets(1)
                        .Cells(1).PasteSpecial Paste:=8
                        .Cells(1).PasteSpecial Paste:=xlPasteValues
                        .Cells(1).PasteSpecial Paste:=xlPasteFormats
                        .Cells(1).Select
                        Application.CutCopyMode = False
                    End With
    
                    'Create a file name. 
                    'If you want to change the file name then change only TempFileName
                    TempFileName = "Your data of " & Ash.Parent.Name _
                                 & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
                    'You can also use a different file format if you want
                    FileExtStr = ".xlsx": FileFormatNum = 52
    
                    With Destwb
                        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                                FileFormat:=FileFormatNum
    
                        MailFromMacWithMail _
                                        bodycontent:="Hi " & Cws.Cells(Rnum, 1).Value, _
                                        mailsubject:="Mail Row or Rows 1", _
                                        toaddress:=mailAddress, _
                                        ccaddress:="", bccaddress:="", _
                                        attachment:=.FullName, _
                                        displaymail:=False
    
                        .Close SaveChanges:=False
                    End With
    
                    KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
                End If
    
                'Close AutoFilter
                Ash.AutoFilterMode = False
    
            Next Rnum
        End If
    
    cleanup:
        Application.DisplayAlerts = False
        Cws.Delete
        Application.DisplayAlerts = True
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    
  2. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  3. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  4. Select the Send Highlight _Row_Or_Rows_Attachment_In_Excel2011 subroutine and then click Run.

In the second subroutine, you must have the following defined:

  • In column A: Names of the recipients

  • In column B: Email addresses of the recipient

  • In columns C:H: Information about the recipient

Note Note

Every row must have a mail address in column B.

The range A1:H? is filtered for every unique mail address in column B. For every unique mail address, a new file is created with only the records with that mail address and then sent to the mail address.

Note Note

In this example the filter range is A1:H? (to use all of the rows on the worksheet). You can change the filter range and filter column by replacing the existing lines specifying the FilterRange with the following two lines in the subroutine:

Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)

FieldNum = 2 'Filter column = B because the filter range starts in column A

To mail rows to each person in a range

  1. Paste or type the following subroutine into the module created at the beginning of this article.

    Note Note

    To create the mail in Outlook 2011 instead, change the name of the function call from MailFromMacWithMail to MailFromMacwithOutlook.

    Sub Send_Row_Or_Rows_Attachment_2()
    'For Excel 2011 for the Mac and Apple Mail
        Dim rng As Range
        Dim Ash As Worksheet
        Dim Cws As Worksheet
        Dim Rcount As Long
        Dim Rnum As Long
        Dim FilterRange As Range
        Dim FieldNum As Integer
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim I As Long
    
        On Error GoTo cleanup
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        'Set filter sheet, you can also use Sheets("MySheet")
        Set Ash = ActiveSheet
    
        'Set filter range and filter column (column with email addresses)
        Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
        FieldNum = 2    'Filter column = B because FilterRange start in column A
    
        'Add a worksheet for the unique list and copy the unique list in A1
        Set Cws = Worksheets.Add
        FilterRange.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=Cws.Range("A1"), _
                CriteriaRange:="", Unique:=True
    
        'Count of the unique values + the header cell
        Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
    
        'If there are unique values start the loop
        If Rcount >= 2 Then
            'Set tempory file path
            TempFilePath = MacScript("return (path to documents folder) as string")
    
            For Rnum = 2 To Rcount
    
                'If the unique value is a mail address create a mail
                If Cws.Cells(Rnum, 1).Value Like "?*@?*.?*" Then
    
                    'Filter the FilterRange on the FieldNum column
                    FilterRange.AutoFilter Field:=FieldNum, _
                                           Criteria1:=Cws.Cells(Rnum, 1).Value
    
                    'Copy the visible data in a new workbook
                    With Ash.AutoFilter.Range
                        On Error Resume Next
                        Set rng = .SpecialCells(xlCellTypeVisible)
                        On Error GoTo 0
                    End With
    
                    Set Destwb = Workbooks.Add(xlWBATWorksheet)
    
                    rng.Copy
                    With Destwb.Sheets(1)
                        .Cells(1).PasteSpecial Paste:=8
                        .Cells(1).PasteSpecial Paste:=xlPasteValues
                        .Cells(1).PasteSpecial Paste:=xlPasteFormats
                        .Cells(1).Select
                        Application.CutCopyMode = False
                    End With
    
                    'Create a file name, if you want to change
                    'the file name then change only TempFileName
                    TempFileName = "Your data of " & Ash.Parent.Name _
                                 & " " & Format(Now, "dd-mmm-yy h-mm-ss")
                    'You can also use a different file format if you want
                    FileExtStr = ".xlsx": FileFormatNum = 52
    
                    With Destwb
                        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                            FileFormat:=FileFormatNum
    
                        MailFromMacWithMail _
                                        bodycontent:="Hi " & Sheets(1).Cells(2, 1).Value, _
                                        mailsubject:="Mail Row or Rows 2", _
                                        toaddress:=Cws.Cells(Rnum, 1).Value, _
                                        ccaddress:="", bccaddress:="", _
                                        attachment:=.FullName, _
                                        displaymail:=False
    
                        .Close SaveChanges:=False
                    End With
    
                    KillFileOnMac TempFilePath & TempFileName & FileExtStr
    
                End If
    
                'Close AutoFilter
                Ash.AutoFilterMode = False
    
            Next Rnum
        End If
    
    cleanup:
        Application.DisplayAlerts = False
        Cws.Delete
        Application.DisplayAlerts = True
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    
  2. With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.

  3. To run the subroutine, on the Tools menu, click Macro, and then click Macros.

  4. Select the Send_Row_Or_Rows_Attachment_2 subroutine and then click Run.

Conclusion

In this article, you saw several examples of how to programmatically send various Excel 2011 components by using email. These methods can be easily adapted to use either Apple Mail or Outlook 2011. You should experiment with these samples so that you are in a good position to creatively modify them for your own purposes.

Additional Resources

Show:
© 2014 Microsoft