Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

[DocAElstein]

Page 4
https://www.excelfox.com/forum/showt...PI-stuff/page4
https://www.excelfox.com/forum/showt...ll=1#post17896
https://www.excelfox.com/forum/showt...age3#post24934
#post17896 , Thread2824 , Post 31

---

Some notes related loosely to these forum posts
https://eileenslounge.com/viewtopic....322955#p322955 https://eileenslounge.com/viewtopic....323065#p323065
https://eileenslounge.com/viewtopic.php?f=30&t=41659

https://www.excelfox.com/forum/showt...dows-API/page3
https://www.excelfox.com/forum/showt...ll=1#post24934
https://www.excelfox.com/forum/showt...age3#post24934
post24934, Thread2989

Theory Ælstein
This is not necessarily a final answer, ( an unlikely to be the final word on the issue ), but this is an attempt to get the thing out, and keep it simmering while I take a break to write my Xmas cards. I have only looked in depth at less than a third of the book chapter

API App message box renewed experiments
Because I am new and still very flaky with API stuff, and because whilst on Panzer Schokolade a few years back, I got to know this API MessageboxAA very well, almost as well probably as a computer professional, exceptionally for Layman me, and as part of my recent API revision I revisited there.
So let me see if I can apply some of my initial findings and solution/ Theory suggestions to that.
In my research and experiments I went off in a tangent into very basic Unicode and ANSI/Ascii associated with VB strings and windoew32 API. In the long run, that may not be such a bad thing. The detailed discussions will be continued in more detail in this page 2 ++

Some (Vague for now) thoughts / Clues gleaned from a week of research
In no particular order, yet….
( These thoughts are given in more detail and better ordered in this page 2 ++ But it’s not finished yet. I have only looked in depth at less than a third of that book chapter
_ Things windows API certainly where and in many ways still are, or efficiently use, ANSI. This does cause issues with String things
_ The issues have been known about for over 20 years and we have things like strPtr extra made to help us, along with things like LongPtr which compliment it in some ways
_ a VBA string variable is a pointer to a VB pointer which final points to some form or part of the final string you actually want. Pointing to a pointer is possibly not so bad, whereas mixing up a pointer with a string is more deadly.
_** At some point in that book chapter , the author is hinting that his attempts to explain what is going on with existing VB documentation are his ideas and that other smart people were thinking something extra might be going on,
_ Somewhat less known as it perhaps should be, … for almost, if not all, API functions/ Sub routines, there is two versions, denoted by the last character in the name of A and W. Very briefly W can be thought of as meaning Unicode, A as ANSI/Ascii. The W things are not so new….
_ I noticed some strange things with types and type documentation. When considering string arguments, they seem to be wording things sometimes as if they are not talking about the string, but the pointer. I got some strange feeling that talking pointers is more low level, or more fundamental
_ I notice some things giving me the string problem issues were actually, (not always), sometimes in documentation or in working API stuff, declared As Any when String was either more intuitive or more often used.

My brain went off in some lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment.



What did I decide to look at, and why
_ (For one thing the computer Unicode ANSI/Ascii very basics and History thereof. But maybe I may not have needed that if I was not so doubly ignorant in this area….)
_ I decided to look in strings, and string types, and string related pointer things.
_ I went off in an absurd diverse direction, thinking to get a way to do something involving a string by avoiding the string.


** As I went on, I started thinking that this original throw away thought of mine was not so bad after all …
…....... the simple answer is that ByVal and ByRef behaviour in windows API Declare lines is different, at least with As String.. ….
.... no one could figure out a more ordered logical well organised interface so the nearest looking combination of Declare line, call line and associated coding is used as an enigma type coding that sets off some particular back end coding to do something….
As I went on, I came to an idea, which with hindsight later searching, some people, al be it very few, but computer Profis never the less, also thought…. As soon as VBA sees a Declare line, it decides to do some Unicode to ANSI transformations, in particular when it sees a string argument. I am thinking I don’t want that.**
For one thing, Excel spreadsheets and word documents, I have noticed seem very good with many exotic characters and languages. Even my preferred earlier Office versions.
I think I, (and likely some others, possibly even some Microsoft people ) , may have been mislead sometimes in code development by apparent failings of passing characters due to failures in the VB Editor, and possibly then even overlooked that we have some (less commonly known and less commonly used) in most cases an API function version that it is regarded as a Unicode version.
I want to see if I can get a more Unicode API thing, and perhaps try at the same time to stop VBA messing with things so much…..My brain started one of its lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment. This time it may have got something interesting and useful. Maybe not
This is just a provisional initial theory:
_ Behold: I have seen a API World, where direct ANSI Strings stuff f is often avoided, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)

