If the column "Number of days worked in the last 14-day period" is >=13, I would like to automatically save the dates and number of days and names in a text file.
I have had a look at sending an email, but from what I can see I would need to click send, and I want this to be completely automatic.
Thanks in advance.
But what should be the "trigger" to start the procedure - shall it be initiated manually, by clicking a button, or done before closing the file, or just after data was added, o
And what if you changed data - it was 13 then 14 - shall it be written again?
How about corrections like was 13 deleted 1 day 12 remains ?
BTW. procedures to send mail could be also fully automatic - see for instance my favourite description here: http://www.rondebruin.nl/win/s1/outlook/amail4.htm or on other Ron's pages.
Best Regards,
Kaper
I just need to keep it as simple as possible. It is very unlikely that the data will change once entered, so I'm not concerned about that.
Id like the "trigger" to be after the data has been added, So if the number days is >=13 the file is appended.
I would love the email option if fully automatic, but I don't have a great understanding of the code involved and wouldn't be able to edit it for what I need.
The "trigger" code in worksheet code module
Main code in regular module (do not forget to change mail address to real one, and make sure you have outlook instaled):
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi Kapper,
Thanks for the code, it is workingalthough when a cell in column B is >=13 if I update any other cells in the row, it sends another email.
Can this be changed so it only sends the email if column B is >=13 and no other cells have an influence?
Many Thanks.
Dean
There is the issue with your layout which can make it not working (not only about mails bat at all). in some weeks (for instance week 30) you have first column for given week (the column which is used by your formula in column B) containing NUMBER OF DAYS WORKED, while in other weeks - like week 14 this year or week 13 of 2019 there is BASIC HOURS.
Going back to our code, try such change in worksheet code (replace old code, do not add whole second procedure with the same name):
it will send mail only when changes are made to column leading to change in column B (ignoring changes to say BASIC PAY RATE, HOLIDAY MONEY PAID, etc.Please Login or Register to view this content.
I will be updating all weeks to include the column for NUMBER OF DAYS WORKED. The additional code has fixed the issue I was having.
I have now been looking at which cells I want to paste into the email. I have found the. Range("A" & myrow & ":D" & myrow), but when I try to adjust it to include column G, I either just get an error or all colums from A to G. Not sure if .Range is is what I need, I have tried .Cells and using Union, but I'm getting nowhere fast.
Really appreciate all your help.
Cheers,
Dean
I have figured it out!
Please Login or Register to view this content.
Surely, could be written in a more compact way, but as it is working, I'd leave it as is. May be just singł cells referred as single cells, ie:And the same way with G4Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks