Automatic sort due date and send email

[DocAElstein]

Hi Thai,

So far, I didn't change anything on your VBA code. I just follow your instruction to test out the code to make sure it send email to my work and gmail. It is working find when i go to Visual basic and hit F5 to run the test code.
So I guess now we can move forward with the code
This is email is good now.
Yes you are correct the code is successful sending email (by click on F5 run) to my xxxxxx.com , company Email address and our shared gmail.
Please just discard my last question, Everything is working fine now.
Now we can move forward to adding subject and body message on the email and remove the test function out of the code.

I am still not quite understanding exactly what you tell me
I expect you may have difficulty understanding English
I was still waiting for some information regarding what you have done regarding testing the code with an Outlook account as the Sender

As I mentioned , I have no experience with Outlook. I understood originally that your final requirement was to use an Outlook account as the Sender ( …. "sendusername") = )
I have not been able to get any answers I can understand from you about that.

So I did some research and further experiments.
This is the results of my research and further experiments.

I did some searches on the Internet:
https://www.startpage.com/do/search?...mail&pl=chrome
http://lmgtfy.com/?q=Outlook+free+EMail+register
OutlookEMailSearch.JPG : https://imgur.com/dPbRfMR

I arrived here:
OutlookEMailSearched.JPG : https://imgur.com/d9hQbXQ

To my surprise, I see I can register an Email account with Outlook, without having Microsoft Outlook installed on my computer.
So I registered a new account for us to share
Excel Learning EMail account.JPG : https://imgur.com/7EWQ9q7

Username : Excel Learning

"sendusername" : "excellearning@outlook.de"
Password ( “sendpassword” ) : xxxxxxxxxxx --- I can give this to you privately

I have seen this:
Settings EMailSynchronising.JPG : https://imgur.com/m3kCAy8
Settings ShowAllSettings.JPG : https://imgur.com/fSt3ZjY
SMTP Settings.JPG : https://imgur.com/b0AF1RK

But
I have tried unsuccessfully to get the code to work with this Email account as the sender.
OhBollox.JPG : https://imgur.com/LTrrJOi
I have no idea how to proceed with the Theme of using my code with an Outlook Email account
I will give up for now with Outlook


So I will leave the Outlook Theme aside for now. I give up here. I do not know how to get the code working with excellearning@outlook.de as Sender

_._________________________


In a few days I will continue to try and help and move forward with removing the test date and adding subject and body message etc..

I will post again in a few days.

For now I will remain with our shared gmail account as the Sender.
.Configuration(LCD_CW & "sendusername") = "excellearning12@gmail.com"



Alan

[Thainguyen]

Hi Alan,
Please ignore the outlook email. I will work on that part by myself. Beside that everything on your code is working fine. I did try out your code and it did send a notification to my work email and our share email too. It is working good.

So I believe we should move forward to removing the test date and adding subject and body message and hyperlink to the template. Please send me the new template through out share email. Thanks.

Just one last concern, With your code. Does it check column G, I and K for due date?

just an example of OUtlook code I used in the past.
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
fname = ws.Range("A1")
mSubject = "Equipment" & " For " & Range("A1").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "Z:\2\Form\\Manufacturing Order.xlsm"

Dim Path As String
ws.Protect ("Equipment")
Path = "\\Equipment- Maint RecordsThai1.xlsm"
mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and change the date 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("A1").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"

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

[DocAElstein]

Hi Thai,
Thanks for reply and info…
_.________________________________________________ ___________________

Please try to use code tags when posting codes in Forum Posts:
http://www.excelfox.com/forum/showth...0690#post10690
http://www.excelfox.com/forum/showth...0700#post10700

_.________________________________________________ _________________________

Just one last concern, With your code. Does it check column G, I and K for due date?

Yes: This code line checks for that in 3 columns, G I and K
____If arrIn(Rw, 7) = TdyDbl + 3 Or arrIn(Rw, 9) = TdyDbl + 3 Or arrIn(Rw, 11) = TdyDbl + 3
__________G_____________________________I_______________________________K
That code line checks to see if the dates in the columns are = (Today's date + 3 days)

        If arrIn(Rw, 7) = TdyDbl + 3 Or arrIn(Rw, 9) = TdyDbl + 3 Or arrIn(Rw, 11) = TdyDbl + 3 Then
' check  for criteria in  Today  + 3 days             Today  + 3 days               Today  + 3 days
' check in  column G                 Or    column I               Or    Column  K
' check in  column 7                 Or    column 9               Or    Column  11

arrIn(Rw, 7) holds Column G .Value2
arrIn(Rw, 9) holds Column I .Value2
arrIn(Rw, 11) holds Column K .Value2
( Excel holds dates as a number : https://bettersolutions.com/excel/da...ates-times.htm
.Value2 for 3rd January, 1900 is _ 3
.Value2 for Saturday, 2nd June 2018 is 43253

If today is Saturday, 2nd June 2018, then _ Let TdyDbl = CLng(Now()) = 43253

This will always give 43253: Let TdyDbl = CLng(DateSerial(2018, 6, 2)) = 43253

This will always give 43255 : Let TdyDbl = CLng(DateSerial(2018, 6, 4)) = 43255 (Monday, 4th June, 2018 ) )


_.________________________________________________ ___________________________________

... I believe we should move forward to removing the test date and adding subject and body message and hyperlink to the template.

Yes, I agree.
Much of the information you need is already in this Thread.
But in a few days I will post again and explain with some examples the current and next codes.

So, I will post some more information in a few days


Alan

[DocAElstein]

Hello Thai,
In this Post, Post #14 ( http://www.excelfox.com/forum/showth...age2#post10705 ) I am explaining 3 files which I will send to you via our shared Email address
excellearning12@gmail.com

All files use the same code. The code is very similar to the very first one that I gave and explained.
http://www.excelfox.com/forum/showth...0669#post10669 http://www.excelfox.com/forum/showth...0670#post10670


All files have a code in the Worksheet code module, ( https://imgur.com/rx4EnWP https://imgur.com/dFUcwtP ) You should run the codes, as you have been doing so in F5

The purpose of this Post, Post #14 ( http://www.excelfox.com/forum/showth...age2#post10705 ) is to help you better understand how the code works.
For each file I have changed/manipulated the test data so that the code will give different results on different days. The File name includes the date on which you should run the code.
I hope that this will help explain the code to you.

The test data I have changed/manipulated such that there is maintenance due 3 days ahead of the date or the File name.


The only modification I have made is to comment out the Test line, so that the test date is removed:

 ' Let TdyDbl = CLng(DateSerial(2018, 3, 15)) - 3 ' To test only #####

Because of this modification, the current days date is now determined by this line:

                 Let TdyDbl = CLng(Now()) ' like 43233 for 13 May 2018

This returns the date for the day in which the code is run.
So I have changed/manipulated the data so that if you run the code on the date of the file, then certain data will meet the criteria for the reminder to be sent of the maintenance required in 3 days


File “RunOnTuesday5thJune.xlsm”
I have modified the data thus:

Row\Col	A	B	C	D	E	F	G	H	I	J	K
19	141	Heller	1826 MK5	Reflow Oven	A138	N/A	N/A	N/A	N/A	N/A	N/A
20	142	NISSAN	MCU-112A331.V	Forklift	A139	N/A	N/A	N/A	N/A	11.05.2018	08.06.2018
21	142	NISSAN/yearly oil change and lube	MCU-112A331.V	Forklift	A140	N/A	N/A	N/A	N/A	N/A	N/A

Worksheet: Equipment PM
So , as 08.06.2018 is 3 days ahead of 5th June , then ,
If you run this file on Tuesday 5th June, then you should you should receive per EMail the appropriate info for the due maintenance on 8th June.
142 NISSAN MCU-112A331.V Forklift A139


If you wish to test the code on any other day than 5th June, then add this test line:

 Let TdyDbl = CLng(DateSerial(2018, 6, 5)) ' - 'To test only for Tuesday5thMay =43256

_.________________________________________________ ________________________


File: RunOnWednesday6thJune.xlsm
I have modified the data thus:

Row\Col	F	G	H	I	J	K
18	06.04.2018	13.04.2018	N/A	N/A	N/A	N/A
19	N/A	N/A	12.05.2018	09.06.2018	N/A	N/A
20	N/A	N/A	N/A	N/A	12.05.2018	09.06.2018
21	N/A	N/A	N/A	N/A	N/A	N/A

Worksheet: Equipment PM
So , as 09.06.2018 is 3 days ahead of 6th June , then ,
If you run this file on Wednesday 6thJune May, then you should you should receive per EMail the appropriate info for the due maintenance on 8th June.
141 Heller 1826 MK5 Reflow Oven A138
142 NISSAN MCU-112A331.V Forklift A139


If you wish to test the code on any other day than 6th June, then add this test line:

 Let TdyDbl = CLng(DateSerial(2018, 6, 6)) ' - 'To test only for Wednesday6thJune =43257

_.________________________________________________ ________

File RunOnThursday7thJune.xlsm
I have modified the data thus:

Row\Col	E	F	G	H	I	J	K
17	A136	N/A	N/A	N/A	N/A	N/A	N/A
18	A137	03.06.2018	10.06.2018	N/A	N/A	N/A	N/A
19	A138	N/A	N/A	13.05.2018	10.06.2018	N/A	N/A
20	A139	N/A	N/A	N/A	N/A	13.05.2018	10.06.2018
21	A140	N/A	N/A	N/A	N/A	N/A	N/A

Worksheet: Equipment PM
Because of the modifications, we have a Weekly, a Quarterly, and a Monthly maintenance due on 10 June.
So if you run the code 3 days earlier, on the 7th June, then you should receive the appropriate info per Email, thus:
137 Juki K3 Screen printer A137
141 Heller 1826 MK5 Reflow Oven A138
142 NISSAN MCU-112A331.V Forklift A139
gmailInputRunOnThursday7thJune bw table.JPG : https://imgur.com/Dh38G4O
gmailInputRunOnThursday7thJune bw table.JPG
If you wish to test this code on a day other than Thursday, 7th June, then add this code line:

 Let TdyDbl = CLng(DateSerial(2018, 6, 7)) ' - 'To test only for Thursday7thJune =43258

_.________________________________________________ _-

See how you get on.
Please report back to me.

I will post again in a few days.

Alan

_.__________________________



The three files, RunOnTusday5thJune.xlsm , RunOnWednesday6thJune.xlsm , RunOnThursday7thJune.xlsm , I will send now via our shared Email, excellearning12@gmail.com

[Thainguyen]

Hi Alan,
I really appreciate for your time and effort.
All of your templates are working WONDERFUL. I did test all three of them and they are working GREAT. Except one minor issue that the password for our share email is incorrect in the code. I will send you our share email password again.

What is our next step on this template?
Can we include wording on the email body and hyperlink such as " Your Tool/Equipment is up for Maintenance service, Please click on the file to open Equipment PM template." Open File here.
Also, I notice the code only copy and past the text from excel to email. I wonder if we can include like the picture below.
Equipment PM.jpg

Again, Thank you for your time and effort to help out on this. I mean alot to me.

[DocAElstein]

Hello Thai,

one minor issue that the password for our share email is incorrect in the code. I will send you our share email password again……
here is our share gmail password.
xxxxxxxxxx....

Thanks very much for this information.
I see now that I made a mistake.
You are correct: I way using the wrong password. That explains why I had difficulty getting the code to work with excellearning12@gmail.com as Sender

I had mixed up passwords.
You are correct: This is the gmail share account that you made:

excellearning12@gmail.com
Exxxxxxxxxxxxxx

_-._________

( I also made an account for us with Outlook ( http://www.excelfox.com/forum/showth...0698#post10698 )

excellearning@outlook.de
Xyyyyyyyyyy

As you can see, I had mixed up the passwords. )

Thanks for pointing out my mistake.

_.__________________________________

I notice the code only copy and past the text from excel to email. I wonder if we can include like the picture below…….

I think it should be possible to meet all your requirements.
Just for example: this is the sort of Mail I receive everyday from my wife.
ExampleEMail.JPG : https://imgur.com/MN5a0Ny
ExampleEMail.jpg
I use a very similar code to that which we are developing.

So you see, all is possible, but will take me some time to do different formatting.

-.________________________________

…
_ (i) Can we include wording on the email body
and
_(ii) hyperlink such as " Your Tool/Equipment is up for Maintenance service, Please click on the file to open Equipment PM template." Open File here.
.

_ (i) Extra wording is easy. No problem
_(ii) I have not myself done a hyperlink as you want. But I am keen to learn how to do this as it will be useful to me also. I am sure this is also achievable



_.________________________________________________





I will post again when I have time, possibly in a few days.


Alan

[DocAElstein]

Hi Thai,
I have made a start at including a header in the received Email
The file, TestInitialHeaderThursday7thJune.xlsm , I will send via our shared Email "excellearning12@gmail.com"

That file is only intended to test the Header for now.
Run the code , Sub Thursday7thJune(), and you should receive this Email message:
gmail TestHeader.JPG : https://imgur.com/BW4Ta2y
gmail TestHeader.JPG

For details of the development of that code see here:
https://tinyurl.com/yawrmr9k
https://tinyurl.com/y94l6tyd
https://tinyurl.com/ydyegbq2

Let me know if you have any comments

I will make further progress when I have the time.


Alan

[Thainguyen]

Hi Alan,
Your code is working good. However, i noticed on header email missing the word "Service" so i added in your code.

Also, there is no borders for header. Can we include the "all borders" for all.

I believe we are close to the final for this template. There just a few adjustment and code needed.
Borders.PNG

[DocAElstein]

Hi Thai

Also, there is no borders for header. Can we include the "all borders" for all.

I am not too familiar with HTML coding, but simple modifications can be found quite easily by a couple of ways..

_way 1) some simple internet searching, for example:
http://lmgtfy.com/?q=HTML+code+Borders
https://www.w3schools.com/tags/att_table_border.asp ,
TryBorders3Schools.JPG : https://imgur.com/YATVQou
TryBorders3Schools Run.JPG : https://imgur.com/RL99ggW


_way 2) excelfox supports HTML in the test area sub forum ( http://www.excelfox.com/forum/forumd...p/17-Test-Area ,
excelfoxTestAreaSubForum.JPG : https://imgur.com/OFhrvff )
excelfoxTestAreaSubForumHTMLcodeisOn.JPG : https://imgur.com/JvHH9l3
So you can post there to test.
To do this, start a new thread, give a title such as “Just testing HTML Tables for Email sending”
excelfoxNewthreadTestArea.JPG : https://imgur.com/AfdzBVx
So you might want to start a new Thread, for example:
Use a title and start of post like…
This is just a test of HTML tables in support of this excelfox Thread
http://www.excelfox.com/forum/showthread.php/2253-Automatic-sort-due-date-and-send-email?p=10715#post10715
excelfoxNewthreadTestArea2.JPG : https://imgur.com/7YfCb84

So you can experiment
If you put the code in HTML tags, then you will see the code in the final post.
If you do not put the code in HTML tags, then you will see the table that the code will produce in the final post
Code With and Without HTML Tags In Editor 2.JPG : https://imgur.com/O84d4SE
Code With and Without HTML Tags In Editor.JPG : https://imgur.com/f4OVEJ6
After posting something like in those screenshots, then you will see this in the final post:
http://www.excelfox.com/forum/showth...0717#post10717
Finally Posted With and Without HTML Tags.JPG : https://imgur.com/vdliA9z



_.________________

Immediate Window
Note also, that in my codes there is often a test line, such as this:
Debug.Print strHeader
If after running the code, then on passing of that code line, a copy of strHeader is passed to the Immediate Window.
You are able to see this final string, strHeader : If you are in the VB Editor and type Ctrl+g, then you will see something like this:
FromVBEditor Ctrl+g TorevelImmediateWindow.JPG : https://imgur.com/68BfnYq
This can be copied from the Immediate Window and used in either way_1 or way_2 above

_._____

_.________________________________________________ ______________________


You may see differences in what you see finally in the received Email. …. I am not sure why this is. … I expect possibly that different HTML reading software may read / interpret HTML slightly differently.

So some further adjustment may be needed.

I made a start here:
http://www.excelfox.com/forum/showth...0718#post10718

and the file ( “TestHeaderWithBordersThursday7thJune.xlsm“ ) I send by our shared Email, excellearning12@gmail.com , has that initial attempt.
_._

I will look further at the further requirements when I have time

Alan.

[DocAElstein]

Hi Thai,

I have a question for you:

Do you prefer to see in the received Email this sort of thing:

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
142	NISSAN	MCU-112A331.V	Forklift	A139	N/A	N/A	N/A	N/A	13.05.2018	10.06.2018

Or do you prefer to see this?

Machine EQ.ID	Manufacture	Model	Description	Serial Number
142	NISSAN	MCU-112A331.V	Forklift	A139

Or do you prefer to see this?

Machine EQ.ID	Manufacture	Model	Description	Serial Number	Weekly Next Service	Monthly Next Service	Quarterly Next Service
142	NISSAN	MCU-112A331.V	Forklift	A139	N/A	N/A	10.06.2018

Which do you prefer?
Or do you want some other combination of columns?




Alan