Having done many empirical measurements, I may have found a way to do Unicode API things, and that may to soime extent explain issues which initiated this Thread https://eileenslounge.com/viewtopic.php?f=30&t=41659


Here we go for now (and a lot to be added to and edited later

MessageBoxÆ function https://www.excelfox.com/forum/showt...ge4#post417897

[DocAElstein]

Page 4
https://www.excelfox.com/forum/showt...PI-stuff/page4
https://www.excelfox.com/forum/showt...ll=1#post17896
https://www.excelfox.com/forum/showt...age3#post24934
#post17896 , Thread2824 , Post 31

---

Some notes related loosely to these forum posts
https://eileenslounge.com/viewtopic....322955#p322955 https://eileenslounge.com/viewtopic....323065#p323065
https://eileenslounge.com/viewtopic.php?f=30&t=41659

https://www.excelfox.com/forum/showt...dows-API/page3
https://www.excelfox.com/forum/showt...ll=1#post24934
https://www.excelfox.com/forum/showt...age3#post24934
post24934, Thread2989

Theory Ælstein
This is not necessarily a final answer, ( an unlikely to be the final word on the issue ), but this is an attempt to get the thing out, and keep it simmering while I take a break to write my Xmas cards. I have only looked in depth at less than a third of the book chapter

API App message box renewed experiments
Because I am new and still very flaky with API stuff, and because whilst on Panzer Schokolade a few years back, I got to know this API MessageboxAA very well, almost as well probably as a computer professional, exceptionally for Layman me, and as part of my recent API revision I revisited there.
So let me see if I can apply some of my initial findings and solution/ Theory suggestions to that.
In my research and experiments I went off in a tangent into very basic Unicode and ANSI/Ascii associated with VB strings and windoew32 API. In the long run, that may not be such a bad thing. The detailed discussions will be continued in more detail in this page 2 ++

Some (Vague for now) thoughts / Clues gleaned from a week of research
In no particular order, yet….
( These thoughts are given in more detail and better ordered in this page 2 ++ But it’s not finished yet. I have only looked in depth at less than a third of that book chapter
_ Things windows API certainly where and in many ways still are, or efficiently use, ANSI. This does cause issues with String things
_ The issues have been known about for over 20 years and we have things like strPtr extra made to help us, along with things like LongPtr which compliment it in some ways
_ a VBA string variable is a pointer to a VB pointer which final points to some form or part of the final string you actually want. Pointing to a pointer is possibly not so bad, whereas mixing up a pointer with a string is more deadly.
_** At some point in that book chapter , the author is hinting that his attempts to explain what is going on with existing VB documentation are his ideas and that other smart people were thinking something extra might be going on,
_ Somewhat less known as it perhaps should be, … for almost, if not all, API functions/ Sub routines, there is two versions, denoted by the last character in the name of A and W. Very briefly W can be thought of as meaning Unicode, A as ANSI/Ascii. The W things are not so new….
_ I noticed some strange things with types and type documentation. When considering string arguments, they seem to be wording things sometimes as if they are not talking about the string, but the pointer. I got some strange feeling that talking pointers is more low level, or more fundamental
_ I notice some things giving me the string problem issues were actually, (not always), sometimes in documentation or in working API stuff, declared As Any when String was either more intuitive or more often used.

My brain went off in some lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment.



What did I decide to look at, and why
_ (For one thing the computer Unicode ANSI/Ascii very basics and History thereof. But maybe I may not have needed that if I was not so doubly ignorant in this area….)
_ I decided to look in strings, and string types, and string related pointer things.
_ I went off in an absurd diverse direction, thinking to get a way to do something involving a string by avoiding the string.


** As I went on, I started thinking that this original throw away thought of mine was not so bad after all …
…....... the simple answer is that ByVal and ByRef behaviour in windows API Declare lines is different, at least with As String.. ….
.... no one could figure out a more ordered logical well organised interface so the nearest looking combination of Declare line, call line and associated coding is used as an enigma type coding that sets off some particular back end coding to do something….
As I went on, I came to an idea, which with hindsight later searching, some people, al be it very few, but computer Profis never the less, also thought…. As soon as VBA sees a Declare line, it decides to do some Unicode to ANSI transformations, in particular when it sees a string argument. I am thinking I don’t want that.**
For one thing, Excel spreadsheets and word documents, I have noticed seem very good with many exotic characters and languages. Even my preferred earlier Office versions.
I think I, (and likely some others, possibly even some Microsoft people ) , may have been mislead sometimes in code development by apparent failings of passing characters due to failures in the VB Editor, and possibly then even overlooked that we have some (less commonly known and less commonly used) in most cases an API function version that it is regarded as a Unicode version.
I want to see if I can get a more Unicode API thing, and perhaps try at the same time to stop VBA messing with things so much…..My brain started one of its lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment. This time it may have got something interesting and useful. Maybe not
This is just a provisional initial theory:
_ Behold: I have seen a API World, where direct ANSI Strings stuff f is often avoided, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)

Having done many empirical measurements, I may have found a way to do Unicode API things, and that may to soime extent explain issues which initiated this Thread https://eileenslounge.com/viewtopic.php?f=30&t=41659


Here we go for now (and a lot to be added to and edited later

MessageBoxÆ function https://www.excelfox.com/forum/showt...ge4#post417897

[DocAElstein]

MessageBoxÆ function

The standard/ old Declareation looks like this
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
In this case disappointingly, the string arguments are given As String
However, take a look at the moderner documentation. ( That is usually in C language , but translating them is usually straight forward. )
int MessageBoxA(
[in, optional] HWND hWnd,
[in, optional] LPCSTR lpText,
[in, optional] LPCSTR lpCaption,
[in] UINT uType
);
int MessageBoxW(
[in, optional] HWND hWnd,
[in, optional] LPCWSTR lpText,
[in, optional] LPCWSTR lpCaption,
[in] UINT uType
);
Encouraging we see the pointer stuff is there on the two string arguments.
So
We is trying to get something that very likely does a Unicode thing, as much as possible. That is to say the provisional Ælstein Theory proclamates something like:
_ Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)

Worked example
I got an interesting word, or rather interesting first character of a word, in the first cell of a worksheet
Whilst Excel seems very good with displaying a massive amount of exotic high up the Unicode list characters, initially both the inbuilt VBA MsgBox and initially the API MessageBoxA don’t do at all well, not even if it's an ANSI that is probably not on my code page .


later.....

[DocAElstein]

MessageBoxÆ function

The standard/ old Declareation looks like this
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
In this case disappointingly, the string arguments are given As String
However, take a look at the moderner documentation. ( That is usually in C language , but translating them is usually straight forward. )
int MessageBoxA(
[in, optional] HWND hWnd,
[in, optional] LPCSTR lpText,
[in, optional] LPCSTR lpCaption,
[in] UINT uType
);
int MessageBoxW(
[in, optional] HWND hWnd,
[in, optional] LPCWSTR lpText,
[in, optional] LPCWSTR lpCaption,
[in] UINT uType
);
Encouraging we see the pointer stuff is there on the two string arguments.
So
We is trying to get something that very likely does a Unicode thing, as much as possible. That is to say the provisional Ælstein Theory proclamates something like:
_ Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)

Worked example
I got an interesting word, or rather interesting first character of a word, in the first cell of a worksheet
Whilst Excel seems very good with displaying a massive amount of exotic high up the Unicode list characters, initially both the inbuilt VBA MsgBox and initially the API MessageBoxA don’t do at all well, not even if it's an ANSI that is probably not on my code page .


later.....

[DocAElstein]

To support this Thread
http://www.excelfox.com/forum/showth...0679#post10679

Re post code in Code tags, Like ....

Please use CODE TAGS if you are writing codes in your post.

To use code tags,
either
select your entire code and press the code tag button # in the editor below,
or
simply type your code as below

[Code]Your Code Here[/Code]

[Code]
Your Code Here
[/Code]




[Code]
Private Sub cmdNot_Click()

Dim OutApp As Object
Dim OutMail As Object

…………………….

……………..

End Sub
[/Code]




BBCodeCodeTags.JPG : https://imgur.com/4HunNcs
Attachment 2060

_.__________________

If you post using Code tags, then it will come out in the final post in a Code Window, like this:

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

    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

[DocAElstein]

To support this Thread
http://www.excelfox.com/forum/showth...0679#post10679

Re post code in Code tags, Like ....

Please use CODE TAGS if you are writing codes in your post.

To use code tags,
either
select your entire code and press the code tag button # in the editor below,
or
simply type your code as below

[Code]Your Code Here[/Code]

[Code]
Your Code Here
[/Code]




[Code]
Private Sub cmdNot_Click()

Dim OutApp As Object
Dim OutMail As Object

…………………….

……………..

End Sub
[/Code]




BBCodeCodeTags.JPG : https://imgur.com/4HunNcs
Attachment 2060

_.__________________

If you post using Code tags, then it will come out in the final post in a Code Window, like this:

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

    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

[DocAElstein]

Code in code tags from here:
http://www.excelfox.com/forum/showth...0699#post10699

Dim OutApp As Object
Dim OutMail As Object
Dim fileName As String
Dim mSubject As String
Dim signature As String
Dim fname As String
Dim mBody As String
Dim rng As Range
Dim rng1 As Range
Dim ws As Worksheet
Dim mailTo As String
 fname = ws.Range("A1")
 mSubject = "Equipment" & " For " & Range("A1").Value
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 'mBody = "Z:\2\Form\\Manufacturing Order.xlsm"

Dim Path As String
 ws.Protect ("Equipment")
 Path = "\\Equipment- Maint RecordsThai1.xlsm"
 mBody = "<font size=""3"" face=""Calibri"">" & _
   "Dear Team,<br><br>" & _
   "Please open the file from below link and change the date on the respective cell after you completed your task.<br><B>" & _
   fileName & ".xlsm" & "</B> is created.<br>" & _
   "Click on this link to open the file : " & _
   "<A HREF=""file://" & Path & fileName & ".xlsm" & _
   """>Files are saved here</A>" & "-->" & Range("A1").Value & _
   "<br><br>Best Regards," & _
   "<br><br></font>"

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

Private Sub cmdNot_Click()
    If Application.UserName = "Thai Nguyen" Then
    Dim ws As Worksheet: Set ws = Sheets("Name")
    Dim rng As Range, rng1 As Range
    Dim fileName As String, fname As String
     Let fname = ws.Range("B4")
     Let mSubject = "Name"
    Dim OutApp As Object, OutMail As Object
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
    Dim Subject As String, signature As String, mBody As String, mailTo As String
        'mBody = "copy you link path in here"
     Let mBody = "<font size=""3"" face=""Calibri"">" & _
     "Hi Team,<br><br>" & _
     "Please open the file from below link and put your signature on the respective cell after you completed your task.<br><B>" & _
     ActiveWorkbook.Name & "</B> is created.<br>" & _
     "Click on this link to open the file : " & _
     "<A HREF=""file://" & ActiveWorkbook.FullName & """>Link to the file</A>" & _
     "<br><br>Regards," & _
     "<br><br>Thai Nguyen</font>    "
     OutMail.display
     Let signature = OutMail.body
        With Application
         .EnableEvents = False
         .ScreenUpdating = False
        End With
        With OutMail
        '.To = "email"
            If ws.Range("EU16") = True Then
             Let mailTo = mailTo + "Thai Nguyen;"
            Else
            End If
            If ws.Range("EU17") = True Then
            mailTo = mailTo + "email"
            End If
            If ws.Range("EU18") = True Then
             Let mailTo = mailTo + "email"
            End If
            If ws.Range("EU19") = True Then
             Let mailTo = mailTo + "email"
            End If
         .To = mailTo
         .CC = "Thai Nguyen"
         .BCC = ""
         .Subject = mSubject
         '.body = "Hi 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")
     ws.Protect ("Name")
     ActiveWorkbook.Save
     ActiveWorkbook.Close
     On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        With Application
         .ScreenUpdating = True
         .EnableEvents = True
        End With
    Else
     MsgBox "You are not authorised to send BOM form, please check with BOM owner"
    End If
End Sub

[DocAElstein]

Code in code tags from here:
http://www.excelfox.com/forum/showth...0699#post10699

Dim OutApp As Object
Dim OutMail As Object
Dim fileName As String
Dim mSubject As String
Dim signature As String
Dim fname As String
Dim mBody As String
Dim rng As Range
Dim rng1 As Range
Dim ws As Worksheet
Dim mailTo As String
 fname = ws.Range("A1")
 mSubject = "Equipment" & " For " & Range("A1").Value
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 'mBody = "Z:\2\Form\\Manufacturing Order.xlsm"

Dim Path As String
 ws.Protect ("Equipment")
 Path = "\\Equipment- Maint RecordsThai1.xlsm"
 mBody = "<font size=""3"" face=""Calibri"">" & _
   "Dear Team,<br><br>" & _
   "Please open the file from below link and change the date on the respective cell after you completed your task.<br><B>" & _
   fileName & ".xlsm" & "</B> is created.<br>" & _
   "Click on this link to open the file : " & _
   "<A HREF=""file://" & Path & fileName & ".xlsm" & _
   """>Files are saved here</A>" & "-->" & Range("A1").Value & _
   "<br><br>Best Regards," & _
   "<br><br></font>"

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

Private Sub cmdNot_Click()
    If Application.UserName = "Thai Nguyen" Then
    Dim ws As Worksheet: Set ws = Sheets("Name")
    Dim rng As Range, rng1 As Range
    Dim fileName As String, fname As String
     Let fname = ws.Range("B4")
     Let mSubject = "Name"
    Dim OutApp As Object, OutMail As Object
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
    Dim Subject As String, signature As String, mBody As String, mailTo As String
        'mBody = "copy you link path in here"
     Let mBody = "<font size=""3"" face=""Calibri"">" & _
     "Hi Team,<br><br>" & _
     "Please open the file from below link and put your signature on the respective cell after you completed your task.<br><B>" & _
     ActiveWorkbook.Name & "</B> is created.<br>" & _
     "Click on this link to open the file : " & _
     "<A HREF=""file://" & ActiveWorkbook.FullName & """>Link to the file</A>" & _
     "<br><br>Regards," & _
     "<br><br>Thai Nguyen</font>    "
     OutMail.display
     Let signature = OutMail.body
        With Application
         .EnableEvents = False
         .ScreenUpdating = False
        End With
        With OutMail
        '.To = "email"
            If ws.Range("EU16") = True Then
             Let mailTo = mailTo + "Thai Nguyen;"
            Else
            End If
            If ws.Range("EU17") = True Then
            mailTo = mailTo + "email"
            End If
            If ws.Range("EU18") = True Then
             Let mailTo = mailTo + "email"
            End If
            If ws.Range("EU19") = True Then
             Let mailTo = mailTo + "email"
            End If
         .To = mailTo
         .CC = "Thai Nguyen"
         .BCC = ""
         .Subject = mSubject
         '.body = "Hi 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")
     ws.Protect ("Name")
     ActiveWorkbook.Save
     ActiveWorkbook.Close
     On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
        With Application
         .ScreenUpdating = True
         .EnableEvents = True
        End With
    Else
     MsgBox "You are not authorised to send BOM form, please check with BOM owner"
    End If
End Sub

[DocAElstein]

Share account for testing file access from a hyperlink in a received EMail

In support of a possible solution to this post in this Thread:
http://www.excelfox.com/forum/showth...0724#post10724

It is required to have a simple hyperlink to an Excel File appear in the received Email sent to members of a team.
I am not sure currently how to get a link directly to the File.

An second alternative involves storing the file at a File sharing site and using the link to the file as the URL part of a hyperlink.

