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

[drjanand]

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]

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.

Please Login or Register  to view this content.

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

[drjanand]

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.

Please Login or Register  to view this content.

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]

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.

Please Login or Register  to view this content.

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