Creating an Automatic Tracking System Using Excel VBA

Dear All

I wanted to ask your assistance to create a VBA code that has the capability to make An Automatic Tracking System Using Excel VBA. Since I am still new at the VBA Code I have managed with a help of my friend to create a system which checking if there is a "Send Reminder" Massage ( As per the below image ) it will directly send an email reminder to a certain user from the listed email if the due date met with today date. What I need to do is to make the due change according to a certain value from a specific sender and according to this value, the due date will change either 1, 2 or 3 days according to the type of the value. and after the whole loop will start again to send a reminder email to a certain mail from the list.

For Example

The VBA code will check for all the excel files for the "Send Reminder" message from the remark column and send the email for the email address column, but before that due date will be changing according to specific words from a specific send from the outlook. which means that required the outlook to send the message from the outlook automatically to the Excel sheet ( separate sheet ) and then our code will start to search from specific sender Ex."IT Department", incase if the code found in the massage that the request is Under process, it will add 2 days on the due date, if the word is Under technical checking, it will add 3 days on the original due date. otherwise, the due date will not be changing

For Example ( emails retrieved from outlook automatically )

The Only massage here which required that the VBA Code need to check the email body is the massage which comes from the IT department and then it will go to email subject to and then to the body massage to check only the words ( Status) and according to the status the due date will be changing according to the number of days as per the below list

The new table After due date modification

For Example

My VBA Code which only sends email reminder is 


Sub SendReminderMail()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim iCounter2 As Integer
Dim MailDest As String
Dim Subj As String

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)

With OutLookMailItem
MailDest = ""
Subj = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
MailDest = Cells(iCounter, 4).Value
ElseIf MailDest <> "" And Cells(iCounter, 4).Offset(0, -1) = "Send Reminder" Then
MailDest = MailDest & ";" & Cells(iCounter, 4).Value
End If
Next iCounter
For iCounter2 = 1 To WorksheetFunction.CountA(Columns(4))
If Subj = "" And Cells(iCounter2, 4).Offset(0, -1) = "Send Reminder" Then
Subj = Cells(iCounter2, 1).Value
End If
Next iCounter2
.BCC = MailDest
.Subject = Subj
.Body = "Reminder: Your next credit card payment is due. Please ignore if already paid." & Subj
.Send
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub

I would really appreciate if you can help me

kindly see the attached work book

sir

the thing is the code above has the ability to send email to people whom their emails are listed in case if there was send reminder massage . The problem is how we can change the due date automatically in case if there was a specific specific mentioned in the email Ex.UnderProcessing . and therefore the due date will change according to status which is mentioned . What I wanted to build is a tool which can check the email for the spesific status of a project and then change the due date in the Excel sheet.