Automatic email from Excel

I have an excel sheet which contains user details and date of expiry of a particular service. I would like to send an email to user when the date of expiry is met. Can you please help me with this. I have searched quite a few solutions but nothing has helped me. I have very little knowledge in VB.

Any help is much appreciated.

Thanks.

Hello hasini,

Welcome to the Forum!

Because you aren't very familiar with VBA, it would be better if you post your workbook. The needed code can then be added directly into your workbook.

Hello hasini,

The attached workbook has the macros shown here already installed. The macro to check for expiry dates runs when the workbook is first opened. It can also be run at anytime by clicking the button on "Sheet1".

The emails are sent using CDO (Collaboration Data Objects) instead of using Outlook. This will prevent the security message from appearing each time an email is sent. You will need to add some information to the macro before it will send the email. These areas are marked in red. I am assuming your are connecting to your email server remotely. If not, let me know.

Macro Code to Send Emails

Please Login or Register  to view this content.

Macro to Check Expiry Dates
Change the subject line and message to what you want sent out. Also, if you change the worksheet name or the layout of your table be sure to make the change in the macro. See areas marked in red.

Please Login or Register  to view this content.

Workbook Open Event
This runs the macro when the workbook first opens.

Please Login or Register  to view this content.

Hello hasini,

Nothing happens, not even an error message? I will need to look into setting this up for sending email on the Exchange server. While CDO does prevent the annoying security messages, it isn't as easy to use as Outlook.

Hello hasini,

I could rewrite the macro to use Outlook, if you don't mind the answering security messages. If you only have a few emails it isn't so bad, but if you have a large list with the possibility of large number of emails then we need another approach.

Hello hasini,

Sorry for the delay. I had took take my wife to her doctor's appointment, and then to work. he attached workbook contains the Outlook macro.

Hello hasini,

I have added a column to the worksheet o hold the date-time stamp for the email being sent. This will prevent the email from being sent more than once. The atached workbook has this macro and the worksheet change alreay installed.

Please Login or Register  to view this content.

Hello hasini,

Can you show me what the mail body text should look like with the expiry date added?

Hello hasini,

Here is the macro with the updated mail body containing the expiry date. It has been added to the attached workbook.

Please Login or Register  to view this content.

Hello hasini,,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Hi,

Mr. Leith Ross, I have added some features to your codes (Check the red colored lines), in order to reach need which is checking Expiry Dates in a range of ten columns not only one:

1- Allow one person only to send the e-mail.
2- If we are sending e-mails to employees on the same exchange server, we don't have to use the full e-mail address (Login ID is enough).
3- Add a comment to the cell contains the date with Sending Details.

You can also Add CC and BCC copies to the code (See The blue colored lines).

Please Login or Register  to view this content.

Thanks Sarails.

pgor,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.