Excel-email Macro

[Olainium]

Hello guys!

I'm new at this forum so hello everyone!

I'm currently working on a excel-sheet to tell due dates of medicine. I've done all the conditional formatting (red when expired, yellow today+7.) needed and the macro to update the sheet when it's opened.
The thing I am having trouble with, since i'm no expert, is to make the email-macro which will trigger when a date in a column is 7 days ahead or same date as today's date.
It seems very straight forward, but I'm just not able to figure it out. Any help would be appreciated.

Best Regards,
Olainium

[Olainium]

Nothing?

[jaslake]

Hi Olainium

Welcome to the Forum!!

You may well be correct

It seems very straight forward

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Olainium]

Hello Jaslake!

Thanks for your answer. I will attach the document, bet you all know where to see it all.Udløb(2).xlsm

So.. What im basically looking for is a way to get excel to send me an email when dates in D > Today. I only need excel to send one email, just a notification, summing all the expired dates if there are multiple of these.
When the date turn red or yellow, it should email me.
Thanks!

BR,
Olainium

[jaslake]

Hi Olainium

This Code in the attached appears to do as you require. Insert the Email address as appropriate.

CTRL + x will fire the Code.

Please Login or Register  to view this content.

[Olainium]

I dont know if its just me messing things up, but when i press ctrl-x outlook opens up and wants me to type a msg. I've attached the screen that comes up when im pressing.ctrl+x.PNG
If it's not just me messing up, it's not doing what im searching for. I've already made a auto_update for the sheet which updates to the current date on startup. Its the same i want the email to do - send whenever excel is opened with a "standard" generated message depending on what medicine is out of date to a specified non-changing email-address.
Any ideas?

[jaslake]

Hi Olainium

Unfortunately many of us using IE are unable to view png files on this Forum. Having said that, have you run the Code on your Sample File?

[Olainium]

Yes, I ran the one you attached to your comment.
Here's the img as JPEG. ctrl+x.jpg

[jaslake]

Hi Olainium

This is what I get when I run the Code with CTRL + x...

Olanium.jpg

[jaslake]

Hi Olainium

I'm wondering if Date Format may be an Issue...

[Olainium]

Seems reasonable. Im running Date/month/year. Seems like you're running month/date/year.

[jaslake]

Let me look at that...

[jaslake]

Hi Olainium

Try this File...I've reformatted the Dates...this is now what I get...

Olanium 1.jpg

[Olainium]

I am still getting the picture before. Also, it should only pick the ones out-dated or 7 days before outdated. It seems like in this code it takes everything. The macro should run automatically on startup too thou.

[jaslake]

Hi Olainium

We can deal with this at ANY time...not important until we get the Code reporting records you wish reported

The macro should run automatically on startup

THIS is a Date Formatting issue and I'm not certain how to solve it

It seems like in this code it takes everything.

I don't get that on my Platform.

I'll look at a different approach.

[jaslake]

Hi Olainium

This Code is in the attached and will run when the Workbook is opened.

Edit...the Forum is not allowing me to attach the File...I'll try later.

Add this Code to the ThisWorkbook Module

Please Login or Register  to view this content.

This Code goes in a General Module.

Please Login or Register  to view this content.

[Olainium]

Hey buddy!

Thanks for all the time and effort you put in this! Really appreciate it. It seems to be working for me right now, but when I change the dates to nowhere close to expired or +7 days the code and the workbook bugs out. I'll post a picture in the morning if needed.

[jaslake]

Hi Olainium

I'd like you to demonstrate this with a File attachment...pictures will be of little help.

when I change the dates to nowhere close to expired or +7 days the code and the workbook bugs out

[good4un2u]

This might help

Please Login or Register  to view this content.

[Olainium]

Hey,
Good4un2, im getting i is not defined using your code.

Jaslake i've attached a sample. I've changed all the dates to far from now. You'll get the debug.Udløb(2) (2).xlsm

[jaslake]

Hi Olainium

Try the Code in the attached...

[Olainium]

It sure opens now, but with the phrase "No records found". Is that what it should do?

**EDIT** Yeah it is, figured it out Changed the msg to what i want it to say Thanks man! Really appreciate it! I'll do some error-hunting now.

[good4un2u]

Hello Olainium,
My code was just a snippet of code that might help. You would need to declare i.

Please Login or Register  to view this content.