Hi,

I am trying to send out an automated email to notify users that a job has been completed I am trying to get the macro to send out an email whenever the date in column 27 equals todays date. In the subject I would like to have the job name inserted this can be found in column 5 of the spreadsheet.

Anyone have any ideas?


Sub SendEmail()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields



        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
                           = "SMTP server here"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
            .Update
        End With
        
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Not IsEmpty(Cells(i, 5)) And Cells(i, 27).Formula = "Today()" Then
        
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "Email@blank.net"
        .CC = ""
        .BCC = ""
        .From = """JobMonitor"" <Email@blank>"
        .Subject = "JOB COMPLETE"
        .TextBody = strbody
        .Send
    End With
        End If
         
         
         
    Next i
End Sub