Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel automatic email


(!)

mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
26-Jun-2012, 12:40 PM #1
Solved: Excel automatic email
Hi Everyone,
my name is steve and this is my first post. I have been asked to compile a system to recall calibrated items within the company i work for, I must stress tht my expierience with excel is very limited.
Attached is the system so far (It is the work of many butchered examples off the net, and several people who know a little at work).
I am trying to achieve a solution that when an item comes within three months of its expiry date the date column turns yellow, when 30 days or uner it turns red (Possibly gets transfere to another sheet)and then emails a short mail incluing cells B,C,D an L of that row to the "Area Owner" . I'm using office 2007, anyone think this is possible? or better still help me with this problem.
I would appreciate it so very much!

By the way, the password is ; Kalibratedbyme (capital K)
Regards,
Steve
Attached Files
File Type: zip example of cal system.zip (204.5 KB, 68 views)

Last edited by mrbluecharge; 27-Jun-2012 at 12:19 PM.. Reason: forgot to include password
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
26-Jun-2012, 04:39 PM #2
Welcome to TSG mrBlueCharge. I'm not real smoothe in excel and could kludge something together with a vbscript. someone else could server you better. Seems I've seen a similar solution here before.

But (the reason for my post) your spreadsheet is password protected
Good luck
mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 06:53 AM #3
Sorry all the password is. kalibratedbyme (capital K)

Thanks again

Last edited by mrbluecharge; 27-Jun-2012 at 12:19 PM..
mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 06:55 AM #4
Any solution would be fantastic draceplace thank you!
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
27-Jun-2012, 09:43 AM #5
Pass word doen't work. Search TSG with 'send email excel' Lots of post there.

This one has a macro that you could edit to read the cells then test dates. You might put the email address on each row to make this easier.
http://forums.techguy.org/archive-bu...el-quotes.html
mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
27-Jun-2012, 12:18 PM #6
Hi draceplace,

I have just tried the solution you gave, i finally got it o work, but it is not really suitable as all my cells will constantly filter by date leaving the macro button not alligned with its specific row, if you could look again sometime i would be very grateful

PS the password Kalibratedbyme strats with a capital "K"

Regards,

Steve
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
27-Jun-2012, 12:38 PM #7
Quote:
Originally Posted by draceplace View Post
Pass word doen't work. Search TSG with 'send email excel' Lots of post there.

This one hasa macro that you could edit to read the cells then test dates. You might put the email address on each row to make this easier.
http://forums.techguy.org/archive-bu...el-quotes.html
Just saying this a place to start, the macro generates an email and reads cells. You would need to alter it to read each line of the worksheet and look at the Calibration due days (the capital K worked!). When greater than xx then yellow, When greater than 90 then red and email.

You might pull that code and start a new thread or read some of the other threads and see if one is closer to what your looking for. This has been done before you just have to find it and tweek it a little.
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
29-Jun-2012, 10:11 AM #8
I think you'll like this. Unzip and put Drop_XL_SYS_CAL_Email01.vbs in the same folder as your excel stuff. Then drag and drop your excel spreadsheet on top of it and it should process for emails.

Disclaimer - this stuff may blow everything up. Use at your on risk. I have office 2010 and window7 but this should work for you
Attached Files
File Type: zip Drop_XL_SYS_CAL_Email01.zip (1.7 KB, 85 views)
mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 12:18 PM #9
I'll get right onto and try it, as soon as I have any result I'll let you know, thanks again for your time, I'm at my witts end!!!
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
03-Jul-2012, 10:57 AM #10
Any luck with this lastest code?
mrbluecharge's Avatar
mrbluecharge mrbluecharge is offline
Member with 7 posts.
THREAD STARTER
 
Join Date: Jun 2012
04-Jul-2012, 08:38 AM #11
WOW
The code works brilliantly, I really cant believe how someone is willing to spend their own time and effort to help someone else.... All i can say is thank you so much this really does show there are good people out there.

Regards,
Steve.
draceplace's Avatar
draceplace   (Dan) draceplace is offline
Computer Specs
Member with 1,781 posts.
 
Join Date: Jun 2001
Location: Conway, Arkansas
05-Jul-2012, 11:43 AM #12
Glad it works for you, I modified an 'emailer' I had written for my own use to match up to your spreadsheet.

Depending on your user security settings and how your email is configured you may be able to change the line

MyItem.Display to MyItem.Send and the email will go out immeadiatly. I find it helpful to review the emails before they go out.
anishmathw's Avatar
anishmathw anishmathw is offline
Member with 3 posts.
 
Join Date: Jul 2012
19-Jul-2012, 11:51 AM #13
Hi Draceplace,

Please give me some help in solving this task. Its posted here

http://forums.techguy.org/business-a...elp-excel.html
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