Automatic sort due date and send email

[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