automtic email notification

[jmatthews]

Can someone please help, I have attached an example. Basically what i am wanting to do is insert a macro that will check all of the cells in column AE and if any cell is empty on the date in the same row of that cell in column AF than a email is sent notifying me and copying someone else.Book2.xlsx

[jmatthews]

is there anyone who could please help me with this?

[nje]

jmatthews, I can't help you with athe code you want, but someone from this forum I think it was john55 (apologies if it was someone else) post this link to some very useful codes about emailing
http://www.rondebruin.nl/sendmail.htm
I do use a very succesful auto email code which sends out a message when a date in a certain cell is reached or has past which you or someone else might be able to adapt for your needs, If you want it I'll post it Good Luck

[nje]

jmatthews, I can't help you with athe code you want, but someone from this forum I think it was john55 (apologies if it was someone else) post this link to some very useful codes about emailing
http://www.rondebruin.nl/sendmail.htm
I do use a very succesful auto email code which sends out a message when a date in a certain cell is reached or has past which you or someone else might be able to adapt for your needs, If you want it I'll post it Good Luck

[jmatthews]

nje,
Thank you very much. If you could post that code i would greatly appreciate it. I may be able to use it or modify it to fit my needs, thank you for responding.

[nje]

I hope it helps. I have attached a workbook where the codes are working. I thought if you saw what it does it might help you see how you can adapt it. Good Luck
Remember to put your email address in the worksheet column 'J' so it can send you the message. It auto sends when the book is first opened so after putting in your email address close the book and then re-open it

[nje]

I have uploaded your workbook with the code I use in it, if you put your email address in column AP it will send you the message from AQ. AR.
I wish I could help you a bit more but I'm just fumbling my way around the coding although with the help of those on this forum I am improving.
I have put a macro button on your sheet for testing purposes so you don't have to keep closing and opening the book to see if it works - Good Luck

[john55]

or

Sub Button39_Click()
Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim MyName As String

    MyName = ThisWorkbook.Name



    For i = Range("ae65536").End(xlUp).Row To 2 Step -1
    If IsEmpty(Cells(i, 32)) And Cells(i, 31) < Date Then

       
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("1.2013")
                strto = .Cells(i, 39).Value 'email address from column am
                strcc = ""
                strbcc = ""
                strsub = MyName
                strbody = "Hi there," & "pls see" & " " & MyName & vbNewLine & vbNewLine & _
                    .Cells(i, 1).Value & " " & _
                    "needs an updated...." & " " & .Cells(i, 7).Value & " " & _
                    "because..." & " " & .Cells(i, 11).Value & "." & _
                    vbCrLf & vbCrLf & "Thank you."
                    'need to change the column for your text....
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                 
     'color in red cells in col A when the email is sent and add date in col af
     Cells(i, 32).Value = Date
     Cells(i, 1).Select
     With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
  
    Next i
End Sub

[jmatthews]

Book2.xlsx

Ok I'm thinking this might be easier. I want to have a code that I can put in multiple worksheets throughout the workbook that will send an email when the date in column AE is reached. I hopefully want to see if it can only send one email per day no matter how many dates are reached or how many times the workbook is opened. Also is it possible to have something like this run automatically without opening the workbook?

[john55]

try this
add some email addresses in col AM for testing...
instead of using the button you can add the code into

Private Sub Workbook_Open()
'
'
'your code
End Sub

and it's activated when you open the workbook.

Sub Button1_Click()
Dim ce As Range, i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Dim MyName As String
Application.ScreenUpdating = 0
    For Each ws In Worksheets
        MyName = ThisWorkbook.Name

    For i = Range("ae65536").End(xlUp).Row To 2 Step -1
    If Cells(i, 31) = Date Then

       
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With ws 'Sheets("1.2013")
                strto = .Cells(i, 39).Value 'email address from column am
                strcc = ""
                strbcc = ""
                strsub = MyName
                strbody = "Hi there," & "pls see" & " " & MyName & vbNewLine & vbNewLine & _
                    .Cells(i, 1).Value & " " & _
                    "needs an updated...." & " " & .Cells(i, 7).Value & " " & _
                    "because..." & " " & .Cells(i, 11).Value & "." & _
                    vbCrLf & vbCrLf & "Thank you."
                    'need to change the column for your text....
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                '.Send
                .display
                 
     'color in red cells in col A when the email is sent and add date in col af
     
     Cells(i, 1).Select
     With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
  
    Next i
    
    Next ws
      Application.ScreenUpdating = 1
End Sub