Embed "Replace" function into an existing macro

[Mbbx9dgm]

Hello

I am trying to design a simple excel database that tracks audit findings and actions and includes automated email updates on 28 days and 7 days periods. I am a VBA novice (ish) and have so far been able to use various forums to paste and amend the following macro, which works perfectly for the task (continue to bottom to see what the issue is):

Sub Notify()
    Dim WS As Worksheet, Rng As Range, c As Range
    Dim OutApp As Object, OutMail As Object
    Dim Msg As String, Addr As String, FName As String, i As Long
    Dim NotificationNecessary As Boolean
 
    Set OutApp = CreateObject("Outlook.Application")
    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set Rng = WS.Range("F6", WS.Range("F" & Rows.Count).End(xlUp))
    For Each c In Rng
        NotificationNecessary = False
        FName = Left(c, InStr(1, c, " ") - 1)
        Msg = "Dear " & FName & Chr(13) & Chr(13)
        Msg = Msg & "The following action from the " & c.Offset(, -3) & " audit is due for completion by " & c.Offset(, 3) & ":" & Chr(13)
        Msg = Msg & "   - Audit Finding: " & c.Offset(, 6) & Chr(13)
        Msg = Msg & "   - Agreed Action: " & c.Offset(, 8) & Chr(13)
        Msg = Msg & "   - Auditor: " & c.Offset(, -5) & Chr(13)
 
        For i = 11 To 11
            If WS.Cells(c.Row, i) = "28 Days" Then
                NotificationNecessary = True
        End If
    Next
        If NotificationNecessary Then
            Msg = Msg & Chr(13) & "Please review the above action for completeness and provide an update to the Auditor on or before the due date." & Chr(13)
            Msg = Msg & Chr(13) & "Regards" & Chr(13)
            Msg = Msg & Chr(13) & "Internal Audit"
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
            .To = c.Offset(, 1)
            .CC = ""
            .BCC = ""
            .Subject = "Reminder: Audit action due for completion within 28 days"
            .Body = Msg
            .Display
            End With
            Set OutMail = Nothing
        End If
    Next
End Sub

As you can see, at "28 days" the macro picks up and creates an auto email ready to send as a reminder. What I need to do now (after the email is created) is replace the "28 Days" within the worksheet for something like "28 Days*" to identify that a reminder has already been sent and stop the macro from creating another reminder email in future.

I wait in anticipation for all you VBA geniuses to help find a solution

P.S. the offset for the "28 Days" identifier would be +5 (columns).

 

[Dryver14]

Sub Notify()
    Dim WS As Worksheet, Rng As Range, c As Range
    Dim OutApp As Object, OutMail As Object
    Dim Msg As String, Addr As String, FName As String, i As Long
    Dim NotificationNecessary As Boolean
 
    Set OutApp = CreateObject("Outlook.Application")
    Set WS = ThisWorkbook.Sheets("Sheet1")
    Set Rng = WS.Range("F6", WS.Range("F" & Rows.Count).End(xlUp))
    For Each c In Rng
        NotificationNecessary = False
        FName = Left(c, InStr(1, c, " ") - 1)
        Msg = "Dear " & FName & Chr(13) & Chr(13)
        Msg = Msg & "The following action from the " & c.Offset(, -3) & " audit is due for completion by " & c.Offset(, 3) & ":" & Chr(13)
        Msg = Msg & "   - Audit Finding: " & c.Offset(, 6) & Chr(13)
        Msg = Msg & "   - Agreed Action: " & c.Offset(, 8) & Chr(13)
        Msg = Msg & "   - Auditor: " & c.Offset(, -5) & Chr(13)
 
        For i = 11 To 11
            If WS.Cells(c.Row, i) = "28 Days" Then
            Msg = Msg & Chr(13) & "Please review the above action for completeness and provide an update to the Auditor on or before the due date." & Chr(13)
            Msg = Msg & Chr(13) & "Regards" & Chr(13)
            Msg = Msg & Chr(13) & "Internal Audit"
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
            .To = c.Offset(, 1)
            .CC = ""
            .BCC = ""
            .Subject = "Reminder: Audit action due for completion within 28 days"
            .Body = Msg
            .Display
            End With
            Set OutMail = Nothing
            Cells(c.Row, i) = ("28 Days*")
        End If
    Next
End Sub

I,ve removed the notification = true part as I dont think it was required and also notice i = 11 to 11, if you are only looking at 11 then you could say i = 11 or drop the variable altogether and say (c.Row, 11)

 

[Mbbx9dgm]

Thanks for the quick response. I have tried what you suggested and it works exactly as I asked for but then have realised I've been a bit of a donut and not been specific enough

The "28 Days" is generated as part of a formula (example below of first row in the range):

=IF(AND(I6<>0,Q6="Open"),IF($J6<=28,"28 Days",IF($J6<=7,"7 Days",IF($J6<0,"Overdue",""))),"NA")

It is the "28 days" within the formula that I need to replace with "28 Days*", rather than replacing the whole forumla with "28 Days*" as it still needs to pick up when days elapsed gets to less than 7.

 

[Dryver14]

Im guessing J6 is a formula too based on dates so you could try something like the following

change the IF to

If WS.Cells(c.Row, i) = "28 Days" And WS.Cells(c.Row, 12) = "" Then

12 or "column L" being the next empty colomn

and then the last line as follows

Cells(c.Row, 12) = ("Message Sent")

use whatever column you wanted to input the "message sent" statement