Auto Email Reminder from Excel when project is almost due

8255 Views 33 Replies 6 Participants Last post by SakuraiHiro, Oct 22, 2016

SakuraiHiro

Discussion Starter · Aug 31, 2016

Hi everyone,

I have no prior experience with Macro/VBA and would need help in creating an excel spreadsheet where an email reminder would be sent 60 days, 30 days and 7 days respectively if the project is not completed to the email stated in the 6th column with information from its row. If its completed, the checkbox would be checked and no mail will be sent.

Font Rectangle Parallel Pattern Number

The email should look like:

To: <email stated in that row>
Subject: Reminder to complete project within (e.g. 60 days)

Body:
Dear all,
Reminder to complete IN1661234 by 31st Aug 2016.

<personal email signature>

Do I need to create a button where I have to click in order for those reminders to be sent or will it be sent automatically everyday as long as the spreadsheet is opened?

Also, is it possible to create 3 different email template for each reminder days, such as 60 days prior to the deadline, the subject of the email would be First Reminder, 30days would have Second Reminder and 7 days prior would have FINAL REMINDER and a urgent marked on the email?

And how do I change the email where those reminders will be sent from?

Any help with regards to the above would be greatly appreciated. Thank you so much.

See less See more

Status: Not open for further replies.

1 - 20 of 34 Posts

reticentAJ

· #2 · Sep 7, 2016

Are you planning on leaving this computer on at all times? The reason I ask is because in order to do what you want, the computer would have to be on at all times in order for this to work. I'm not even sure if Excel allows for automatically doing something like that without input from the user. I can definitely see if it's possible, but if you don't want to/plan on keeping this computer on at all times, then the information wouldn't be helpful for you

.

In regards to changing where those emails come from, what are you looking for exactly? Do you want each email to come from an separate email address? I'm just trying to get an idea of what you're actually asking.

In my opinion, what you are trying to do shouldn't be done a computer. This is a project that should be moved to a server. This will allow you to remove the constraint of using Excel and opens endless possibilities of what you can do. It will also make it easier in my opinion. You might be able to accomplish this without having to pay anything. I know there are free web hosting servers out there, but most are limited in what they can do. You can get a decent server pretty cheap, depending on the specs you need. I personally have a server that I spend about $30 a year for and I have full access to do everything on the server. Worth it in my opinion. This is just a better alternative in my opinion. If you want to stick to the Excel route, that's fine, assuming it's possible. Let me know what you think.

See less See more

OBP

· #3 · Sep 7, 2016

There are many examples of Workbooks that do what you are asking for except possibly changing the email address.
Have you tried using the Search or the Advanced Search facility?

SakuraiHiro

Discussion Starter · #4 · Sep 18, 2016

reticentAJ said:
Are you planning on leaving this computer on at all times? The reason I ask is because in order to do what you want, the computer would have to be on at all times in order for this to work. I'm not even sure if Excel allows for automatically doing something like that without input from the user. I can definitely see if it's possible, but if you don't want to/plan on keeping this computer on at all times, then the information wouldn't be helpful for you .

In regards to changing where those emails come from, what are you looking for exactly? Do you want each email to come from an separate email address? I'm just trying to get an idea of what you're actually asking.

In my opinion, what you are trying to do shouldn't be done a computer. This is a project that should be moved to a server. This will allow you to remove the constraint of using Excel and opens endless possibilities of what you can do. It will also make it easier in my opinion. You might be able to accomplish this without having to pay anything. I know there are free web hosting servers out there, but most are limited in what they can do. You can get a decent server pretty cheap, depending on the specs you need. I personally have a server that I spend about $30 a year for and I have full access to do everything on the server. Worth it in my opinion. This is just a better alternative in my opinion. If you want to stick to the Excel route, that's fine, assuming it's possible. Let me know what you think.

Hi, thank you so much for your reply and apologies for the late response.

