Automatic sort due date and send email

**Thainguyen** · 06-18-2018, 10:57 PM

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

Best Regards,
Thai

**DocAElstein** · 06-20-2018, 02:56 PM

Hi Thai

Originally Posted by Thainguyen

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:

Code:

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 _ ..

Code:

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

_.. to something like this:

Code:

            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** · 06-20-2018, 09:40 PM

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.

Code:

.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** · 06-22-2018, 03:26 PM

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** · 06-25-2018, 12:26 AM

Hi Thai,

Originally Posted by Thainguyen

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** · 07-01-2018, 03:00 PM

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 )