Automatic sort due date and send email

**DocAElstein** · 05-05-2018, 01:25 PM

Hello Thainguyen,
Welcome to ExcelFox.

I don’t think anyone will be able to help you unless by coincidence they have done almost the same as what you want and recognise exactly what you want. The chances of that are very slim

On the face of it, what you want seems fairly easy, that is to say, it seems like the help I can give.

But your description of the problem is much too brief and lacking in detail. Remember that any description of your problem will be clear to you as you understand fully your project.
For anyone not familiar with your problem / project you will need to give much more complete detail.

Walk us through a thorough working example of what you want done, showing with hand filled in detail where necessary a before and after based on what a code should do for you

Your current description is, in my opinion, much to brief for anyone to understand exactly what you want. Someone trying to help will need to make assumptions and guesses that likely won’t be correct and so a lot of time would be spent/ wasted goiung back and forth to finally understand what you want. Voluntary helpers will be less inclined to want to spend / waste a lot of time in trying to understand the problem, compared to paid helpers who might welcome the many extra hours of paid work necessary.

Alan

**DocAElstein** · 05-13-2018, 02:49 PM

Hello Thainguyen,
I have no response from my earlier reply to your PM on me , so I will just do a short attempt, to get you started, and / or show you what sort of solution I can do…

Briefly:
what I have done, the code,
and
the results.

What I have done, the code
I saved your File as .xlsm extension so that I can put a code in it.

I assume you want the code to spring in when you open the Workbook.
So I have one code in the ThisWorkbook code module, which is set to kick in when the workbook opens, Private Sub Workbook_Open()
I assume the workbook is open every day so that a check can be done each day for your “3 days before due service date” criteria check.

Brief Code description:
Code is here: http://www.excelfox.com/forum/showth...0669#post10669

Code sections Rem 1 – Rem 2:
Captures your data from worksheet “Equipment PM”

Code section Rem 3
The current date is determined in the Excel Double Number Format ( http://www.eileenslounge.com/viewtop...=29398#p227569 )
Just for testing purposes ##### , I change that to 3 days before 15.03.2018, so as to test , as an example "Quarterly Next Service" of 15.03.2018. So on running the code you should get an Email telling you about that due Service on 15.03.2018, that is to say the info on row 20 for that Machine.

_Row\^Col	J	K
19	N/A	N/A
20	15.02.2018	15.03.2018
21	N/A	N/A

Worksheet: Equipment PM

Code section Rem 4
Determines the rows , if any, that you should be told about. ( In the test ##### example, that is just one row, that being row number 20 )

Code section Rem 5
This makes a table in the “HTML” coding type form which has the row info you want to be informed about.

Code section Rem 6
This code part does the Email sending. As I mentioned per PM , you will need to amend Email Addressee and password…
"YourEMailAddress"
"YourEMailPassword"
You may also need to change the "smtpserver" info: I tested it and it works for a gmail address, and the "smtpserver" is currently set for that.
Currently the Email is sent .To me, so you will want to change that as well.
The main body text sent is chosen to be given in HTML form, and the actual string given is that produced in code section Rem 5

The results:
So I tested it , using a spare gmail account of mine as the “sender”, and I sent it to my German Telekom Email address.

The test data in your workbook Looks like this:
http://www.excelfox.com/forum/showth...0666#post10666
http://www.excelfox.com/forum/showth...0668#post10668
http://www.excelfox.com/forum/showth...0668#post10667
and, as mentioned , I have an extra line for testing in the code which suggest the current date is 3 days before 15.03.2018.
So you want info of this form sent to you:
__142 __ NISSAN MCU-112A331.V __ Forklift __ A139
That info comes from this part of your test data supplied:

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	N
2	Machine EQ.ID	Manufacture	Model	Description	Serial Number	Weekly Date of Service	Weekly Next Service	Monthly Date of Service	Monthly Next Service	Quarterly Date of Service	Quarterly Next Service	Softwear
20	142	NISSAN	MCU-112A331.V	Forklift	A139	N/A	N/A	N/A	N/A	15.02.2018	15.03.2018

Worksheet: Equipment PM

So….
If the workbook is opened ( and macros enabled ) , then I find a few second later , this arrived at my German Telekom Email address:
GermanTelekomInBox.jpg : https://imgur.com/YI0ER9g
GermanTelekomInBox.JPG

GermanTelekomEMailDueMaintenance.jpg : https://imgur.com/12wvzte
GermanTelekomEMailDueMaintenance.JPG

_.________________

So if that all sounds something like what you want , and if you want more help, let us know.
I have not tested it thoroughly at this stage, nor fully ‘Commented and explained the code.

Alan

Ref:
http://www.excelfox.com/forum/showth...once#post10518