Automatic sort due date and send email

[Thainguyen]

I have an equipment maintenance template and I looking for a code can automatically sorting Due date for Weekly,Monthly, Quarterly and send a notification email to me or my team with out us to open excel template. On email body it should include a snapshot of quick information of the item need maintenance. Please see picture attached for template layout.

Additional information.
If you open the template attached in this post. You will see the due date for next service for weekly (G), Monthly (I), and Quarterly (K). I would like VBA code to pick up the date from these columns and automatic send me an email notification 3 days before the due date. Also, i would like the VBA code to take a snapshot or include the row of the equipment that needed to be service in the email body with hyperlink.

I really appreciate if someone could post the code on here. Thanks

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

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]

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

[DocAElstein]

Hello Thai,

Mostly I have just a short time on the computers per day. But I am glad to help, so post any questions that you have at any time, and I will gladly answer them when I look in to the Forum from time to time.

I see you have a new temporary gmail for our experimenting. Thank you. This is very useful. You have passed me the password privately, so we can share this for now. Later you can forget about the account, or you can change the password and continue using it just for your private use, ( and / or that of your colleagues )

Here is the info you have given me:
Our temporary email : excellearning12@gmail.com
Current password : xxxxxxxxxx - I will not publish this in the Forum. You have given this to me privately,

_.____________________

I did try to change couple thing on your code but i am not sure how it is working…..
I need the VBA to check for due date and send to a team email not only me. But I don't think they are going give me their Passwords.

You do not need the passwords from your team.
You do not need any passwords of the people to whom the program sends the Email
The Program can send the Email to many people.
You can add as many people to send to as you like. You do it like this:
.To = "ThaiAdresse@gmail.com"
.cc = "TeamMember1@t-online.de"
.cc = "Teammember3@gmail.com"
.cc = "TeamMember2@gmail.com"
.cc = “TeamMember205@someEmail.xyz”
.cc = "Anyone@AnyEMail.yahoo.com"
.cc = ……..
_.... etc.. etc….

There is no requirement to have the passwords for any team members. You do not need any password of anyone that you send the Email to. You only need their Email address . - You add their Email address to the list of .cc s as I show above

_.__________________________

I did try ….

You gave me a file privately.
You have saved the file as .xlsm. Yes, this is good
But..
You have put the code in the wrong code module.
You put it here:
WorksheetCodeModule.JPG : https://imgur.com/6O6Nj7m

That is incorrect.
Sub Workbook_Open() is a special code. it works automatically when the workbook opens and macros are enabled.
It belongs here:
ThisWorkbookCodeModule2.JPG : https://imgur.com/JlFFBWP




But this is not important for our experiments, because: if it is in ThisWorkbook Code Module, then the code will work automatically when the workbook is opened.
For our experiments it is better to leave it in the Worksheet ( Sheet1 ) Code Module
For our experiments we will leave it in Worksheet ( Sheet1 ) Code Module. This is easier for our experiments. It is easier for now, because then we do not need to keep closing and opening the workbook to get the code to work. Just for our experiments we can run the code manually. ( Later we can put the code in the ThisWorkbook Code Module )

We can run the code manually. This is better for our experiments:
To Run the code manually from Worksheet ( Sheet1 ) Code Module, do this:

_1) Open File, “Equipment- Maint RecordsThai1.xlsm” ( I send this to you privately as it has our temporary password in it )
_2) Enable macros
_3a) Hit Alt+F11 to see VB Editor, and then Double click on “Sheet1 ( EquipmentPM)” in VBA Project Window
Sheet1 ( EquipmentPM).JPG : https://imgur.com/N9AmTyI
Or
_3b) Right click on first Tab and select “View Code” : https://imgur.com/dFUcwtP
ViewCodeFirsttabCodeModule.JPG : https://imgur.com/rx4EnWP

_4a) Click anywhere in the code, _4b) and hit Play>, ( or Hit key F5 )
_4aClickInCode _4bPlay(or hit F5) .JPG : https://imgur.com/gdGldCt




See how you get on, and let me know how you get on.
( I send you the file, “Equipment- Maint RecordsThai1.xlsm” , privately later after I post this )

Alan

[DocAElstein]

Hello Thai
I cannot respond to your last PM to me. Your InBox is full.
You must delete some of your messages, before you may recieve any new PMs
Alan

[DocAElstein]

This is the message I was just trying to send to you per PM......

Hi Alan,
I did log in our share email but i can't find your template. I saw the test email but there no attachment in that email.

