Avviso scadenze su foglio Excel

=I5="SI"

=I5="NO"

Sub inviamail()
Dim rng As Range
Dim cel As Range
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim Oggetto As String
Dim IndirizzoMail As String
Dim Testo As String
Dim ur As Long
ur = Cells(Rows.Count, 9).End(xlUp).Row
Set rng = Range("I5:I" & ur)
Set OutlookApp = New Outlook.Application
For Each cel In rng
    If cel.Value = "SI" Then
        Oggetto = "Avviso scadenza" & cel.Offset(0, -4).Value
        IndirizzoMail = "TuoIndirizzomail" '<=== In questo punto devi inserire il tuo indirizzo tra le virgolette
        Testo = "E' in scadenza il contratto di: " & cel.Offset(0, -4)
       Set MItem = OutlookApp.CreateItem(olMailItem)
        With MItem
        .To = IndirizzoMail
        .Subject = Oggetto
        .Body = Testo
        .Display
        '.Send
      End With
    End If
Next
End Sub

Sub InviaMail()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
    Dim MittenteMail, OggettoMail, DestinatarioMail As String
   
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "TuoIndirizzomail"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Tuapassword"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.aruba.it"

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 'questo solo in caso di problemi
        .Update
    End With

   
    strbody = "Avviso di scadenza"
MittenteMail = "TuoIndirizzomail"
DestinatarioMail = "TuoIndirizzomail"

    With iMsg
        Set .Configuration = iConf
        .From = MittenteMail
        .To = DestinatarioMail
        .CC = ""
        .BCC = ""
        OggettoMail = "PROVA"
        .From = MittenteMail
        .Subject = OggettoMail
        .TextBody = strbody
        .Send
    End With

End Sub