Email from Excel

hi All,

Its probably an old topic but I would really appreciate some help with this.
I am trying to use this spread sheet to manage our purchasing requirements .

I need to get an email sent to the address listed in u2 and v2 , when anything becomes "DUE" in the column Q.

Subject of the email

Job no - from cell "A4"
Component due - from the column U , corresponding to the item that has become "DUE" in Column "Q"

and the Text DUE.

Dont really need anything in the body of the email.
But wouldn't hurt to have the "REQD" date listed in the body.

Body :
This is due on on , the date from the column "I" corresponding to the item that has become due.

I am very new to VBA , any help would be greatly appreciated.

Thanks
Kind Regards
winsomeness

the idea is that the user gets multiple emails.

Which ever item is due on column Q , one email for every item that is due .

the information to be emailed should be

Job no from "A4"
corresponding component name from "U" and also may be when it was actually required from Column "I"

perfect .thanks humdingaling.

Is there any way to do this automatically when the sheet is opened , without having to run the macro
thanks again

that's brilliant . now just have to worry about all the emails that will come through everyday.
I dont suppose there is any way to only email once for everyday , is there ?

That's great.

Or would there be an option where I could open say 20 workbooks at once .
Use a shortcut Key to run the code across all the open workbooks.

So it does not matter if someone else opens the sheet later in the day or even if I have to open the sheets later in the day.
the code wont run unless the shortcut key is used.

I wouldn't want to hit the shortcut key 20 times , it would be good if the shortcut works across all open workbooks.

let me know what you think

Kind Regards
winsomeness

hmm ..

I have a folder called "Purchasing Aids "
Within which I have this file

".Template - Purchasing schedule calculator rev 10"

Each time a new Job is released , I would copy this template and paste within the same folder
Rename it to "JOB no - Purchasing schedule calculator rev 10"

so every time i will delete ".Template" and replace it with the unique Job number say "2060"
at the moment i have about 30 Jobs so 30 individual spread sheets.

so it will look like "2060 - Purchasing schedule calculator rev 10"

As Jobs are completed I will simply move them into a another folder "completed"
I will take your suggestion , which ever way you think is best to manage it , I will be happy to follow


let me know what you think
Kind Regards
winsomeness

i downloaded the registry file
deleted 15 aug line and saved
opened again
it entered the line again and put a y next to it

then i opened my template , i got the email about the "due" items based on the code you provided yesterday.
I saved and closed it .
Opened it again and was hoping that it does not send the email again . but got the email again.

How do I make the macro refer to regsitry.

That code , I think i had that when i was trying before I got the code from you.
I have deleted all that after I got your code

the files can just be xslx

The files can be "xlsx"

I thought they had to be xlsm for the macro to work
so they do not have to be xlsm

oh ok so thats what the xlsm is

No the individual template sheet is actually just a "xlsx".

I made it into a macro enabled file just before uploading here.
So i shall leave that as "xlsx" and delete all codes from the VB developer section.

Shall wait for the new code that you are working and just use it on the registry spreadsheet within the same folder as you had suggested.

hi mate

I tried it

changed the location on the registry file
Const strFolder As String = "M:\Procurement\Purchasing\Purchasing aids\WIP\Winsomness\Test" 'folder in which all your files are kept

Deleted 15th aug saved and closed
open the registry - winsomeness v2 file

It enters 15th aug and enters Y and no emails sent is still o
and i am not getting any emails.

I changed the cell inside the spread sheet to have an actual email address
I have deleted ' in front of send and put it infront of display and still no emails being sent

any ideas ?

Please advise accordingly

Kind Regards
winsomeness

done
thats just going to make things so much easier

thanks heaps

Kind Regards
winsomeness