Automatic sort due date and send email

Printable View

04-27-2018, 08:19 PM
Thainguyen

1 Attachment(s)

Automatic sort due date and send email

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
05-05-2018, 01:25 PM
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

2 Attachment(s)

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
Attachment 2056

GermanTelekomEMailDueMaintenance.jpg : https://imgur.com/12wvzte
Attachment 2057

_.________________

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

05-22-2018, 02:55 PM
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,

_.____________________

Quote:

Originally Posted by Thainguyen

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

_.__________________________

Quote:

Originally Posted by Thainguyen

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
05-23-2018, 01:36 AM
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
05-23-2018, 01:43 AM
DocAElstein

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

Quote:

Originally Posted by Thainguyen

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
05-23-2018, 03:15 AM
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 = "" & _
"Dear Team, " & _
"Please open the file from below link and put your signature on the respective cell after you completed your task. " & _
fileName & ".xlsm" & " is created. " & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & Path & fileName & ".xlsm" & _
""">Files are saved here</A>" & "-->" & Range("C6").Value & _
" Best Regards," & _
" "

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
05-23-2018, 11:29 AM
DocAElstein

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

Quote:

Originally Posted by Thainguyen

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

Quote:

Originally Posted by Thainguyen

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

Quote:

Originally Posted by Thainguyen

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

Quote:

Originally Posted by Thainguyen

... 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
05-23-2018, 08:53 PM
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.
05-24-2018, 01:53 PM
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:

Quote:

Originally Posted by Thainguyen

… 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
06-01-2018, 03:08 PM
DocAElstein

Hi Thai,

Quote:

Originally Posted by 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
06-01-2018, 06:41 PM
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 = "" & _
"Dear Team, " & _
"Please open the file from below link and change the date on the respective cell after you completed your task. " & _
fileName & ".xlsm" & " is created. " & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & Path & fileName & ".xlsm" & _
""">Files are saved here</A>" & "-->" & Range("A1").Value & _
" Best Regards," & _
" "

With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
06-02-2018, 01:16 PM
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

_.________________________________________________ _________________________

Quote:

Originally Posted by Thainguyen

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)

Code:

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 ) )

_.________________________________________________ ___________________________________

Quote:

Originally Posted by Thainguyen

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

_Row\^Col	J	K
19	N/A	N/A
20	15.02.2018	15.03.2018
21	N/A	N/A

_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

1 Attachment(s)

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:

Code:

' 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:

Code:

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:

Code:

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:

Code:

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
Attachment 2065
If you wish to test this code on a day other than Thursday, 7th June, then add this code line:

Code:

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

06-05-2018, 09:06 PM
Thainguyen

1 Attachment(s)

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.
Attachment 2061

Again, Thank you for your time and effort to help out on this. I mean alot to me.
06-06-2018, 01:20 PM
DocAElstein

1 Attachment(s)

Hello Thai,

Quote:

Originally Posted by Thainguyen

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.

_.__________________________________

Quote:

Originally Posted by Thainguyen

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
Attachment 2062
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.

-.________________________________

Quote:

Originally Posted by Thainguyen

…
_ (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
06-13-2018, 05:27 PM
DocAElstein

1 Attachment(s)

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
Attachment 2069

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
06-13-2018, 08:04 PM
Thainguyen

1 Attachment(s)

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.
Attachment 2070
06-17-2018, 03:47 PM
DocAElstein

Hi Thai

Quote:

Originally Posted by Thainguyen

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.

_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

_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

_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

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

06-18-2018, 10:57 PM
Thainguyen

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

Best Regards,
Thai
06-20-2018, 02:56 PM
DocAElstein

Hi Thai

Quote:

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

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,
06-22-2018, 03:26 PM
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
06-25-2018, 12:26 AM
DocAElstein

Hi Thai,

Quote:

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 :confused:
As I mentioned in post #24 ( http://www.excelfox.com/forum/showth...0726#post10726 ) , I do not understand how that can work :confused:

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

Hyperlink in received EMail Testing

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 )
01-22-2019, 02:52 PM
klimbo123

Quote:

Originally Posted by DocAElstein

......

thanks to your comment and your links that you give us I Bluestacks Kodi Lucky Patcher was able to find the solution to my problem
thanks bro
01-22-2019, 03:17 PM
DocAElstein

Quote:

Originally Posted by klimbo123

thanks to your comment and your links that you give us I was able to find the solution to my problem
thanks bro

Yous welcome,
Thanks for the feedback
Alan
:)