Automatically send email from excel

[dfenton21]

Hi All,

I'm not sure if this is possible, but there's no harm in asking:

Firstly, is it possible for excel to send a short email message to a recipient if =COUNTA(C2:C15)<14.

Secondly, is it possible for excel to send that email to multiple email address (each email address is a different cell).

Thanks

 

[nirvana_]

Here is the code to send the e-mail. You need to put it in Worksheet_Change and Worksheet_SelectionChange events for Sheet1.

The script assumes that the formula is put into cell A1 in sheet 1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Worksheets(1).Cells(1, 1).Value < 14 Then
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        With myItem
            .To = "[EMAIL="ToWhoever@google.com"]ToWhoever@google.com[/EMAIL]"
            .Subject = "My Subject"
            .SentOnBehalfOfName = "[EMAIL="ABC@ABC.COM"]ABC@ABC.COM[/EMAIL]"
            .Send
        End With
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Worksheets(1).Cells(1, 1).Value < 14 Then
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        With myItem
            .To = "[EMAIL="ToWhoever@google.com"]ToWhoever@google.com[/EMAIL]"
            .Subject = "My Subject"
            .SentOnBehalfOfName = "[EMAIL="ABC@ABC.COM"]ABC@ABC.COM[/EMAIL]"
            .Send
        End With
    End If
End Sub