VBA Email reminder when Due date expired based on different criterias

[GAMU]

Dear All

I have create a simple userform that generates an Outlook-email based on combobox1 (related to sheet "Param" column A+B).

Basically what i need to do is send every 3 days a reminder to the person selected in combobox1 (which is : issue assigned to, related to email address īn sheet "Param" column B) Untill column "i" date-closed is filled in with a date by the issue initiator.

This is to obbligate the person that the issue is assigned to find a solution and communicate this with the issue initiator to close the issue-date, when this is done the reminders should stop sending emails.

I don't know if this thread is a tough one, but for me it is. Thanks a lot for your time

Download:
GMU Example.zip

Cheers
Gabriel

[Bilbo_Baggins]

How would you want this process to be initiated?

Is there a user form?

You could add a button for a click event or it could be tied to an event.

The basic code would be fairly easy, look to see if there is a value in the cell and if not, fire the email.
The question is, how do you want to initiate that process?

[GAMU]

Yes there's a userform indeed (I tried to shrink the file because the original is more complex).

The process should go like: when you open the file a popup msg ask you and tells you about the open issues (issue date expired) and if you like to send a reminder to the person listed in sheet "Param"column A, by clicking Yes vba collect the row data (sheet1) of the issues expired and submit the email again.

Also, wee need to consider that in sheet1 other issues might still be open under the same name, till the initiator put the Date-closed in column "i" then the popoup msg stops to appear only when the date-closed is provided.

Thank you Bilbo_Baggins, i really appreciate your help.

Cheers
Gabriel

[hectop]

The code below will run through the issues list (Sheet1) and if the issue is not closed, will send an e-mail every 3 days
You can call the SendEMail function from the code that pastes the data into the issues list. There's a little validation in the code that you may not need if the combo box always is in sync with the Parm sheet and no one can enter an issue without using the form.

Please Login or Register  to view this content.

[GAMU]

Thank you hectop!
Would you please be kind to check if this is right?

Please Login or Register  to view this content.

[hectop]

It's hard to discuss this via posts. I'm making a few assumptions.
The code you have in your Cbn_Send_Click() sub sends an e-mail if you click on submit, and saves the form data to the issues list if you click on Save & Close.

You indicated that you wanted to send an e-mail every 3 days so the code in EMailChk reads through your issues list and would send an e-mail every 3 days. Assumption is you don't want to do this manually. You'd have another button on Sheet1 to kick this off.

The SendEMail function could be used to send the e-mail in either case so you don't have duplicate code (albeit it will be slightly more complicated. Give a bit more time and I'll post the updated code.

[hectop]

If you want to reuse code in SendEMail then you can change the contents of Cbn_Send_Click() to the following.

Please Login or Register  to view this content.

The contents of SendEMail would then changed to the following. It only builds the message if it wasn't passed

Please Login or Register  to view this content.

If you want to tell the person how many reminders they've received, uncomment the line above

Please Login or Register  to view this content.

This would only contain a value if the e-mail was created from EMailChk.

(Hope I haven't confused you too much with all this.)

[GAMU]

Hi hectop
I think i need some more help, if you have the time of course, i really appreciate all the efforts you are making to helping me.
The zipped file contain the original form and customized ribbon with an additional tab: air-export, if you click on write new issue button this will open the userform. Would you please integrate your code into the script and make the changes to submit button ?
If you try to save the data from the userform it will probably give you an error, this is because the script save the data into the sheet but also in an external located worksheet, but it's a problem, just to let you know.
GMU_ v1.2.zip
Thank you so much!
Cheers
Gabriel

[hectop]

GMU_Ex3.zip
Gabriel,
This file has a Send Reminder button associated to a macro which loops through the issue log and each 3 days will send a reminder e-mail to the Issue Owner. It should demonstrate the functionality.

I didn't want to mess with your last attached sheet. I've never played with Ribbon Bars like that (although I'll have to investigate them now) and I'm worried that modifying your Ribbon Bar may introduce other errors.
What I would suggest is that you add a Step4 to the Ribbon Bar that would be the equivalent of the Send Reminder button.

You would just add the

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to new module in your spreadsheet. I'm saying a new module since it seems to conform to your coding standard.

Hope this helps.

Taras

[GAMU]

Good morning Taras,
Thank you for that. When i click on submit (form) i got an error on this line in userform view code:

Please Login or Register  to view this content.

Also, if i click Send Remidner teh code generate only the email for the first person in the list of row 5, but if i need to send the reminder to the person of row 7 how do i do that ?
Thank yu, have a good one!
Cheers
Gabriel

[hectop]

Please use the code in GMU_Ex3.zip that I posted.
A reminder would be sent if the following conditions are met:

1. Close date is blank
2. Number of days between the issue date and current date are evenly divisable by 3
3. The Owner on Sheet1 exists in column A of the Param sheet

If you're still having a problem, send the spreadsheet that you've put the code into directly to me. Check your private messages for my direct e-mail.