Automatic Outlook mail due date reminder based on Excel file

i've put a check cell in column F so you do not repeat reminders

Private Sub Workbook_Open()
    
    Dim i As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    
    For i = 2 To Range("C65536").End(xlUp).Row
        If Cells(i, 6) <> "Y" Then
            If Cells(i, 3) - 7 < Date Then
                
                strto = Cells(i, 4).Value 'email address
                strsub = "Project " & Cells(i, 2).Value & " is due on Due date " & Cells(i, 3).Value 'email subject
                strbody = "Dear " & Cells(i, 1).Value & vbNewLine & "please update your project status" 'email body
                
                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    '.Send
                    .display
                                    
                End With
                
                Cells(i, 5) = "Mail Sent " & Now()
                Cells(i, 6) = "Y"
                
            End If
        End If
    Next
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    '.Send
                    .display
                                    
                End With

i put a ' infront of send so it doesn't send, remove it and it will send (it should be in green in the code)

likewise for display
if you dont want to look at message as well put a ' in front and the email message will not show

ps the code has been made so

If Cells(i, 3) - 7 < Date Then

meaning as long as the date in cell minus 7 is less than today it will trigger
which is why i put in the check cell in column F

try this code

Private Sub Workbook_Open()
    
    Dim i As Long
    Dim OutApp, OutMail As Object
    Dim strto, strcc, strbcc, strsub, strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
   
    
    
    For i = 2 To Range("C65536").End(xlUp).Row
        If Cells(i, 6) <> "Y" Then
            If Cells(i, 3) - 7 < Date Then
                Set OutMail = OutApp.CreateItem(0)
                strto = Cells(i, 4).Value 'email address
                strsub = "Project " & Cells(i, 2).Value & " is due on Due date " & Cells(i, 3).Value 'email subject
                strbody = "Dear " & Cells(i, 1).Value & vbNewLine & "please update your project status" 'email body
                
                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    .Send
                    '.display
                                    
                End With
                On Error Resume Next
                Cells(i, 5) = "Mail Sent " & Now()
                Cells(i, 6) = "Y"
                
            End If
        End If
    Next
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

hi,

Everything is working fine. But i have three concerns regarding this;

1) I want to bold the cell value in the email body.
2) the code is not running automatically when i open the excel file.
3) if there are more than one entries with due dates less than 7 days then only the last one will be sent and the rest will be skipped.

Expecting your kind support

not a problem

please mark as solved if your question has been answered

Cheers
hum

you really should have started a new thread, i didnt even know i was still subscribed to this thread

This line controls who you send to

 strto = Cells(i, 4).Value 'email address

all you need to do is modify it with ";" in between each email address

if you need help beyond this
start a new thread posting your specific question with example sheet if possible

isurunk1,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

ABHIJITNAZIR did you see post #13?
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

pmc22222, welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

AmalJS,
Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread