Automatic sort due date and send email

[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

[Thainguyen]

Hi Alan,
I prefer the first one on your comment above.

Best Regards,
Thai

[DocAElstein]

Hi Thai

I prefer the first one on your comment above.

As next step, for this post , Post #22, ( http://www.excelfox.com/forum/showth...0723#post10723 ) , I will explain here the code to get the full column information..

Currently this code section produces a full table:

Let strHeader = "<table border=""1"";width=" & 1623 * Adj & ">" & vbCrLf & _
"<col width=" & 80 * Adj & ">" & vbCrLf & _
"<col width=" & 207 * Adj & ">" & vbCrLf & _
"<col width=" & 113 * Adj & ">" & vbCrLf & _
"<col width=" & 143 * Adj & ">" & vbCrLf & _
"<col width=" & 158 * Adj & ">" & vbCrLf & _
"<col width=" & 115 * Adj & ">" & vbCrLf & _
"<col width=" & 97 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & vbCrLf
' 4.5b) Single header row
Let strHeader = strHeader & _
"<tr height=17>" & vbCrLf & _
"<td>Machine EQ. ID</td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Manufacture </td>" & vbCrLf & _
"<td>Model</td>" & vbCrLf & _
"<td style=""color:Black;background:#D8D8D8"">Description</td>" & vbCrLf & _
"<td>Serial Number</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly     Date of Service</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly      Next Service</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly       Date of Service</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly        Next Service</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly         Date of Service</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly         Next Service</td>" & vbCrLf & _
"</tr>" & "</table>"
Debug.Print strHeader ' Ctrl+g from VB Editor to show code in Immediate Window after Run : https://imgur.com/68BfnYq

After that part of the code is run, we see from this code line, Debug.Print strHeader , the following produced in the Immediate Window.
<table border="1";width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description </td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr> </table>

That string code above is also the HTML code sent _.. , (
__.htmlbody = strHTML = strHeader
__.Send ' Do it )
-.. in the Email to produce the final header information thus
Column1RecievedHeader.JPG : https://imgur.com/bctC5Yl

_. The above code section basically does two things, _1: defines the size and format of the table, and _2: adds the first header row.

_.___________

We now need to adjust the code to add the required data lines for servicing due in 3 days time.

_.__________

My original coding produces those lines ( rows ) not cell ( column ) by cell, but rather in a looping process. Code section, '5b) data rows , produces approximately something of this form this, as seen for example in the Immediate window using Debug.Print ProTble :
<tr height=16>
<td>137</td>
<td>Juki</td>
<td>K3</td>
<td>Screen printer</td>
<td>A137</td>
</tr>

<tr height=16>
<td>141</td>
<td>Heller</td>
<td>1826 MK5</td>
<td>Reflow Oven</td>
<td>A138</td>
</tr>

<tr height=16>
<td>142</td>
<td>NISSAN</td>
<td>MCU-112A331.V</td>
<td>Forklift</td>
<td>A139</td>
</tr>

As we see, there is the information required in the HTML string to give 3 rows in the above example and each row has 5 cells ( columns ) ( That 5 column ( cell ) information is given within 5 pairs of code tags like
<td>141</td>
<td>Heller</td>
<td>1826 MK5</td>
<td>Reflow Oven</td>
<td>A138</td>

_._________________________-


My original coding produced the coding just for the first 5 cells ( columns ) in a row.
If we make approximately this change to the current test code, changing
.htmlbody = strHTML = strHeader
To
.htmlbody = strHTML = strHeader & ProTble
_.. then those required data rows are added to the sent HTML table string code thus giving after execution of .Send , the following received EMail:
SentFullHeader&5cells.JPG : https://imgur.com/1oguroR


So basically, we just need to change this line from
__ For iCnt = 1 To 5
to
__ For iCnt = 1 To 11

Once these adjustments are made we will receive the following:
SentFullHeader&11cellsValue2.JPG : https://imgur.com/u0DmPh3
We see a problem here , that the date is in the .Value2 format
One way to overcome this problem is to introduce a second capture data range array, arrInV() . This array is then used to produce the data table , ProTble
Then we simply change this line from
LisRoe = LisRoe & "<td>" & arrIn(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf
to
LisRoe = LisRoe & "<td>" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf

Then the received Email will look like this:
SentFullHeader&11cellsValue.JPG : https://imgur.com/22yLeLy


_._______________________-

Some other minor adjustments are needed to get the colour into some data cells in the received EMail
For example, we change this _ ..

     LisRoe = LisRoe & "<td>" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf

_.. to something like this:

            Select Case iCnt
             Case Is = 7: Let LisRoe = LisRoe & "<td style=""background:#92D050"">" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf ' green background column G ( column 7 )                   '  ' -1 is because Split Function returns array of string types in 1 Dimensional array starting at indice 0, so our jCnt is one too big
             Case Is = 9: Let LisRoe = LisRoe & "<td style=""background:yellow"">" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf ' vellow background column I ( column 9 )                   '
             Case Is = 11: Let LisRoe = LisRoe & "<td style=""background:#D8D8D8"">" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf ' grey background column K ( column 11 )                   '
             Case Else: Let LisRoe = LisRoe & "<td>" & arrInV(arrRws(jCnt - 1), iCnt) & "</td>" & vbCrLf
            End Select

Finally this will produce this as the received Email:
SentFullHeader&5cellsValueFullFormat.JPG : https://imgur.com/dQbW5lR


_.________________-


Sent via our shared Email, ( excellearning12@gmail.com ) , is a file to try,
“TestFullHeaderandFullRowsThursday7thJune.xls m”

_._________________-

I will post further, possibly tomorrow.
Alan

[Thainguyen]

Hi Alan,
I did test out your template and it work GREAT. That is Perfect Header.

I did try to putting this code in for email body. It is not perfect but i guess you can help to improve it better.

.HTMLBody = strHTML & "Hi Alan, This is a testing messgae for the email body."

Now i guess we are going to work on body email and hyperlink to the file.
Thanks

Best Regards,
Thai,

[DocAElstein]

Hi Thai,
Re: hyperlink to the file
I have done some research into this.
It would appear that it is not possible to make a hyperlink in the body of a received EMail which will directly somehow “present” you with an Excel file.
An Excel file cannot be opened with an internet Browser. ( Or if it can, I have not been able to find a way to do this. )

From my research it appears that a hyperlink in a received Email body can typically only be of a file form which can be read by a Browser. This will typically be of
Either:
_(i) a form of a .HTML or .htm file, ( as an example, such as this, which I have successfully read from my browser, file:///F:/2018/Juni2018/DailyProtableFilled.htm by inserting that link directly into the URL bar
htmFileOpensWithBrowserGoogleChrome.JPG : https://imgur.com/asm7aiw
File example: https://app.box.com/s/p84sm14vj11uenabjtzoo0qbq8dgd574 )
Or:
_(ii) a link to some place where the file can be downloaded from. Such a link would start with the usual form similar to https://lasjfa.......

So far I have only been able to embed an hyperlink in the body of the received Email which goes to an internet site, that is to say, _(ii), a form such as https://lasjfa.......

So, currently I do not know how to produce a hyperlink in the Email received body text which will directly take you to an Excel File,

_.__________________________

Here are a couple of alternative solutions.

Alternative 1
The basic idea here is that the current File is attached to the sent Email

This requires the use of a temporary File, as you can not seem to attach the file currently running the code to attach it.

I have included a demo File for you , TestFullHeaderandFullRowsThursday7thJuneAttatchmen t.xlsm , sent privately to you.

_.________________________


Alternative 2
In this suggestion I use a hyperlink in the received Email that contains the URL share link to the file stored in a file sharing internet site, ( box net )
The team member can then download the file from there, ( and they have the possibility to upload the file after they have made any adjustments to it )

So the received Email has a form such as this, with a Hyperlink to click on:
LinkIngmail .JPG : https://imgur.com/BU3En9M
LinkInGerkanTelekom .JPG : https://imgur.com/PhDJSww

After clicking on that Hyperlink, the Team Member has the possibility to download the file
Free10GB box net 10 .JPG : https://imgur.com/fLxuKya

I have included a demo File for you , TestFullHeaderandFullRowsThursday7thJuneLink box net.xlsm , sent privately to you.


( Further details about the box net account that I have created for us are here : http://www.excelfox.com/forum/showth...0725#post10725 )
_.____________________________________

Alan

[DocAElstein]

Hi Thai,

Here is the code I am use to attached hyperlink to email outlook. Please review and you might can use it. I am using the server at work to save the file so I don't use online.
…….

Thanks for the VBA Outlook codes that you have supplied.
I have put the codes here for future reference:
http://www.excelfox.com/forum/showth...0700#post10700
http://www.excelfox.com/forum/showth...0690#post10690
It appears that you are using this sort of code part for your hyperlink

"<A HREF=""file://" & Path & fileName & ".xlsm" & """>Files are saved here</A>"
Or
"<A HREF=""file://" & ActiveWorkbook.FullName & """>Link to the file</A>"

( In my last code I suggested this <A HREF=""https://app.box.com/s/8pm9ciujbshtk8wvicm7vat565lx25s0"">File is here</A> )

You appear to be using the full path to an Excel file as the path for the hyperlink. I do not understand this
As I mentioned in post #24 ( http://www.excelfox.com/forum/showth...0726#post10726 ) , I do not understand how that can work


Possibly, if this does work for you, then it is some unique feature of Outlook. I do not know. I do not understand how this can work.
I do not have Microsoft Outlook. I do not have any knowledge of Microsoft Outlook. I have no experience of Microsoft Outlook.

_.___________________
Do the Outlook VBA codes that you gave me work?
If so, when a Member of your team click the hyperlink, then what happens???

_._____________
You could try changing the in my code, with one of your hyperlinks.
Maybe this will work for you.
In the next 2 test file , ( TestFullHeaderandFullRowsThursday7thJuneLink ExcelFile1.xlsm and TestFullHeaderandFullRowsThursday7thJuneLink ExcelFile2.xlsm , I send to you private) , I have simple changed the
this <A HREF=""https://app.box.com/s/8pm9ciujbshtk8wvicm7vat565lx25s0"">File is here</A>
with
<A HREF=""file://" & ThisWorkbook.FullName & """>File is here</A>"
or
<A HREF=""file:///" & ThisWorkbook.FullName & """>File is here</A>"

When I receive the Email in German Telekom or gmail, and I click on the link, then nothing happens.

So I am not sure how to proceed currently

Alan

[DocAElstein]

Hi Thai
Re Hyperlinks in received Emails…

I have posted some test files to friends of mine and asked them to try out some test codes. ( http://www.excelfox.com/forum/showth...0730#post10730 )

I have also posted 3 files to you using our share g mail account , ExcelVBAExp@gmail.com
Please can you also try out the same test…

Hyperlink in received Email test: Please do the following.

_1) Download all three files , and important: All must be stored in the same Folder.
( the three files are:
Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received Email.htm
Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls
Test File Thai to send EMail containing Hyperlinks to Files.xlsm )

_2) Open only file Test File Thai to send EMail containing Hyperlinks to Files.xlsm
Run code Sub Sendfromexcellearninggmail()

You should receive an Email similar to these:
Alan 5 Links in German Telekom.JPG : https://imgur.com/3qDEYi6
Alan 5 Links in gmail.JPG : https://imgur.com/0sdyZEj

_3) Please click on the links.

_4) Please reply and tell me what happens when you click each link
( _ 4b) Please do this for all 5 links both the
received Email at excellearning12@gmail.com
and also
for the received Email at your work address T____en@o____g.com )

Thanks
Alan

_.________________

( P.s. When I tested the codes, only Link 1 works for me in a received EMail )