This code works! Thanks for your advise.
Sub Reminder()
Dim i As Integer
Dim Sendmsg
Dim rng As Range
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Dim TextBody As String
Dim ApprovedTextBody As String
Dim Finfo As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Finfo = "All Files (*.*),*.*"
Title = "E-Mail Attachment: Select file to attach."
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
'mail server details
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mail_server
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With
i = 1
Do While Worksheets("INVOICES").Cells(i, 3) <> ""
If Worksheets("INVOICES").Cells(i, 12).Value <= Date + 7 And Worksheets( _
"INVOICES").Cells(i, 18) = "" Then
Worksheets("INVOICES").Rows(i).Interior.Color = vbYellow
TextBody = "Hello," & vbNewLine & vbNewLine & _
"The attached invoice is still awaiting approval. Kindly advise if this invoice is " & _
"approved for payment as soon as you get a chance. " & vbNewLine & vbNewLine & _
Worksheets("INVOICES").Cells(i, 6).Text & " Inv " & _
Worksheets("INVOICES").Cells(i, 5).Text & vbNewLine & "JSID " & _
Worksheets("INVOICES").Cells(i, 3).Text & vbNewLine & _
Worksheets("INVOICES").Cells(i, 11).Text & vbNewLine & _
Worksheets("INVOICES").Cells(i, 7).Text & " " & Worksheets("INVOICES").Cells(i, 8).Text & _
vbNewLine & vbNewLine & "Thank you," & vbNewLine & "cschoyer"
With iMsg
Set .Configuration = iConf
.To = Worksheets("INVOICES").Cells(i, 13).Text + "@xyz.com"
.CC = "cschoyer@xyz.com"
.BCC = ""
.From = """cschoyer"" <cschoyer@xyz.com>"
.Subject = "Pending Approval " & Worksheets("INVOICES").Cells(i, 6).Text & " Inv " & _
Worksheets("INVOICES").Cells(i, 5).Text & " JSID " & _
Worksheets("INVOICES").Cells(i, 3).Text
.TextBody = TextBody
.Send
End With
End If
i = i + 1
Loop
Sendmsg = MsgBox(Prompt:="Are you sure you're ready to send?", _
Buttons:=vbYesNo, Title:="Warning")
If Sendmsg = vbNo Then
Exit Sub
Else
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub
Bookmarks