Email to e sent if the expiry date is after 15 days

[azmatullahkhan]

HI, I want to make an excel file where i have list of suppliers contract which is as follows
s.No Name of Supplier Type of service Anual Value Starting Date Expiry Date

I need help if the expiry date is after 15 days the system should sent automatic email to my Lotus Notes is it possible i am not familier at all to VB

[Simon Lloyd]

Something like this

Please Login or Register  to view this content.

You get the idea anyway

Regards,
Simon Lloyd
For more help on Microsoft Office Applications Click Here

[azmatullahkhan]

not at all as i do not know as to what is this

[azmatullahkhan]

what if i attach my file and someone can fix this for me please

[azmatullahkhan]

Dear Simon may i get some help on this please

[Simon Lloyd]

The code supplied, when run should check the dates and email you the address and expiry date of the row/entry that is 15 days old or more

[azmatullahkhan]

shal i attach the file and you fix this programme in the file and resend to me please

[azmatullahkhan]

Hi Simon is it ok if i attach the file and you fix it please

[Simon Lloyd]

How to add and run a Macro[help]1. Copy the macro above .
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[azmatullahkhan]

This is where i have an issue the point no 7 which you wrote that i am not able to do that was the reason i could not do it, i have attached the file for your riveiw if you can do those changes by inserting this Macro to my file and return please. i have attached the file please have a look there are two sheets in data base i would like you to insert the Macro.

[Simon Lloyd]

Make custom changes means change the sheet and coulmn names to suit your need, if i do it all for you it wont help you lear or understand how it works and you'll be unable to maintain it.

[azmatullahkhan]

I tried a lot but could not if you change and help me , i am not able to do it

[Simon Lloyd]

I've just checked your example, my code would be extremely easy to change for your needs!, you need to add the code as i've shown and then step through it using F8, but to be honest you only need to change the column letter...etc!

[azmatullahkhan]

Dear Simon, I tried but still same it will help me if you do and let me know the details.Please.

[Simon Lloyd]

Provide the code where you have tried it, it really is very very simple, to be honest if you couldn't manage it then i suggest you don't use any vba until you've learnt the basics as you'll never be able to maintain it or repair it!

[azmatullahkhan]

Dear Simon, HI, I need your help please i need this file very badly please help me if you can, please fix it for me regarding learning i am sure that i can learn encyclopedia from you.please help to do this file.

[Simon Lloyd]

Provide the code where you have tried it, it really is very very simple, to be honest if you couldn't manage it then i suggest you don't use any vba until you've learnt the basics as you'll never be able to maintain it or repair it!

You said you've tried and tried, as i asked provide the code where you have tried so i can see the changes, i can help you if i can see what you've already done!

[azmatullahkhan]

I accept, i request if you can help i will be for sure come back and learn from you alot but i need this file very urgrntly, i need your help to learn alot, but please give this file to me as i am in need of it badly please.

[Linspire]

Simon,

You provided the codes are not working, I'm having problem to compile the codes for the 1st line.

It said syntax error.

[Simon Lloyd]

Simon,

You provided the codes are not working, I'm having problem to compile the codes for the 1st line.

It said syntax error.

I wrote that code directly on here, the first line is missing a parenthesis and should look like this

Please Login or Register  to view this content.

[Simon Lloyd]

I accept, i request if you can help i will be for sure come back and learn from you alot but i need this file very urgrntly, i need your help to learn alot, but please give this file to me as i am in need of it badly please.

This is the last time of asking, post the code showing what you have tried so i can further help you!

[Linspire]

For your information, this is my first time do this visual basic coding

I have another compilation error,

"For without Next".

[Simon Lloyd]

For your information, this is my first time do this visual basic coding

I have another compilation error,

"For without Next".

Here's the code checked and and now has no compilation errors.

Please Login or Register  to view this content.

[Linspire]

Do you mind add on some comments,
after I compile it , there is another error message

"Run-time error '438':
Object doesn't support this property or method.

When I clicked debug, it highlighted the following code.

.body mycell.offset(0,-5).value & " has expired with expiry date " & mycell.value

I'm trying use your suggestion code be used as sending alert email to staff, and CC to managers in the document register excel spreadsheet.

[Simon Lloyd]

my mistake, with the straight forward sendmail you cant send .body, you'd need to open the outlook application, for what you want there are plent of worked examples on Ron De Bruins site, google "Ron de Bruin Sendmail" and you'll find his site with loads of great stuff, ideally you'll want to use ADO or CDO as this will negate the need for the user to "Allow" the mail to be sent from their pc.