macros for auto email from excel

[AsifShabbir]

Hi All,

I am new to this forum and hopeful that I would be helped out. Actually I have two macros in one worksheet for generating emails before 42 days (normal mail) and 14 days (reminder) before due date. These are working well but I want some changes in it and I am unable to do it as I don't know much about writing macros. What I want is as under;

1. I want macros to be triggered when value/ date in column D is <= today + 42 rather than If Cells(i, 13).Value = 1.

2. I want an offset value in Column N which is date of sending email. it means column N should be auto populated with the date when mail generated / sent and when there is date in Column N excel should exit macro.

3. For the 2nd code (Reminder) I want to set the conditions that when there is some date in Column N it should not be triggered otherwise when it is triggered an offset value(date) in Column O and then exit macro.

4. I want to run both macros automatically when worksheet is opened and conditions are met. Now I have set it with buttons.

Here are the codes

Private Sub CommandButton1_Click()
Dim 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
    For i = 4 To Sheets("Sheet1").Range("D65536").End(xlUp).Row
        If Cells(i, 13).Value = 1 Then
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("Sheet1")
                strto = .Cells(i, 8).Value
                strcc = .Cells(i, 11).Value
                strbcc = ""
                strsub = "Review Date of " & .Cells(i, 1).Value
                strbody = "Hi there" & vbNewLine & vbNewLine & _
                    "Review Date of " & .Cells(i, 1).Value & " " & _
                    .Cells(i, 2).Value & _
                    " located at  " & vbNewLine & vbNewLine & .Cells(i, 19).Value & vbNewLine & vbNewLine & _
                    " is due on " & .Cells(i, 4).Value & "." & _
                    vbNewLine & vbNewLine & "Please take appropriate action to ensure that the document is reviewed" & _
                    " in time for approval before the due date" & _
                    vbNewLine & vbNewLine & "Best Regards," & _
                    vbNewLine & vbNewLine & "Asif Shabbir"
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                .Display
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
    Next i
End Sub

2nd code (Reminder)

Private Sub CommandButton2_Click()
Dim 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
    For i = 2 To Sheets("Sheet1").Range("P65536").End(xlUp).Row
        If Cells(i, 16).Value = 1 Then
            Set OutApp = CreateObject("Outlook.Application")
            OutApp.Session.Logon
            Set OutMail = OutApp.CreateItem(0)
        
            With Sheets("Sheet1")
                strto = .Cells(i, 8).Value
                strcc = .Cells(i, 11).Value
                strbcc = ""
                strsub = "Reminder for Review of " & .Cells(i, 1).Value
                strbody = "Hi there" & vbNewLine & vbNewLine & _
                    "This is to remind you again that Review Date of " & .Cells(i, 1).Value & " " & _
                    .Cells(i, 2).Value & _
                    " located at  " & vbNewLine & vbNewLine & .Cells(i, 19).Value & vbNewLine & vbNewLine & _
                    " is due on " & .Cells(i, 4).Value & "." & _
                    vbNewLine & vbNewLine & "Please take appropriate action to ensure that the document is reviewed" & _
                    " in time for approval before the due date." & _
                    vbNewLine & vbNewLine & "Best Regards," & _
                    vbNewLine & vbNewLine & "Asif Shabbir"
            End With
    
            With OutMail
                .To = strto
                .CC = strcc
                .BCC = strbcc
                .Subject = strsub
                .Body = strbody
                .Display
            End With
    
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
    Next i
End Sub