Send Email in Excel 2011 (Mac) using Mac Mail on date in specific cell

**drjanand** · 06-29-2012, 04:35 PM

Hello,

I've searched the programming forum, and did not see anything that answered my question specifically. If you know of a thread that does, I will be happy to look at it. I'm a neophyte when it comes to VBA and macro programming, and need to create a macro that will send an email to specific recipients when the date in a certain cell = today's date. I don't need to attach a workbook, worksheet or range of cells to the mail. Since some employees use Outlook, I would need code for both Mac Mail and Outlook.

I would like the subject to reference a specific cell, as well as have the text 'Validation'; e.g. Subject: <display info from cell B1> Validation
I would have the body text simply say: "This test is approved for implementation."

The trigger for sending the email would be when the date (result of a calculation in cell G37) = the current (Today's) date.
When the email has been sent, place a '1' in the "Mail Sent" column N of the same row as the triggering date (e.g. Row 37)

The email addresses are listed in the same sheet as the other data beginning at C108.

Thank you for any assistance you can provide!

**drjanand** · 07-16-2012, 04:09 PM

Please disregard this post, and refer to the one below it, that has the excel file attached. I did not realize I posted twice.

Here is my attempt to create a solution for emailing multiple recipients from Excel 2011 for Mac using MacMail application. Special thanks to Ron de Bruin for providing much of the macro VBA code. His macro did not specifically address my question, and I've tried to adapt his code for my uses.

I've tried using similar code with the email addresses in the same worksheet as all the other data and could not get the email to send to multiple people. For ease of use, I created a separate worksheet named 'Contacts' that contains the filterable list of email names and addresses. I don't need to attach any excel data to the email, so there is no need to save any files for attachments.

There is an error somewhere in here that I can't figure out. I believe it is in the MacForMail function using the vlookup. I know little about VBA coding, and am still traumatized from learning Basic back in the early 80's :-D. Here is my code, and thank you in advance for any help.

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 Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim mailAddress As String
    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 range and filter column (column with names)
    Set FilterRange = Worksheets("Contacts").Range("A1:B" & Ash.Rows.Count)
    FieldNum = 2    'Filter column = B, first column in the FilterRange

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Contacts.Columns(2))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

                'Look for the mail address in the Contacts worksheet
            mailAddress = ""
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                    VLookup(Contacts.Cells(Rnum, 1).Value, _
                        Worksheets("Contacts").Range("A3:B" & _
                        Worksheets("Contacts").Rows.Count), 2, False)
            On Error GoTo 0

    If mailAddress <> "" Then

                    MailFromMacWithMail _
                                    bodycontent:="Hello, the Director of this project says to 'busta move' on Test Implementation! Here is some pertinent information:  " & Worksheets("Daily").Range("C1:D8"), _
                                    mailsubject:=Daily.Range("C1") & " Test Implementation Breaking News, _
                                    toaddress:=mailAddress, _
                                    ccaddress:="", bccaddress:="", _
                                    attachment:=False
                                    displaymail:=False

                    .Close SaveChanges:=False
                End With

            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
    End If

cleanup:
    Application.DisplayAlerts = True

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Please let me know what I've done wrong here! I really need to get this working and am running out of time.

**drjanand** · 07-16-2012, 04:15 PM

Here is my attempt to create a solution for emailing multiple recipients from Excel 2011 for Mac using MacMail application. Special thanks to Ron de Bruin for providing much of the macro VBA code. His macro did not specifically address my question, and I've tried to adapt his code for my uses.

I've tried using similar code with the email addresses in the same worksheet as all the other data and could not get the email to send to multiple people. For ease of use, I created a separate worksheet named 'Contacts' that contains the filterable list of email names and addresses. I don't need to attach any excel data to the email, so there is no need to save any files for attachments.

There is an error somewhere in here that I can't figure out. I believe it is in the MacForMail function using the vlookup. I know little about VBA coding, and am still traumatized from learning Basic back in the early 80's :-D. Here is my code, and thank you in advance for any help.

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 Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim mailAddress As String
    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 range and filter column (column with names)
    Set FilterRange = Worksheets("Contacts").Range("A1:B" & Ash.Rows.Count)
    FieldNum = 2    'Filter column = B, first column in the FilterRange

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Contacts.Columns(2))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

                'Look for the mail address in the Contacts worksheet
            mailAddress = ""
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                    VLookup(Contacts.Cells(Rnum, 1).Value, _
                        Worksheets("Contacts").Range("A3:B" & _
                        Worksheets("Contacts").Rows.Count), 2, False)
            On Error GoTo 0

    If mailAddress <> "" Then

                    MailFromMacWithMail _
                                    bodycontent:="Hello, the Director of this project says to 'busta move' on Test Implementation! Here is some pertinent information:  " & Worksheets("Daily").Range("C1:D8"), _
                                    mailsubject:=Daily.Range("C1") & " Test Implementation Breaking News, _
                                    toaddress:=mailAddress, _
                                    ccaddress:="", bccaddress:="", _
                                    attachment:=False
                                    displaymail:=False

                    .Close SaveChanges:=False
                End With

            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
    End If

cleanup:
    Application.DisplayAlerts = True

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Please let me know what I've done wrong here! I really need to get this working and am running out of time. I've attached the updated excel file that is macro enabled for reference.
Excel2011EmailMacro_MacMail.xlsm

**drjanand** · 07-17-2012, 10:32 AM

Problem solved! God. I came in this morning, made a single change, and it all works! Here is the code for those that need to send an email triggered by a value in an excel spreadsheet. It will send to multiple recipients (an individual email for each) listed on a contacts sheet using VLookup. This code uses Mac Mail as the mail application.

Sub Send_Email_to_Multiple_Recipients()
'For Excel 2011 for the Mac and Apple Mail
    Dim rng As Range
    Dim Ash As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim mailAddress As String

    On Error GoTo cleanup

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    'Set Contacts sheet as the sheet to filter
    Worksheets("Contacts").Activate
    Set Ash = ActiveSheet

    'Set filter range and filter column (column with names)
    Set FilterRange = Ash.Range("A3:B" & Ash.Rows.Count)
    FieldNum = 1    'Filter column = A, first column in the FilterRange

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Ash.Columns(1))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

            'Look for the mail address in the Contacts worksheet
            mailAddress = ""
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                   VLookup(Ash.Cells(Rnum, 1).Value, _
                          Worksheets("Contacts").Range("A4:D" & _
                              Worksheets("Contacts").Rows.Count), 2, False)
            On Error GoTo 0

            If mailAddress <> "" Then

                'Filter the FilterRange on the FieldNum column
                FilterRange.AutoFilter Field:=FieldNum, _
                                       Criteria1:=Ash.Cells(Rnum, 1).Value

                    MailFromMacWithMail _
                                    bodycontent:="The project Director says to 'Busta Move' on Implementation. Projected Launch Date is " & Worksheets("Progress").Range("D6") & ".", _
                                    mailsubject:=Worksheets("Progress").Range("C1") & " Validation Breaking News!", _
                                    toaddress:=mailAddress, _
                                    ccaddress:="", bccaddress:="", _
                                    attachment:="", _
                                    displaymail:=False

            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
        
    End If
    
    Worksheets("Progress").Range("O37").Value = "Y"

cleanup:
    Application.DisplayAlerts = True

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

I hope this is useful to others. Thanks again to Ron de Bruin's excellent VBA coding skills that provided the foundation for this.