This post discusses the setting up of such an account to allow storing of, and sharing via a supplied link to, the file.

As an example of a file sharing site we consider the free version of box.net
Some googling my be needed to finally get at the free version which may go under the name of “free” , “Individual rate”, “Personal free”
Currently you need to find your way to the free 10GB offer. This is currently at this link:
https://account.box.com/signup/n/personal#fbms6
Free10GB box net account register.JPG : https://imgur.com/NB3GThi
Note , by registering, you can choose a language to suit you.
Free10GB Select language .JPG : : https://imgur.com/aNzW1kq
( You can change the language to a different one after registering also
Free10GB Change language .JPG : https://imgur.com/IosqbAI )


For this registering , I use the created gmail account used for experiments in the current thread which this post supports, excellearning12@gmail.com ( excelfox Thread : http://www.excelfox.com/forum/showth...and-send-email )

The password I pass on privately to those needing
Free10GB box net account register 2.JPG : https://imgur.com/Y2pLogO
Free10GB box net account register 3.JPG : https://imgur.com/QhCR8fP
Free10GB box net account register Verify Email 4.JPG : https://imgur.com/ffG7erw

Various steps are then gone through, they may be slightly different to the following:

At some point you should you should see the possibility to upload a file, following steps similar to these:
Free10GB box net 5 .JPG : https://imgur.com/lNWvQwF
To upload a file and get a URL link to use in a hyperlink to it:
Upload Files:
Free10GB box net 6 .JPG : https://imgur.com/rTU1Xbk
Select a file:
Free10GB box net 7 .JPG : https://imgur.com/wKKlqoO
Select share to obtain a URL link to the file :
Free10GB box net 8 .JPG : https://imgur.com/R3VbyhR
Copy link to be used in Hyperlink :
Free10GB box net 9 .JPG : https://imgur.com/8yaYwaK

[DocAElstein]

Share account for testing file access from a hyperlink in a received EMail

In support of a possible solution to this post in this Thread:
http://www.excelfox.com/forum/showth...0724#post10724

It is required to have a simple hyperlink to an Excel File appear in the received Email sent to members of a team.
I am not sure currently how to get a link directly to the File.

An second alternative involves storing the file at a File sharing site and using the link to the file as the URL part of a hyperlink.

This post discusses the setting up of such an account to allow storing of, and sharing via a supplied link to, the file.

As an example of a file sharing site we consider the free version of box.net
Some googling my be needed to finally get at the free version which may go under the name of “free” , “Individual rate”, “Personal free”
Currently you need to find your way to the free 10GB offer. This is currently at this link:
https://account.box.com/signup/n/personal#fbms6
Free10GB box net account register.JPG : https://imgur.com/NB3GThi
Note , by registering, you can choose a language to suit you.
Free10GB Select language .JPG : : https://imgur.com/aNzW1kq
( You can change the language to a different one after registering also
Free10GB Change language .JPG : https://imgur.com/IosqbAI )


For this registering , I use the created gmail account used for experiments in the current thread which this post supports, excellearning12@gmail.com ( excelfox Thread : http://www.excelfox.com/forum/showth...and-send-email )

The password I pass on privately to those needing
Free10GB box net account register 2.JPG : https://imgur.com/Y2pLogO
Free10GB box net account register 3.JPG : https://imgur.com/QhCR8fP
Free10GB box net account register Verify Email 4.JPG : https://imgur.com/ffG7erw

Various steps are then gone through, they may be slightly different to the following:

At some point you should you should see the possibility to upload a file, following steps similar to these:
Free10GB box net 5 .JPG : https://imgur.com/lNWvQwF
To upload a file and get a URL link to use in a hyperlink to it:
Upload Files:
Free10GB box net 6 .JPG : https://imgur.com/rTU1Xbk
Select a file:
Free10GB box net 7 .JPG : https://imgur.com/wKKlqoO
Select share to obtain a URL link to the file :
Free10GB box net 8 .JPG : https://imgur.com/R3VbyhR
Copy link to be used in Hyperlink :
Free10GB box net 9 .JPG : https://imgur.com/8yaYwaK