Hi Thai,
I sent you the file, “Equipment- Maint RecordsThai1.xlsm" attatched to an EMail this morning.
I sent it to you at your company EMail that you sent me.

In the Thread, ( This post: http://www.excelfox.com/forum/showth...0675#post10675 ) , I gave you instructions on what to do with that file.
Alan

[Thainguyen]

Hi Alan,
So far, I didn't receive any of your email with the template. That is weird. Can you please send it through our gmail.

I am using my template on my desk top with your VBA code and it is working. It sent notification through our gmail. Now how can i turn it to a good template and send notification through my outlook email? Also, can you help create Subject and Body on the email with hyperlink to the template. Example.

Subject: Tool and Equipment up for service
Body: Please review the list item need to be service below and open the hyperlink -->


Here is example of coding for another template that i had created in the past with Outlook email with Subject, body and hyperlink to the template.
Private Sub cmdNot_Click()

Dim OutApp As Object
Dim OutMail As Object
Dim fileName As String
Dim mSubject As String
Dim signature As String
Dim fname As String
Dim mBody As String
Dim rng As Range
Dim rng1 As Range
Dim ws As Worksheet
Dim mailTo As String
Set ws = Sheets("MRO")
fname = ws.Range("B4")
mSubject = "MRO " & " For " & Range("C6").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "2-SO\Material Request Form .xlsm"

Dim Path As String

mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and put your signature on the respective cell after you completed your task.<br><B>" & _
fileName & ".xlsm" & "</B> is created.<br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & Path & fileName & ".xlsm" & _
""">Files are saved here</A>" & "-->" & Range("C6").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"

With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With


With OutMail
'.To = "email"
.To = ""
.CC = ""
.BCC = ""
.Subject = mSubject
'.body = "Dear Team," & vbCrLf & vbCrLf & "Please open the file from below link and put your signature on the respective cell and save the sheet"
'.htmlbody = RangetoHTML(rng)
.htmlbody = mBody
'.Attachments.Add fileName
.display
End With
'ws.PageSetup.RightHeader = "&""Calibri,italic""&11& " & ws.Range("A1")
ActiveWorkbook.Close False
ActiveWorkbook.Close
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

[DocAElstein]

Hello Thai,
Thanks for the extra info, thanks and for emptying your InBox.
_.______________

So far, I didn't receive any of your email with the template. That is weird. ..

have you checked any Spam Folder you may have. ? Can you check if you have any company IT department to see if they block such Emails?

I used my German Telekom Email address to send to you.

I am just interested to see where the problem lies in case I have a problem with the Emails that I send generally from my German Telekom Email address: I am not aware of any problems. I am in constant contact with people using my Emails and was not aware of any problems with anything that I send not arriving.
So I am just curious..
Also, as we are using automation of Emails, it would be of interest to know if there are issues with any particular Email addresses with your company, as this may raise issues in the codes we are using
_.______________________________

Just out of interest I will re send the copies of the Emails I sent yesterday using my Telekom account, but this time using my gmail account.
I will send again to your company EMail
I will also send to out shared Email address**
Please tell me if you receive this information.

_.______________________________

**I will send as you have requested the File, “Equipment- Maint RecordsThai1.xlsm” once again, but this time I will send it on our shared address which you have registered for us, excellearning12@gmail.com

Finally I will also, just out of interest , send a copy of my Emails from yesterday from my Telekom address to our shared address which you have registered for us, excellearning12@gmail.com
_.__________________________

So I will send you three Emails, ****
_1 one on your company address, from my gmail account. This is just a copy of what I sent you yesterday.
_2 a new Email I will send with the file “Equipment- Maint RecordsThai1.xlsm” attatched using my Telekom account and sent on our shared gmail account, excellearning12@gmail.com
_3 Finally one on our shared address, from my Telekom account. This is just a copy of what I sent you yesterday

Please tell me what Emails you receive from those 3 which I send. I will send all three shortly after I post this Forum post which I am writing now
_.__________________________

...I am using my template on my desk top with your VBA code and it is working. It sent notification through our gmail...

.. so… It would appear that you have already managed to get a code version to work which is similar to that in the file from me, “Equipment- Maint RecordsThai1.xlsm”
But just to be sure, I suggest you check the file that I have sent ( will re send ) ; and follow my instructions from http://www.excelfox.com/forum/showth...0675#post10675 ; and make sure you understand what is going on
_.______________________

... Now how can i turn it to a good template and send notification through my outlook email?..

As I mentioned to you, I do not have Outlook. I have no experience using Outlook VBA code. But I did give you some information regarding the "smtpserver" issue per PM.
_.______________

... Also, can you help create Subject and Body on the email with hyperlink to the template. Example. .....

I suggest we take things one step at a time.

_1 Tell me what Emails you receive from the 3 which I will send to you in a short time after completing this Forum Post.
_2 Check my file, ( if you receive it ). Follow my instructions from http://www.excelfox.com/forum/showth...0675#post10675 and make sure you understand what is going on. – You may have already done something similar to this. But I am not sure exactly what code you used for your reported…”…..I am using my template on my desk top with your VBA code and it is working. It sent notification through our gmail….”…….
_3 We will address the issue …”…. Now how can i …… send notification through my outlook email?..”.. Hopefully this will just be an issue of getting the correct "smtpserver" But I am not sure.

_4 the other issues we will then look at later

I will take another look in over the next few days.
I suggest you review again all my posts and PM’s ( I suggest you make text or Word copies of PM’s, as , as you have experienced , the storage of them is limited at excelfox. We should also keep as much information as possible in this Forum Thread , for the benefit of others who may be following the Thread now or in the future. )




Some last points. These are just suggestions:
_A) . You might want to consider using a separate Email account , as we are doing here with the excellearning12@gmail.com for this project. I do this and have found it very convenient. This way I always have a separate source of copies of all sent Emails via my code, and I do not clutter my main Email accounts with this extra information. Such an account you could also share with your colleagues and team members. So they would also have a record of all
_B) It is very easy to arrange that the sent Email has an attachment such as an Excel File. Currently we are not using that option. The code could easily be modified to send attached a complete copy of the file, which can also be arrange to not include or to include the coding.




Alan

P.s. Please do not delete anything from our shared account, excellearning12@gmail.com , so that we can keep track of all infomation between us as well as all of our experiments



****EDIT: All 3 EMails were sucessfuly sent by me

[Thainguyen]

Hi Alan,
Thank you for all your hard work. I got your all your email and template now. It is working out great. Also, it did sent email to my work email when i am using the run sub/user form function. However, when i am open and close the template, it only send email to my gmail only.

Now I believe we can remove the testing code and working on the rest of the code to identify due date from column G, I, K to send an notification to me and my team.

[DocAElstein]

Hi Thai,
Yesterday I got sent to me Emails, presumably from the code that you were experimenting with.
It appears to me that you are making good progress. You seem to have the code working to some extent. Good

_.________________________________________________



Unfortunately, I do not quite understand what you are saying here:

… Also, it did sent email to my work email when i am using the run sub/user form function. However, when i am open and close the template, it only send email to my gmail only. .

I do not understand exactly what you are trying to say.
Because I do not understand this, I do not quite know at what stage you / we are.

I can see that you have successfully used the code to send Emails. I think you have done this using our shared Email account, excellearning12@gmail.com as the sender, using our code, Private Sub Workbook_Open().

I do not know if you have successfully used your company Email ( Outlook ) as the sender using our code, Private Sub Workbook_Open(). ??
Have you used your company Email ( Outlook ) as the sender using our code, Private Sub Workbook_Open(). ??
Is your plan still to use your company Email ( Outlook ) as the sender using our code, Private Sub Workbook_Open(). ??
If your answer to the above questions is Yes, then please tell me which "smtpserver" you used to enable our code to send from your company Outlook address. I would be interested to know that. ( As I mentioned previously, I have only used such codes to send from
either
Google Mail, gmail.com
or
German Telekom, t-online.de, Email accounts )


I would suggest that at this stage, that you do not try to get the code to work automatically when the Workbook is opened.
It easier to test the code working manually. It is very easy later to just transfer the code over to the ThisWorkbook code module.
But , It is easier to develop and test the code manually at this stage.

_.______________________-


I have reposted your given Outlook VBA code using code tags Here:
http://www.excelfox.com/forum/showth...0690#post10690

Code tags makes it easier to see in the Thread.


I will examine that code over the next few days. I think I understand your further requirements ….”….. create Subject and Body on the email with hyperlink to the template….” Etc..
I will look into this.



_.____________________

I will happily help you further, when I have time.
But I need some clarity as to what stage you are currently at.
I was not able to fully understand your last post ( http://www.excelfox.com/forum/showth...0688#post10688 )




Alan