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

    NO.

    Project Description

    Due Date l

    Remarks

    Email Address for the people who are responsible

    1

    Project 1

    10/1/2020

    Send Reminder

    [email protected]

    2

    Project 2

    10/1/2020


    Send Reminder

    [email protected]

    3

    Project 3

    15/1/2020

    [email protected]

    4

    Project 4

    16/1/2020

    [email protected]

    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 )

    sender Name

    Sender Email

    Massage Subject

    Massage Body

    Date

    Football Team

    [email protected]

    Football match

    Let's Play football at 8:00 AM

    8/1/2020

    Basket Ball Team

    [email protected]

    Basketball Training

    We need to do some training at 8:00 AM

    9/1/2020

    IT Department

    [email protected]

    Project 1

    Your Request Status is UnderProcessing

    10/1/2020

    IT Department

    [email protected]

    Project 2

    Your Request Status is Under Technical Check

    10/1/2020

    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

    Status Name

    Number of days should be added according to the status name

    UnderProcessing

    2+day on the due date

    Technical Check

    3+day on the due date

    Under Final Confirmation

    1+ day on the due date

    The new table After due date modification

    For Example

    NO.

    Project Description

    Due Date l

    Remarks

    Email Address for the people who are responsible

    1

    Project 1

    12/1/2020

    Send Reminder

    [email protected]

    2

    Project 2

    13/1/2020


    Send Reminder

    [email protected]

    3

    Project 3

    15/1/2020

    [email protected]

    4

    Project 4

    16/1/2020

    [email protected]

    I would really appreciate if you can help me

  • 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.