LadyDisdain
New Member
- Joined
- Apr 9, 2017
- Messages
- 7
I am pretty new to VBA and have been tasked with creating a rather complex email reminder system.
We have a list of equipment which have unique IDs. Each row has various details relating to that equipment ID such as calibration instructions, dates calibrations are due, last calibrated date, etc.
I have written a code that looks at a column called Email Reminder Date and sends an email with details for each piece of equipment who's email reminder date equals today, or is less than today. Once the email is sent, a new sheet (Email Log) is populated with the Equipment ID, the date and time that the email was sent (Timestamp of reminder email), and the date that was in the Email Reminder Date (CalRem) column at the point of sending the email (Reminder date at point of last email)
I only want this email to send if there hasn't already been an email sent for this piece of equipment and reminder date, so I need to lookup the Equipment ID in the Email Log sheet, find the most recent date in the Timestamp of reminder email column, and then compare the corresponding Reminder date at point of last email with the value currently in the Email Reminder Date (CalRem) column. If there is no existing log for that equipment ID, or the reminder date is newer than the last one logged, then an email reminder is sent.
The equipment IDs in the email log cannot be sorted alphabetically, they need to remain in the order they are logged. I can't take the actual workbook home with me, so I'm working on paper printouts - trying to keep typing to a minimum so won't include the full code but I can expand if needed.
Current rule for identifying Equipment IDs which I need to expand upon (there may be multiple rows that match the criteria):
(CNBH refers to a function which enables me to refer to things by column names)
When all the emails are sent, the Email Log sheet is populated:
I am able to return the max value using the following:
Problem one: I need "ABC001" to be the equipment ID (EquipID), but can't get this to work.
Problem two: I can lookup "ABC001", but it returns the first match it finds. I need to combine it with the max code.
Any help would be much appreciated, I've been going round and round in circles trying different codes I've found online.
We have a list of equipment which have unique IDs. Each row has various details relating to that equipment ID such as calibration instructions, dates calibrations are due, last calibrated date, etc.
I have written a code that looks at a column called Email Reminder Date and sends an email with details for each piece of equipment who's email reminder date equals today, or is less than today. Once the email is sent, a new sheet (Email Log) is populated with the Equipment ID, the date and time that the email was sent (Timestamp of reminder email), and the date that was in the Email Reminder Date (CalRem) column at the point of sending the email (Reminder date at point of last email)
I only want this email to send if there hasn't already been an email sent for this piece of equipment and reminder date, so I need to lookup the Equipment ID in the Email Log sheet, find the most recent date in the Timestamp of reminder email column, and then compare the corresponding Reminder date at point of last email with the value currently in the Email Reminder Date (CalRem) column. If there is no existing log for that equipment ID, or the reminder date is newer than the last one logged, then an email reminder is sent.
The equipment IDs in the email log cannot be sorted alphabetically, they need to remain in the order they are logged. I can't take the actual workbook home with me, so I'm working on paper printouts - trying to keep typing to a minimum so won't include the full code but I can expand if needed.
Current rule for identifying Equipment IDs which I need to expand upon (there may be multiple rows that match the criteria):
Code:
1LastRow = Cells(Rows.Count, 1).End(x1Up).Row
For 1Row 2 To lLastRow
If Cells(1Row, (CNBH("Calibration Reminder Date"))) = Date And Cells(1Row, (CNBH("Available"))) = True Then
'Send email with details such as due date, calibration instructions, etc. Example:
EquipID = Cells(1Row, (CNBH("Equipment ID")))
CalRem = Cells(1Row, (CNBH("Calibration Reminder Date")))
When all the emails are sent, the Email Log sheet is populated:
Code:
'1 = Equipment ID
'2 = Timestamp of reminder email
'3 = Reminder date at point of last email
LastLog = Sheets("Email Log").ListOnjects("EmailLog").ListRows.Count + 1
Sheets("Email Log").Cells(LastLog + 1, 1) = EquipID
Sheets("Email Log").Cells(LastLog + 1, 2) = Now()
Sheets("Email Log").Cells(LastLog + 1, 3) = CalRem
I am able to return the max value using the following:
Code:
myVar = Evaluate("MAX(IF(A:A=""ABC001"",B:B))")
Problem one: I need "ABC001" to be the equipment ID (EquipID), but can't get this to work.
Problem two: I can lookup "ABC001", but it returns the first match it finds. I need to combine it with the max code.
Any help would be much appreciated, I've been going round and round in circles trying different codes I've found online.