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
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 | email1@outlook.com |
| 2 | Project 2 | 10/1/2020 | Send Reminder | email2@outlook.com |
| 3 | Project 3 | 15/1/2020 | email3@gmail.com | |
| 4 | Project 4 | 16/1/2020 | email4@hotmail.com |
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 | Football@example.com | Football match | Let's Play football at 8:00 AM | 8/1/2020 |
| Basket Ball Team | basketball@example.com | Basketball Training | We need to do some training at 8:00 AM | 9/1/2020 |
| IT Department | IT@examlple.com | Project 1 | Your Request Status is UnderProcessing | 10/1/2020 |
| IT Department | IT@examlple.com | 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 | email1@outlook.com |
| 2 | Project 2 | 13/1/2020 | Send Reminder | email2@outlook.com |
| 3 | Project 3 | 15/1/2020 | email3@gmail.com | |
| 4 | Project 4 | 16/1/2020 | email4@hotmail.com |
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