No, I don`t plan to leave the computer on at all times.
The excel are usually accessed by a small group of staff, though currently, I am the one doing the reminders manually. As I`m currently an intern, I would be leaving the company in a few months time, and by then someone else would be doing the reminders. So for the reason I want to change the sender would be so that I can pass on the work back to the company smoothly.

As for hosting a server, I did consulted my supervisor, but it is not within my authority to hand those data over to a server. It would be best if I stick to the excel method.

SakuraiHiro

Discussion Starter · #5 · Sep 18, 2016

OBP said:
There are many examples of Workbooks that do what you are asking for except possibly changing the email address.
Have you tried using the Search or the Advanced Search facility?

Hi!
I did tried using the search function, as well as searching for other sources for help. However, having no prior experience, I have a hard time figuring out how to alter the code to fit it to my template.

DataBase

· #6 · Sep 19, 2016

You can have Excel run a timer so that it can periodically run a process, iv done this before.

But the preferred way to do this would be to run once when the workbook is open, and if thereafter you want to run it again (while the workbook is already open) you can have a button to run the process again.

This is possible however you will need to attach your workbook (remove sensitive data) so that I don't have to recreate it on my side, formatting and layout will cause the macro to fail so its always best to code using the original workbook

SakuraiHiro

Discussion Starter · #7 · Sep 19, 2016

DataBase said:
You can have Excel run a timer so that it can periodically run a process, iv done this before.

But the preferred way to do this would be to run once when the workbook is open, and if thereafter you want to run it again (while the workbook is already open) you can have a button to run the process again.

This is possible however you will need to attach your workbook (remove sensitive data) so that I don't have to recreate it on my side, formatting and layout will cause the macro to fail so its always best to code using the original workbook

Thank you so much for your help!
Have attached a template of the excel. Just wondering, if for another person open the excel in place of me, would the emails be sent from his outlook? Or how do I change who the sender is? (As i`m an intern and have to pass the work back to my colleagues)

DataBase

· #8 · Sep 19, 2016

Whoever is logged into the machine their profile will send the email. I will work on this tomorrow hopefully and attach a working workbook.

Rollin_Again

· #9 · Sep 19, 2016

My preferred method of automating a task like this is to use a small VBS script that will open the workbook hidden in the background and call the macro. Using this method will allow you to use Windows built in Task Scheduler and schedule it to run on its own at whatever predesignated times you choose. I also prefer to use the CDO or MAPI mail method instead of using the Outlook Object Library method.

DataBase

· #10 · Sep 21, 2016

I have a question. In the workbook your notes say "When the day is 60 days and 30 days before the due date"

is this correct or was this meant to be 60 or 30 days AFTER the due date?

from the workbook you sent me

Lets take "Battery Pack" as an example

the due date for this is 18/09/2016.
are we sending the 30 day email on the 19/08/2016? (-30)
or
18/10/2016 (+30)?

need clarification on this so I can complete this properly

thanks,

See less See more

SakuraiHiro

Discussion Starter · #11 · Sep 21, 2016

DataBase said:
I have a question. In the workbook your notes say "When the day is 60 days and 30 days before the due date"

is this correct or was this meant to be 60 or 30 days AFTER the due date?

from the workbook you sent me

Lets take "Battery Pack" as an example

the due date for this is 18/09/2016.
are we sending the 30 day email on the 19/08/2016? (-30)
or
18/10/2016 (+30)?

need clarification on this so I can complete this properly

thanks,

Yes, it is indeed 30/60 days before due date (1/2month before) so as to ensure that they will be able to complete the task on time

DataBase

· #12 · Sep 22, 2016

Yeah sorry I didn't read your first post properly. I have it working I just need to make a few tweaks but have a busy week at work so will hopefully send it to you tomorrow.

DataBase

· #13 · Sep 23, 2016

Please try the attached, you will need to enter some live dates and test weather it works as expected or not. I am not able to test thoroughly as I am short of time this week.

Hope this helps.

SakuraiHiro

Discussion Starter · #14 · Sep 24, 2016

DataBase said:
Please try the attached, you will need to enter some live dates and test weather it works as expected or not. I am not able to test thoroughly as I am short of time this week.

Hope this helps.

Thank you so much for your help! Will input some live data next week when I have it!
Just want to clarify, will the mails be sent automatically or will there be a popup before sending it? And it will only pop up if the day is 30/60 days before the due date?

SakuraiHiro

Discussion Starter · #15 · Sep 26, 2016 (Edited)

DataBase said:
Please try the attached, you will need to enter some live dates and test weather it works as expected or not. I am not able to test thoroughly as I am short of time this week.

Hope this helps.

Hi!
I have tested for this 2 days, and realized that even after I send the emails or switching the status to closed, the email will still pop up whenever I reopen it.
Is it possible for it to pop up on the 30 and 60days prior? (However this would cause the concern of what if the day falls on a weekend and no one is there to open the excel sheet)

E.g.: Date started is X, due date would be 2 years later, Y
first reminder will be on Y- 60, final reminder would be Y-30.
However, if on either of that day, the email is not sent, it will be sent on the next time it is opened. And once its sent, it will not be sent again until the next reminder (final reminder).

If the account is closed before one/both of the reminder date, then email will not be sent.

(It will be great if the mails could be sent without having the computer on/ manually opening the excel itself)

SakuraiHiro

Discussion Starter · #16 · Oct 1, 2016

SakuraiHiro said:
Hi!
I have tested for this 2 days, and realized that even after I send the emails or switching the status to closed, the email will still pop up whenever I reopen it.
Is it possible for it to pop up on the 30 and 60days prior? (However this would cause the concern of what if the day falls on a weekend and no one is there to open the excel sheet)

E.g.: Date started is X, due date would be 2 years later, Y
first reminder will be on Y- 60, final reminder would be Y-30.
However, if on either of that day, the email is not sent, it will be sent on the next time it is opened. And once its sent, it will not be sent again until the next reminder (final reminder).

If the account is closed before one/both of the reminder date, then email will not be sent.

(It will be great if the mails could be sent without having the computer on/ manually opening the excel itself)

'

Edit: I changed the formula such that date = today + 60, + 30 and +7 for each respective reminders. I will try using windows task scheduler to have it opened automatically daily. However the problem of sending emails to already closed consignment still persist and im unsure how to change it.
I also notice the .display and .send comment you wrote below but have no idea how to implement it. currently it seems to be displaying the email instead of sending automatically. How do i switch it to .send?

DataBase

· #17 · Oct 3, 2016

Hi,

I have some time today so will look into these for you

SakuraiHiro

Discussion Starter · #18 · Oct 3, 2016 (Edited)

DataBase said:
Hi,

I have some time today so will look into these for you

Thanks! I know how to change it to having it sent automatically though there will always be a popup which I have attached.

To add on, how do I replicate the macro onto other sheet in the same workbook, same template? I tried copying the same set of code to the sheet2 and ThisWorkbook VBA but it seems to only trigger the sheet that is opened/viewed currently.

See less See more

SakuraiHiro

Discussion Starter · #19 · Oct 5, 2016 (Edited)

Have uploaded my current file for reference.

Is it also possible to make the due date in the emails red and bold? I tried using but doesn`t seems to work for me

Edit: made further changes, only problem left is changing font of deadline in email and email not being send if status is closed. (Suggestion if too complicated: if email section is empty/blank, email will not be generated)

Edit 2: I changed If Bcell<> Empty Then to If Bcell.Offset(0, 5) <> Empty Then such that when email column is empty, mail will not be sent and it worked! Now the remaining thing to work on would be the date font in the email!

Really appreciate your help!

DataBase

· #20 · Oct 18, 2016

Hey very sorry for not replying, iv been away for a while so haven't been able to get on. How are you getting on, did you mange to figure it all out?

1 - 20 of 34 Posts

Status: Not open for further replies.