Trying to Get Email Sent With Correct Values

[dalgic]

I have an excel sheet attached below,

What I am trying to do is to make excel send an automated email to the address stated in "G1".

Email will be sent according to the "Closing Date", this date is calculated by stating how many days allocated starting from the "Date Opened" for that case under "Days Allocated".

The email should include all the details from CASE ID, CHANNEL ID etc.

I no how to send emails for one row but I dont know if it is possible to do it for any row which expired.

The functions are there that check the expiry and email when the excel document is opened and check buttion, but I could not modify it properly as I took this code from another excel document on this forum.

Can anybody help?

Thanks

[starryknight64]

I'm not quite sure I was able to fully answer your question, but here's my best shot (so far):

Please Login or Register  to view this content.

I hope this helps!

[Leith Ross]

Hello dalgic,

I removed the "Email Sent" button. The macro will now add the comment automatically once the email is sent. The other 3 buttons now use the same macro, Macro1 in Module1. There wasn't a need to have separate macros for the buttons. The macro "Check Dates" will send out an email if the closing date is today or after today and "Case ID" cell doesn't have a comment. Here are the macros which have already been added to the attached workbook.

Macro for Buttons to Add Comments

Please Login or Register  to view this content.

Macro to Check for Expired Dates

Please Login or Register  to view this content.

[dalgic]

Hello Leith, thank you soo much for that, I have modified the document a bit with some drop downs and validations but theres a few problems.

1. When it emails the cases, cases details are added into each other in after the first email. It should be each row in a new email.

2. When the excel sheet is opened it doesnt check and send an email immediately. you have to press the check dates button?

3. I have added a calendar popup addin, if you check the zip I have also included the addin.

4. Also I have drop downs for Case Status, I dont know if it is possible but I am trying to make the row to move to the sheet "Closed" when the status is picked as one of the closed status.

Thanks for all your helps.

[Leith Ross]

Hello dalgic,

The first 2 problems, unfortunately, are my fault. Easy enough to fix, though. The request to move the email, once sent, to a new sheet is also doable. I can make the changes for you in about an hour and a half. First, I have to take my wife to work.

[dalgic]

come on man, you can take your time . you have done more than i expected, very much appreciated. thanks

[Leith Ross]

Hello dalgic,

I made all the changes with the exception of the pop up calendar. I forgot to ask you what you wanted to do with this.

[dalgic]

Hello Leith,

thanks for the reply again, what do you mean for the calendar? because the calendar is already implemented there? what options do we have with the calendar?

Also my second question is, is it not possible to remove the row when it is copied to the sheet "closed" and if its open it will move back to the sheet "Case Chase". Like this theres gonna be a lot of duplicates dont you think so?

I managed to use the cut function instead of the copy, it moves it to the closed sheet but could not get it to work the other way around.

Thanks so much again.

I have a few other implementations which I am trying to do, I dont know if you can help me with those aswell. I feel like im asking too much . sorry