Sending Mail from Excel 2011 by Using Apple Mail or Outlook 2011
Summary: Learn how to send mail from Microsoft Excel 2011 for the Mac with Apple Mail and Microsoft Outlook 2011.
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
Creating the Sample Workbook
Sending the Entire Workbook
Mailing a Single Worksheet
Mailing Multiple Worksheets
Mailing a Range or Selection
Mailing Specific Worksheets
Mail One or More Rows to Each Person in a Range
Conclusion
Additional Resources
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
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
Create an empty Excel 2011 workbook.
Save the file with the .xlsm (macro-enabled file in Excel 2011) file name extension.
Open the Visual Basic Editor by clicking Tools, Macro, and then click Visual Basic Editor.
Insert a standard module by clicking Insert and then clicking Module.
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.
Save the workbook.
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)
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.
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
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 SubTo send a workbook other than the active workbook, change the assignment to the wb variable. For example: Set wb = ThisWorkbook.
Note
It doesn't have to be the active workbook in use at the time the subroutine is called.
With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
Select the Mail_workbook_Excel2011_1 subroutine and then click Run.
To send the entire workbook (Example 2)
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.
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
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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.
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
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 SubYou 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.
With the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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.
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
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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.
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
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
Select the Mail_Range_In_Excel2011 subroutine and then click Run.
To mail a selection
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.
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
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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.
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
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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
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
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
Paste or type the following subroutine into the module created at the beginning of this article.
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
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
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
Paste or type the following subroutine into the module created at the beginning of this article.
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 SubWith the cursor in a Visual Basic Editor screen, press CMD +Q to close the Visual Basic Editor and return to Microsoft Excel.
To run the subroutine, on the Tools menu, click Macro, and then click Macros.
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
Find more information about the topics discussed here in the following locations: