Send automatic Outlook email based on Due Date

[WesternPartner64]

Hi All,

I am new to this forum and to VBA so hoping someone can help!

I need to be able to send an Outlook email reminder automatically 30 days prior to a licence or certificate renewal due date.

I have attached the spreadsheet with the VBA code currently but this relies on Kutools and I was hoping to achieve the outcome without Kutools.

There are two due dates that trigger a reminder email columns F and H, F for a Licence renewal reminder and H for a Certificate reminder.

I would like the First Name to appear automatically next to 'Dear' in the content and would like 'Kind Regards Neil' to appear after the content of 'Your Licence is due for renewal.' or 'Your Certificate is due for renewal.' depending on which due date is met.

Thank you to anyone that provides assistance.

[humdingaling]

there are plenty of examples of this in this forum which dont use Kutools

https://www.excelforum.com/excel-pro...xcel-file.html

let me know if you have issues adapting the code to your specific needs

ps i suggest you add a column at the end to note whether or not you have already sent a reminder
no point sending the same reminder over and over and over again

[humdingaling]

i note your subject is going to be a little more difficult to code than the example attached
so i went and coded it out

[WesternPartner64]

Hi Hum,

Thank you for your speedy response and work on this project, unfortunately I couldn't get it to Run on my computer:

Run-time error '1004':
Method 'Rows' of object '_Global' failed.

When Debug pressed the For i = 4 To Cells... row is highlighted.

Thanks again in advance.

Cheers
Barry

[WesternPartner64]

Hi Hum,

I closed everything I had open and then reopened the download file and it worked perfectly

Will the Macro run everytime I open the spreadsheet?

Thanks again, great work!

[WesternPartner64]

Hi Hum,

I went to change the name at the bottom of the email and can no longer find the VBA code that you compiled, which I could see originally??

What am I missing?

[humdingaling]

are you sure you checking the right area? the code is THISWORKBOOK module rather than module 1

i also realised i made the whole code and didn't refer to the correct worksheet
so maybe force it by doing Sheet1.Activate after the initial IF statement

[WesternPartner64]

Thanks Hum,

Finally got back to this task, all good, great work, thanks.

[humdingaling]

not a problem
glad to hear it works

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[WesternPartner64]

Thread view.bmp

Sorry Hum, this is all I see under Thread Tools??