Page 4 of 6 FirstFirst ... 23456 LastLast
Results 31 to 40 of 51

Thread: ब्लॉग कोशिश कर रहा है بلاگز کی ک*Trying Blogs

  1. #31
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    So.
    This whole help file business looks like a very complicated way to do something very simple. Not surprisingly it seems to go wrong a lot and fixing it when it breaks needs a lot of computer programming knowledge.
    Maybe I should need to word that differently. Hang on, .. __ let me take a quick swig of my Microsoft 'Kool Aid' __ then I will begin.. _...
    If I may, just to avoid confusion. I will use the term “Bugg y feature” to refer to what rational thinking says is a Bug and what when drugged up on Microsoft 'Kool Aid' appears as a neat feature.

    Microsoft Help Text aka “Help” “Feature” and “Buggery features
    Aka , as applied to this Thread..
    What is HelpFile:= HelpContextID:= about

    It is impossible for me ( or anyone I expect ) to guarantee to walk you through a working demo of getting the “Pop up” box optional argument parameters of HelpFile:= HelpContextID:= to do what ever it is that they are supposed to do.
    The reason for this is that the “Buggering Features” are too many and varied.
    Anyway_....Microsoft have a “Help” system, ( Sometimes referred to loosely as “Help” ). Crudely this could be considered as an alternative to writing some explanatory notes in a media such as a text file, or Word document or as in a supplied user manual.
    It has the interesting “feature” that in order to master it and use it you will become, or need to be, at a fairly confident level or programming and general computer knowledge. You may need constant access through the internet to get help and advise onto how to maintain the system , that is to say make it continually work, as changes , such as Microsoft updates, will result on additional things needing to be done as time goes on. One of the major “feature” therefore is its Volatility…. And / or it often does not work
    A useful tip I have learnt when looking for general information on this Microsoft help through the internet is to include extra terms such as “problem” or “not working”. Without these terms it proved almost impossible to get ay information on the Theme, but with those terms, a great deal of information was obtained allowing for possibility of using up many hours of time in the many interesting actions that may likely to be needed in order to make the Help work, and or repair the damage done to many other things in the initial attempts. These include adding and removing security updates, and manipulating registries and the such both manually and with computer programs calling up many API codes.

    Here we go,
    Help text Window Feature and “Buggary features”
    Most things “Help” or of the help nature in Microsoft uses similar looking Windows. When, for example you is in the VB Editor ( Alt+F11 from Excel spreadsheet ) Window looking at code, you can highlight a word in the code, hit F1, and then such a Help Window comes up
    HelpApplicationHelp.JPG : https://imgur.com/ZAXUHmW
    So there could be some thinking behind the “Microsoft Help” as a (bad) attempt to bring it all together in some organised way.
    .chm Files ( getting them to work/open)
    These are the files used as the “raw data” in whatever hidden software takes these files and uses them in some process to get those help windows up. They contain some sort of compressed data of coding written in some variation of the HTML language.
    It is likely you may have some .chm files, as the Microsoft Help Text aka “Help” is likely have been used / integrated into some software that you may have on your computer
    You may find some ( in Vista and Win 7 ) if you click on the bottom left Microsoft sign and then type in _ chm _ in the small search box.
    Here are some also that I made or found earlier https://app.box.com/s/bx2pkvtemsppscz60rd6f430wm89c6fj https://app.box.com/s/varm2k1ojlwwtemnefuhhnym1d84bcxs

    It is impossible for me to guarantee to walk you through a working demo of getting the HelpFile:= HelpContextID:= to do what ever it is that they are supposed to do. One of the “Buggering Features” that may prevent getting a successful use of Help is that security updates from Microsoft may intentionally or unintentionally prevent a .chm file from operating as initially intended.
    Solutions to this problem range from one extreme to the other.
    _ One extreme is major heart surgery to registry files, along with a possible need for application of additional software.
    or
    _ If you are lucky some of the following may be all you need to ensure that a .chm file will work as it should:
    Try out a .chm first( sometimes you need to have done this to make other workarounds work fter you find out that it doesn’t work )
    After finding or downloading such a file, navigate to it in its folder , and make a note of its path and file name. ( In the Microsoft Explorer window you may be able to copy the path to the clipboard
    chmFolderPath.JPG : https://imgur.com/5Si2HIq )
    So for that example shown you need to make a note of the following path and file name ( remember the extra \between the two ) :
    G:\Excel0202015Jan2016\ExcelForum\UserForm\samplehelp.chm _ ‘This is the sort of Help File string that you will need
    Now try either of the following:
    _(i) Double click on the file in the Window Explorer window
    or
    _(ii) Run this code ( change the Help File string to suit what you have as a .chm File )
    Code:
    Sub HelpGetItUp()
     Application.Help HelpFile:="G:\Excel0202015Jan2016\ExcelForum\UserForm\samplehelp.chm"
    End Sub
    As a result of doing either of those a Help Window Thingy may or may not come up. If it does come up, it may give an error , that is to say something in the largest window to the effect of one or more things. Here a couple, with workarounds:

    Buggery Internet Error Feature 1:

    i Navigation to the webpage was canceled.
    __ What you can try:
    __ .- Retype the address.

    Workaround:
    If that error is obtained it may mean that some form of blocking has been done by some sort of security measure. If this has happened an extra option will now be seen towards the bottom of the general settings in the Properties Window which you see by right clicking on the file in the Windows Explorer Window and selecting _ Properties
    chmUnblock.JPG : https://imgur.com/ObMrbOy
    Hit _ Unblock.
    After doing this, the (i) or (ii) should result in a normal looking Help type Window.
    Note:
    _ It may initially be necessary to make the attempt of the (i) or (ii) , or otherwise you may not see the option _ Unblock.
    _ Strangely it is sometimes necessary to have done the above workaround in order to avoid other problems later. So it is sometimes advisable to try and get this Buggery Internet Error Feature. One way to achieve this is to upload and then download the .chm file to a trusted file sharing site such as box.net ( https://account.box.com/signup/n/personal#rw1w0 )

    Buggery Internet Error Feature 2:
    A similar “Internet Error” window may show:
    ThisProgramCanNotDisplayTheWebPage.JPG https://imgur.com/NduujoY
    i This Program Can Not Display The Web Page

    Workaraound: WARNING: Do this at your risk. You are messing with dangerous stuff
    via left bottom Microsoft symbol_ regedit.exe_ HKEY_LOCAL_MACHINE
    regeditHKEY_LOCAL_MACHINE.JPG https://imgur.com/MHAggqH
    _ SOFTWARE --- Microsoft
    SOFTWAREMicrosoft.JPG https://imgur.com/n5TnD1b
    HTML --- 1.x --- right click mouse _ Neu --- Key
    HTMLHelp1_xNewKey.JPG https://imgur.com/z7QnvCV
    Name the new folder that appears as _ ItssRestrictions
    ItssRestrictions.JPG https://imgur.com/nDyE9tL
    right click mouse _ Neu --- DWORD (32-bit) Value
    Name the new folder that appears as _ MaxAllowedZone
    Double click on that to get an edit Window
    EditDWORD32bitValue.JPG https://imgur.com/07AAqu1
    Give a value of _ 4

    Refresh your computer somehow
    (**** Edit: 25Jan 2018: .. another possible Registry modification reported, but have not tried it as have not needed it yet )

    3_ Buggery Features 3
    See next post for another way to get over the “Help” thing usually not working

    _.________________
    Back not to the original Thread question:

    _ Application.InputBox(Prompt:= , Title:= , Default:= , Left:= , Top:=, HelpFile:= , HelpContextID:= , Type:= ) Method
    Coming back to the Theme of this Thread.
    As noted , to avoid the extra complication for now of the “Application.InputBox Buggery features” , I am considering a slightly different thing
    __ InputBox(Prompt:= , Title:= , Default:= , Left:= , Top:=, HelpFile:= , Context:= )
    __( Note the slight syntax difference between Context:= and HelpContextID:= ) Function

    One main part of the Thread question ( what are those two options supposed to do ) can now be [Solved]:
    Two possible ways to “launch” a .chm help file where discussed ( (i) and (ii) )
    A third way (iii) is possible.:- Many of the “Pop up User Info getting / giving Window” stuff have the option to allow a button to “launch such a file” …
    Typically the Options are like
    The main option:
    _ HelpFile:= _________ --- _______ The full path and file name of the .chm file
    A second option:
    _ HelpContextID:= or Context:= _ --- _A number that takes you to a particular part or section of the main text

    _ For InputBox _ it appears the Context:= is not optional.
    _ For Applcation.InputBox and for Application.Help _ it appears that HelpContextID:= is optional. (But it is not too clear with the case of Applcation.InputBox due to the additional “ Application.InputBox Buggery features”)
    _ For InputBox it appears that no Button for help must be specified: it will appear if HelpFile:= and Context:= are given. ( There is no option to add buttons anyway here )
    _ For MsgBox the button option (buttons:=vbMsgBoxHelpButton ) must be given or the Help Button will not appear. ( But if the (buttons:=vbMsgBoxHelpButton is not given and the helpfile:= and context:= are given and valid, then the code line will not error )
    _ For Applcation.InputBox it would appear that one of the “Buggery features” is that no Help Button comes up. ( But then..Nothing to do with the Help seems to work for Excel 2007+ with Applcation.InputBox _ – That is a “feature” of it .. “Bugger me feature


    The next post will attempt to make a .chm file. But will also have to tackle possible further “Buggery features


    Ref:=
    InputBox ( Prompt:= , title:= , default:= , xpos:= , ypos:= , helpfile:= , context:= ) ) ' If helpfile is provided, context must also be provided. Button comes then automatically.
    MsgBox ( prompt:= , buttons:= , title:= , helpfile:= , context:= ) ' If helpfile is provided, context must also be provided . _ For the required Button buttons:=vbMsgBoxHelpButton must be given
    Application.InputBox ( Prompt:= , Title:= , Default:= , Left:= , Top:= , HelpFile:= , HelpContextID:= , Type:= ) ' “Buggery Features”: In Excel 2007 + Left:= , Top:= , HelpFile:= , HelpContextID:= can be anything and are ignored
    Application.Help HelpFile:= , HelpContextID:= ' , HelpContextID:= is optional

    https://stackoverflow.com/questions/...e-was-canceled
    https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx
    https://www.youtube.com/watch?v=1gdGpnCCFWM
    **** http://www.winfaq.de/faq_html/Conten...?h=tip2026.htm
    https://powerspreadsheets.com/excel-vba-inputbox/

    Last edited by DocAElstein; 02-28-2018 at 02:28 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #32
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Create a Help Project. Aka make a .chm file that probably won't work
    Part 2 A walkthrough example
    Part 2a) Getting a .chm file that might come up by double clicking on it or via Application.Help HelpFile:=MyCodeX.chm

    _3_ Buggery Features 3 / Workaround ( To understanding WTF Microsoft HTML Help Workshop is ) :
    This is just one way that on average lead to the most success of getting a final working .chm
    Instead of starting from scratch, I found I had a better chance of producing a final .chm if I started with one that worked and went backwards.
    Unfortunately this is characteristic of many things Microsoft. You need to have a working answer to or a working solution to get there ? A "chicken and egg" situation. So I have done my best to give a working solution on which to build / modify.

    In a way I am not really using the Microsoft HTML Help Workshop as it is intended. I guess the Idea is that you can make , edit and organise all the files you need to create a final .chm with the Microsoft HTML Help Workshop Development Environment. Sounds simple but I could not find any decent documentation or learning material. In most of the You tube videos the author seemed confused and surprised half the time as to what was actually going on


    To start with you need to have a working .chm.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #33
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Create a Help Project. Aka make a .chm file that probably won’t work
    Part 2 A walkthrough example
    Part 2b) Getting a .chm file that might launch from the help argument options in InputBox Function

    _4_ Buggery Feature Context-Sensitive Help ( InputBox Function )
    A special form Of Cursor Lock:= was used when the Microsoft data base xxxxs developed a few things:
    Cursor Lock:= Load of dis coordinated Bollox aka Another mess and it does not work ever not even from the start but don’t tell anyone maybe they won’t notice.

    From the last post we see that for _ InputBox Function ' If helpfile:= is given , then context must also be provided. ( This is not the case for Application.Help, - the HelpContextID can be omitted. )
    But possibly someone that should have taken that into consideration forgot about or did not know that . Oh dear.
    In fact, frequently a problem arises ( not just in the case of the InputBox when calling up a help Window ) : You may get an unexpected error, or rather an error message , something of the form
    ___ HH_HELP_CONTEXT called without a [MAP] section
    HH_HELP_CONTEXTcalledwithoutaMAPsection.JPG https://imgur.com/8NOQW95
    ( The code may go on further never the less , after hitting OK, or even in some case it will move on whilst the Message box is still showing, but the Help Window you wanted will not be shown.
    The following workaround may get around this problem.

    Workaround: ( Patch up the .hhp file )
    From this point I am not quite sure what it is that I am doing, but it appears that some “directions” are missing. These are necessary to satisfy one of the standard Windows programs ( API ddl stuff: http://www.eileenslounge.com/viewtopic.php?f=18&t=28885 ) which is called into use , for example, by the the context:= argument parameter, which in the case of the InputBox Function is not optional once the helpfile:= is given.

    ## The main project file ( the file with the .hhp extension ) can be opened with a text editor such as Notepad++. Or at least if you do that something is displayed which you can edit as if it was normal text. What you see there is similar to what you see in the left hand window of the HTML Help Workshop window after you have a created/ compiled project:
    One way to get to this is in the Windows File explorer Window select and right click mouse to get the option to Edit with Notepad++
    RightClickOnhhpFileOpenWithNotepadPlusPlus.JPG https://imgur.com/7AOgfiQ

    By modifying the .hhc project File in Notepad, saving that, and then opening the hhc project file in Microsoft HTML Workshop, and finally compiling will result in the changes reflected in the final .chm file

    These following changes, taking the current working example as example will illustrate the added information which appears to add the “missing directions”
    Part of the initial File looks something like this, here in this example showing two HTML Files:
    Code:
    [FILES]
    INDEXPAGE.htm
    _1X40LL8V5.htm
    Somewhere under that must be added something to give finally in place of that above , the following final text shown below
    Code:
    [FILES]
    INDEXPAGE.htm
    _1X40LL8V5.htm
    
    [ALIAS]
    AnyWord   = INDEXPAGE.htm
    AnotherF___ingWord  = _1X40LL8V5.htm
    [MAP]
    #define AnotherF___ingWord  2
    #define AnyWord   42
    After doing this, the file must be opened in Microsoft HTML Help Workshop and compiled to produce a new .chm
    Usually then I got success in calling up a help window from a code line including both of the two options
    of
    HelpFile:=
    and
    the context:= or HelpContextID:=.

    Exactly what is going on there is anyone’s guess, or those that know won’t tell, maybe as they forgot to get the whole thing co coordinated correctly from the start. For some unknown reason a constant variable must first be associated with a File, ( That is the AliAS stuff ), after which a number can be given to that variable.
    I got this info from various reported ways to fix various bugs, ( those were not directly related to anything to do with the InputBox Function ), so I expect there are some integrate internal coding reasons for having to do this bodge ( workaround )

    Note also interestingly, that in both the case _... of the
    _ InputBox __ ‘ including HelpFile:=, and context:=
    and the
    _ Application.Help __ ‘ including the optional HelpContextID:= argument
    _... as long as valid sections are given in the hhc “text” section, then the code will not error if a non existent Context number is given. In this case no help window comes up.
    If a valid ( existent ) context number is given then… if you are lucky… it works finally !!!!
    _...____

    So I got it to work., finally….
    I went through worked examples following all the stuff that I have tried to explain in the last few posts..
    Then when I run a code like this:
    Code:
    Sub UpDogsBollox()
    ' Application.Help HelpFile:="G:\Excel0202015Jan2016\ExcelForum\UserForm\HTML Workshop\chmFillesProjectFiles\Jan21\AnyFileName.chm", HelpContextID:=2
    Dim strReturned As String
     Let strReturned = InputBox(Prompt:="Hello Help World. Help", Title:="Alan Help", Default:="Bollox", xpos:=100, ypos:=100, HelpFile:="G:\Excel0202015Jan2016\ExcelForum\UserForm\HTML Workshop\chmFillesProjectFiles\Jan21\AnyFileName.chm", Context:=2)
    End Sub
    This is what happens:
    First an InputBox pops up,
    and then
    if you select the Help Button, ….. then a help Window comes up
    HelpGetUpBollox.JPG https://imgur.com/KdKOYWr







    Conclusions:
    I think am happy to mark this thread as [Solved].
    I have got as far as making the_..
    HelpFile:= HelpContextID:=
    ( or rather the InputBox Function which works
    HelpFile:= Context:= )
    _..options in the typical “simple Pop up user interface message box / input box” to work, after a fashion, to do whatever it is that they should do.
    What they should do is at least clear now:, ( Obvious to some maybe.. but no documentation says this clearly … ): The typical _ helpfile / context _ optional arguments should cause an extra button to appear which when Hitting should launch a Window which is part of the “integrated Microsoft Help” thingy.
    I have gone where probably not many have in recent years, and that is to get working examples.
    I’ve got far enough to realise that the “integrated Microsoft Help” thingy might have been somebody’s career making pet idea for a while and seemed a good idea at the time. In the mean time it is a mess and usually does not work.

    It would probably be better to go back to writing notes on the back of a Beer mat
    ( And don’t bother with the drink Microsoft 'Kool Aid'. – drink something better. I can recommend some good dark German Bier )

    [Solved]
    Alan

    P.s.
    ( Any comments or further inputs would be very welcome, Thanks )







    Ref
    https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx
    https://www.codeguru.com/cpp/w-p/hel...-HTML-Help.htm
    https://support.microsoft.com/de-de/...sensitive-help
    http://www.help-info.de/en/Help_Info...context-id.htm



    Some general notes:

    Files:
    hhp _ is a project File. The big thing that you usually will want to create as / with _ File --- New _ with HTML Help Workshop or other software. This will somehow contain or at least “manage” somehow the following 3 file types

    “data” type files, which are used in
    hhc _ is a Help table of contents, usually just 1 I think
    hhk _ is a Help index, usually just one I think
    htm _ are fairly standard HTML file thingies. If you read any good introduction article, book or watch a good introductory You tube video then you will soon get the general idea
    https://www.youtube.com/watch?v=1gLh...D6F7E289625976
    These HTML files contain as main part the bulk of all the text and other stuff you actually want to read. If you are lucky then you might find that simply double clicking on them will “bring them up” as normal looking stuff to read. This probably will come up in your internet browser: Most browsers I expect recognise these types of files, and you can also probably get them up manually if you copy the whole string path and file name and then paste it into the URL bar of your browser ( URL Bar = the place where you usually see the long _ https://www.goog………………. _ – In place of that paste in the _ G:\Excel0202015Jan2016\Ex……………\Part2.htm )

    Modifying Files:
    The hhp file opens in Notepad ++ and shows some text. ## This was useful in order to modify to add those missing [ALIAS] and [MAP] bits
    All other files can be opened and modified in a text editor such as Notepad or Notepad ++

    Actually making/ modifying a “Project”:
    I’m buggered if I could actually work out how to use the HTML Help Workshop Development Window in the efficient way that I expect it should be.
    I expect the idea is that you move around and modify the HTML Files and some how do all the index and contents automatically. But god knows how.

    This is how I actually got the stuff working…
    Way a) manipulate all files with text editors… mostly !!
    The hhc and hhk files ( List index contents stuff ) look similar and have to stay similar, at least as far as how the HTML files they reference is concerned.
    You open them up with a text editor, preferably both at the same time, and make sure that the HTML Files that they reference exist.
    Changehhkhhchtm.JPG https://imgur.com/0gMOk8J
    Once you have done that, then you don’t actually have to “load the HTML Files”… when you make a new project… these files, and / or the hhp file and / or the complied .chm “ know where they are and what to do with them.
    The HTML Files you then modify manually by opening them up in a text document .. or even in Word it seems possible !!

    Default Topic Index Page Title Page
    The hhc File and the bit of the hhp File that opens in a Notepad ++ and the left margin window must all reference the same Default topic=, but god knows what that is – it seems to be some HTML file that must also exist
    So you need to change 4 things simultaneously,
    _ 1 hhc File in a text document
    _ 2 the bit of the hhp File that opens in a Notepad ++
    _ 3 !! and for this you have to be in the Microsoft HTML Help Workshop Development Environment .. modify the left margin window bit .. Project .. [OPTIONS] …..
    _ 4 the name of the HTML file
    IndexPage_1_2_3_4.JPG https://imgur.com/ztE5ZES

    Way b) In the Microsoft HTML Help Workshop Development Environment
    Select either the Contents or Index tab ( to right of the Project tab ) , and double click on the bit you want to modify. The corresponding HTML File comes up the main big window and you can work on it similarly to as if you had opened with a text program such as Notepad or Notepad++
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #34
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    VBA to automate Send and Automatically Sending of E-Mails and Workbooks

    VBA to automate Send and Automatically Sending of E-Mails and Workbooks.


    Learn Material

    In support of this Thread
    http://www.excelfox.com/forum/showth...kbooks-at-once


    Available stuff on the internet
    I downloaded about 40 YouTube videos, ( TheElvesAndTheShoemakerAndEmailUsingVBA.JPG : https://imgur.com/rPNV8Kd ) and found an endless amount of Blogs and other technical support and documentation. I doubt it is possible to review all the Blogs and other technical support and documentation as there is just too much.
    I narrowed down the You tube tutorials to a list approximately in order of how good I found them.
    Its on a scale of 1 to 10 , 1 as very useful and 10 as not very useful. Above 10 gives an approximation of how far away I estimate they are in informing / educating as needed to allow somebody to successfully produce and understand a working code

    Here is the current list:

    I will update it from time to time


    _1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA.


    _1b ** VBA A2Z : Email Automation using VBA. Use GMAIL, Yahoo, Microsoft etc. : https://www.youtube.com/watch?v=nj8mU3ecwsM
    _1a ** Dinesh Kumar Takyar : How to send email using Gmail via Excel VBA : https://www.youtube.com/watch?v=pFl7W8d7d4M
    _2a DontFretBrett : Send Email From Excel Using VBA & Gmail : https://www.youtube.com/watch?v=cOhupIT0rNA
    _4 ** FREE VBA TIPS : gmail vba automation | smtp error fix - gmail via Excel automation - vbatip#31 : https://www.youtube.com/watch?v=Z4aHONly9UQ
    _5 Computergaga : Email Workbook as an Attachment - Excel VBA- : https://www.youtube.com/watch?v=ibjNwZqOZnY
    _6 GuideMe ShowMy Testie : How to send an automated email using VBScript : https://www.youtube.com/watch?v=H4nnPxnCIbI
    _7 Office Academy : Send Email from Gmail using Excel VBA- Modified (with Code Explanation) https://www.youtube.com/watch?v=KIH_FjpPZyg
    _8 FREE VBA TIPS How to send email using gmail via Excel VBA _ 100% working _ sample attached - vbatip#30 https://www.youtube.com/watch?v=cJ3tQTjRTHM
    _10 Excel VBA Excel VBA _ Total Automation of Sending eMails https://www.youtube.com/watch?v=xGFI3H60yXY&t=9s


    Note these ** give details about the security settings required in gmail
    gmail security settings
    For the purposes of my Excel VBA Email investigations I use two Email accounts: My main German Telekom ( t-online.de ) account. I had no problems with this and more or less everything worked the first time.
    I also made specifically a gmail account. I wanted to start from scratch with this to investigate the reported typical security issues that typically seem to prevent the codes initially working. I report my experience with these issues here:
    Gmail account code testing
    The Way 1), using the CDO object library , was completed and was working successfully with my main German Telekom ( t-online.de ) account.
    The I made an account, ExcelVBAExp@gmail.com ( https://support.google.com/mail/answer/56256?hl=en https://accounts.google.com/SignUp ) . The confirmed account was then given as
    ___ excelvbaexp@gmail.com
    I edited code such:
    _ changed the username to __ excelvbaexp@gmail.com
    _ changed the password to that I had given and confirmed when setting up the account
    _ changed the server name to __ smtp.gmail.com
    _ used the user server port of 465 ( for my Telekom addressee 456 or 25 seemed to be OK )
    _ changed _ .To = Doc.AElstein@t-online.de _ to _ .To = excelvbaexp@gmail.com
    I ran code _ Sub PetrasDailyProWay1_COM_Way() _ and it worked first time!!
    Here were some of my settings:
    excelvbaexpInitialSettings.JPG https://imgur.com/NkvDbvB
    LoggedInMy 1 Account 2 SignInSecurity LessSecureApps 3 ON.JPG https://imgur.com/IZEHuGs

    I changed the setting from the last screenshot to less Secure Apps OFF. I then got this error when running code code _ Sub PetrasDailyProWay1_COM_Way()
    Runtime Error 2147220975 (80040211) LessSecureAppsOFF.JPG : https://imgur.com/ddmNJ2E
    Code:
    Laufzeitfehler '-2147220975 (80040211)':
    
    Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0x80040217. Die Serverantwort lautet not available
    
    
    Runtime Error '-2147220975 (80040211)':
    
    The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
    After switching back to Less Secure Apps ON , the code worked.
    LessSecureAppsONfrommWarning.JPG : https://imgur.com/TW2Z7XQ
    LessSecureAppsONfromWarning2.JPG https://imgur.com/TW2Z7XQ
    Note: I had to refresh and log in , Log out etc.. a few times, before it let me slide the switch to ON








    _2 ) Way 2)
    Office Software.. one of ‘em
    Microsoft Outlook …….
    I currently do not think it is possible to use this for a “stand alone” way to use Excel VBA to automate Email activity. This is reflected in no rating under 10 or anywhere near. This is not necessarily reflecting the quality of the videos. This is simply my estimation of how relevant they are to a “stand alone” way to use Excel VBA to automate Email activity. I guess I might think differently if I were more familiar with actually using the Outlook software alone for personal management and Emails etc..


    40 Wise Owl Excel VBA Introduction Part 29.1 Creating Outlook Emails https://www.youtube.com/watch?v=f8s-jY9y220&t=1813s
    129 How to send Email through Outlook at Specific Tme https://www.youtube.com/watch?v=Oa4uDulciQ0
    140 Jie Jenn Send Email From Outlook In Excel _ Excel VBA Tutorial https://www.youtube.com/watch?v=3m2THsOZWjg
    154 YourProgrammingNetwork Excel 2010 VBA Tutorial 61 Sending Emails https://www.youtube.com/watch?v=iuLb3glfZdk
    156 YourProgrammingNetwork Excel 2010 VBA Tutorial 62 - Sending Emails with attachements https://www.youtube.com/watch?v=mOjxfCNnu-U
    158 Excel # 348 Serienmail mit mehreren Anhängen über Outlook versenden VBA https://www.youtube.com/watch?v=i-gvQQ0749Y
    159 Dr. Gerard Verschuuren Outlook API VBA Can Send Emails from Excel through Outlook or Shell https://www.youtube.com/watch?v=UTzq6-UrmP8&t=10s
    160 Send E Mail in VBA Excel through Microsoft Outlook (Video 1 of 4) https://www.youtube.com/watch?v=OwLgf7jebAs
    162 Alex C Send Mass E Mail to Distribution List with Outlook and Excel (Video 2 of 4) https://www.youtube.com/watch?v=i_uJNMJVW1k
    163 Alex C Send Personalized Mass E Mail to Distrubiton List with Outlook and Excel (Video 3 of 4) https://www.youtube.com/watch?v=sIQLedQPrPs
    164 Send Outlook E mail from VBA Excel with HTML Tags (Video 4 of 4) https://www.youtube.com/watch?v=wuoV47SQeKU
    171 Excel macro to send bulk emails using Outlook https://www.youtube.com/watch?v=uW8gjf3lB88
    190 Excel VBA _ Total Automation of Sending eMails https://www.youtube.com/watch?v=xGFI3H60yXY
    255 Send an outlook email with attachments via vbscript https://www.youtube.com/watch?v=nmr283CD_-M&t=93s
    260 Excel Macro to Send Email from Outlook with attachment https://www.youtube.com/watch?v=7gy2oRQdGf0
    386 Send Invoice as an Email Attachment VBA https://www.youtube.com/watch?v=QsmWVSIW77Y
    403 Using Dates with Excel VBA to Automate Email Reminders https://www.youtube.com/watch?v=x2_y0_KDaN0
    409 Send email from excel using VBA w3 https://www.youtube.com/watch?v=V3Uc38y4
    430 SparksEdge ToExcel SparksEdge_ How to send an Emails using Excel and VBA https://www.youtube.com/watch?v=5hJbSngqpP8
    445 Kiran Manchekar Send email using excel macro VBA Tutorial https://www.youtube.com/watch?v=GVopzYXsnqY
    700 Jie Jenn How to automatically CC people when you send an email _Outlook Tutorial https://www.youtube.com/watch?v=BGMcsQ89JUk



    _._______________________________

    Videos from Dinesh Kumar Takyar


    CDO way
    4 Dinesh Kumar Takyar Aug 28, 2014 CDO How to send email using Gmail via Excel VBA https://www.youtube.com/watch?v=pFl7W8d7d4M&t=16s

    Excel VBA – Outlook way
    4 Dinesh Kumar Takyar Nov 16, 2012 How to use automation to send ms outlook mail using Excel VBA https://www.youtube.com/watch?v=XSR4gmBuqiE&t=161s
    5 Dinesh Kumar Takyar Nov 21, 2014 Automate Excel To PDF & Send PDF Document As Mail Attachment https://www.youtube.com/watch?v=XEBr12uxG2I&t=105s
    7 Dinesh Kumar Takyar Jul 3, 2014 Automatically send email to users queries in Excel with VBA https://www.youtube.com/watch?v=N1DMPct5cVA&t=214s
    11 Dinesh Kumar Takyar Apr 28, 2014 How to send email reminder automatically from Excel Worksheet using VBA https://www.youtube.com/watch?v=Em4RCvsAV7s
    9 Dinesh Kumar Takyar Feb 23, 2018 How to send Email through Outlook at Specific Tme https://www.youtube.com/watch?v=Oa4u...ature=youtu.be
    12 Dinesh Kumar Takyar Oct 1, 2014 Using Dates with Excel VBA to Automate Email Reminders https://www.youtube.com/watch?v=x2_y0_KDaN0&t=19s






    _.________________________________________________ ______________________-




    Here are some other YouTube Videos were some information may be gleaned, but I have not fully reviewed them. They are generally not so useful as the previous ones.




    Email Worksheet Via Outlook in Excel _ Excel VBA Tutorial-2jrA-IscKq4
    How to Email Your Current Workbook in Excel VBA-0xnk-kjbrpo
    Out API VBA Can Send Emails from Excel through Outlook or Shell-UTzq6-UrmP8




    Automated email report of excel data-5miDjW6pp5c
    Automatically Display Worksheets of Specific User-FPJBVa5i8ec
    how to send email in outlook _ vba-i-USyj-iPnY
    send birthday emails in powerpoint using VBA-1jFJ35ZPR_A
    Serien-Email mit Access, VBA und Outlook versenden-LfDUie6Mlzg
    VBA How send email in Access-c41EJLd7HOQ
    VBA macro to send a email to one of a list of emails-A-SWhq4GJs4



    Last edited by DocAElstein; 02-27-2018 at 09:12 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  5. #35
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    German Telecom ( Deutsch Telekom FreeMail t-online.de ) Copy Paste HTML code work around

    German Telecom ( Deutsch Telekom ( Freemail t-online.de ) ) Copy Paste HTML code work around

    I had a problem for a long time when copying manually via the clipboard from Microsoft Word ( or from most other sources ) into my main Email Internet tab Browser Program , t-online.de ( German Telekom Free mail).
    The problem started suddenly, close to the time that some update/ changes were made to the Program a few years ago.
    The problem was that usually most formatting was lost, and only simple plain b/w text came into the browser.
    Previously, most formatting , such as text color, text fonts , test size etc. were maintained such that the final E mail main body as seen and sent, looked very similar to as the prepared message in the Word document. This maintaining of format was / is still the case when pasting manually into gmail and many other places. ( This conversion to simple b/w is usually only seen when pasting into very simple basic text reply windows which supported no formatting )
    Formatedtext.JPG ; https://imgur.com/A9Sq5Y1 PastedInGermanEMail https://imgur.com/CZRH5jj
    Formatedtext.JPGPastedInGermanEMail.JPG
    ( Here is the same after pasting into gmail: PastedIn gmail.Jpg https://imgur.com/CzqCJ7y )



    I noticed the issue in a different situation, and was able to do a simple workaround that spread some light on the issue.
    This post gives the background to the situation.
    The next post explains the workaround. ( The workaround by passes pasting into the German Email Reply/Editor Window, but the sent and received Email does not suffer from the problem of loss of formatting ).

    The issue in a different situation..- The situation
    I answered a Forum question here recently, ( http://www.excelfox.com/forum/showth...kbooks-at-once ), and a spin off was a further insight into the issue.
    The thread concerned automating sending of an Email with attachments using ( Excel ) VBA.
    The successful solution involved sending directly to any Email the complete protocols, messages, HTML code string etc.. etc.. that any Email program, such as Email Internet tab Browser Program , t-online.de , would in end effect “send” out along the internet. ( The final solution was not dependent on having any Email Program available or open. )

    The VBA Program to send Email solution:
    It was a completely independent “stand alone” solution: Part of the program “ took in “ the main body / main content of the message, that is to say what a user would normally manually type in and/ or paste in. ( As seen in the above screenshots )
    ( This main body is what I personally might prepare, for convenience, ( and for purposes of having a back up ) , in a Word document and then copy and paste manually across. That copied across would then be sent , and the Word document is then effectively my back up copy of the sent message )

    Supplying the main Email message body to the code:
    This main body can be supplied to/ given within the code
    either as…….
    _ a simple text string, like pseudo __ = “ Hello,” & vbCrLf &this is my message. “ __ , which would give a final message received like
    Hello,
    This is my message
    That text can also in code typically be supplied in a single String type variable in a typical code part like:
    Dim strText As String
    _ Let strText = "Hello," & vbCrLf & " this is my message"
    Code:
    Dim strText As String
    _  Let strText = "Hello," & vbCrLf & " this is my message"
    or………

    _ alternatively it could be given as HTML code supplied similarly in a long string variable. That string variable would contain a long string of all the HTML necessary to create text, table, pictures and all possible formatting. That HTML type code can usually be read by most “internet type” or general computer communication type software such as to be able to show a complete formatted page or pages just as one might see in an internet web site or a large Microsoft Word document containing all sorts of formatting including tables, pictures, etc.. etc.. ( I chose to use this second HTML string giving way). This sort of coding is the most common language, at least up until recent years, in computer communication generally.
    ( By the way htm, HTML ,XHTML, html are more or less the same – it is all to do with coding which mostly consists of ” tag pairs” made with pointy brackets https://www.youtube.com/watch?v=1gLh...D6F7E289625976 )
    Making/ writing the .htm code string
    If I was a fluent computer HTML expert then I could just write out the htm equivalent of the message with formatting that I wanted to send by Email. I am not, and I don’t need to be: There are many ways to get the code, such as free converters on the internet which give the code if you paste in what you finally want into a window. ( All software I have tried have not suffered from loosing the format when I paste in to their window )
    But something even more convenient is available as standard with Microsoft Word:
    A simple practical solution was found to be to take advantage of the possibility of saving a normal Word ( file, normally saved with the extension .docx ) , with a file extension of .htm
    SaveAs Word.JPG Save As Word doc to htm.JPG https://imgur.com/h7XFAQX https://imgur.com/vhRE9CC
    This somehow saves the document primarily in some version of HTML coding language. ( I have no idea what it normally does with the .docx extension – probably a trade secret ). A small amount of coding was then all that was necessary to convert that .htm file into a single text string of the type required for the Email sending code with the option of supplying the main body in HTML format. ( That coding is a line like, pseudo _ strText = GetTheFileAsATextStreamandReadItAll_ )

    HTML is one of the main languages used in internet communication, and most internet browsers and Email programs use this. ( In the case of the first simple test string option, ( = “ Hello,” & vbCrLf &this is my message. “ ) , that given text string would probably be converted initially by some software into HTML coding before being further used . ( Hence it is more efficient to use the htm option in the first place )

    The long HTML string is rather difficult to “see” in its single string form as it is literally an extremely long sting text with pointy bracket HTML code. To a first approximation, this long string of information forms the bulk of what is actually “sent down the internet line”.
    No programmer could ever examine easily such a long string of coding. The coding is therefore organised neatly with things like the _ vbCrLf _ used in simple text strings to give a new line.
    Because of this last point, if you open such a HTML file with a simple text editor then you see the coding neatly laid out.
    For example, this is a screen shot of a simple Word document, which I made and then saved as .htm:
    ProMessageTelekom.JPG : https://imgur.com/zn3BGWj
    ProMessageTelekom.JPG
    “ProMessageTelekom.docx” : https://app.box.com/s/pumcahipuhjbl59ka7lb3qv8i682kcwc
    “ProMessageTelekom.htm” : https://app.box.com/s/vfz0y102bikl9hrka6dlgqyotlrttlpi

    If I right click on the .htm File in the explorer window, then I can choose to open this file in a simple text editor , ( rather than by using Word, which may be the default, - but note always: You might find that by default it opens on your Internet browser, - that is because , as noted, that browser software has as its main job reading HTML coding and converting it into the text , pictures, tables etc. that you can “see” )
    OpenProMessageHTMLWithTextEditor.JPG : https://imgur.com/4zev9Kv
    OpenProMessageHTMLWithTextEditor.jpg
    Opening the file in a text editor will give you ( towards the end ) the actual coding relevant specifically to “making” that seen text. The relevant part is in between the “pointy bracket code tag pair” named _ div class=WordSection1 ___ /div __ That section is all that I am interested in: All the other stuff is I expect a lot of stuff specific to a Word Document or maybe even some other complicated stuff. I don’t know
    ProMessageHTMLInTextEditor.JPG : https://imgur.com/eTUd17q
    ProMessageHTMLInTextEditor.jpg

    Last part of HTML code for File “ProMessageTelekom.htm” :
    See here:
    http://www.excelfox.com/forum/showth...0525#post10525




    Using code instead of Email Window to send Email ( with attachments )
    The codes for doing this are described from here: http://www.excelfox.com/forum/showth...0518#post10518
    The end result is , simplified, as follows:
    The .htm file is “sent” by the code along the internet line as a HTML code string
    rather than
    pasting into the Email Editor and hitting the send button manually

    The German Telekom Email Editor / Email Program is not used by this particular code . ( that step is effectively bypassed). So, the issue of the format loss occurring when pasting into the German Telekom Email Editor / Email Program is not present

    However: The received Email in German Telekom suffered a similar problem.
    Using the same Word file example again:
    This was the File used to produce the HTML code string to send
    ProMessageTelekom.JPG : https://imgur.com/zn3BGWj
    This came on as expected in most places, for example in gmail:
    ProMessageArrivedAt gmail UsingCodeToSend.jpg : ProMessageTelekom.JPG : : https://imgur.com/u57kRD7



    But.
    .. The same sent to a German Telekom t-onlone.de address came on with a similar format loss problem to that experienced when pasting into the German Telekom Email Editor / Email Program
    ProMessageRecievedAT tOnLinede UsingCodeToSend.JPG : https://imgur.com/h4wJVXd



    _.______________________



    So that this post was in way of an introduction.

    In the next post is the bit relevant to the issue with pasting into a German Telekom Email Window..
    Last edited by DocAElstein; 03-01-2018 at 10:33 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #36
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Solution ( work arounds ) to the issue with pasting into a German Telekom Email Window.
    .


    To recap from last Post….
    I had a problem for a long time when copying manually via the clipboard from Microsoft Word ( or from most other sources ) into my main Email Internet tab Browser Program , t-online.de ( German Telekom Free mail).
    The problem started suddenly, close to the time that some update/ changes were made to the Program a few years ago.
    The problem was that usually most formatting was lost, and only simple plain b/w text came into the browser.
    Previously, most formatting , such as text color, text fonts , test size etc. were maintained such that the final E mail main body as seen and sent, looked very similar to as the prepared message in the Word document. This maintaining of format was / is still the case when pasting manually into gmail and many other places. ( This conversion to simple b/w is usually only seen when pasting into very simple basic text reply windows which supported no formatting )
    Formatedtext.JPG ; https://imgur.com/A9Sq5Y1 PastedInGermanEMail https://imgur.com/CZRH5jj
    ( Here is the same after pasting into gmail: PastedIn gmail.Jpg https://imgur.com/CzqCJ7y )


    Some thing similar was experienced when I recently wrote a compute program to directly send Emails:

    This was the File used to produce the HTML code string to send
    ProMessageTelekom.JPG : https://imgur.com/zn3BGWj
    This came on as expected in most places, for example in gmail:
    ProMessageArrivedAt gmail UsingCodeToSend.jpg : ProMessageTelekom.JPG : https://imgur.com/u57kRD7

    But.
    .. The same sent to a German Telekom t-onlone.de address came on with a similar format loss problem to that experienced when pasting into the German Telekom Email Editor / Email Program
    ProMessageRecievedAT tOnLinede UsingCodeToSend.JPG : https://imgur.com/h4wJVXd


    _._______________________-


    I looked at and compared a lot of different sent HTML strings , and examined what came into different places.
    In other words I messed around empirically with the HTML string, and then took a look at how that effected the formatting actually seen in the final received and viewed Email.
    I do not really understand HTML programming ( or anything much with computing for that matter ) so I do not know the significance of what I noticed.
    However I was able to consistently ensure that the correct format was received , that is to say , the received Email looked very similar to as it had in the prepared Word document.

    I went on to write an additional part , ( an extra Function ) which made the necessary changes to the HTML code string just before the final sending. This consistently resulted in the correctly looking Email being received.

    Once again using the same example:
    This was my prepared Word document, saved in .htm extension, and used in my code to produce the long string of HTML code text to send as an Email:
    ProMessageTelekom.JPG : https://imgur.com/zn3BGWj

    With the modified code, the received Email now looks like this:
    ProMessageRecievedAT tOnLinede UsingModifiedCodeToSend.JPG : https://imgur.com/UuPdyOR

    _._________

    The code modification:
    The existing code:
    Part of the existing coding is a Function
    ( http://www.excelfox.com/forum/showth...0517#post10517 )
    This uses the Word .htm file to produce the single ling string of HTML coding.
    The final result is for that Function to return that string in the variable MyLengthyStreaming
    Effectively the signature line of the Function defines / declares that variable thus:
    Pubic Function MyLengthyStreaming() As String
    VBA indeed holds a variable of name MyLengthyStreaming of String type whilst that Function code is running

    That code line is then effectively, pseudo
    xxxxDimxxxxxxx MyLengthyStreamingxx As String
    In place of the normal “filling” of a variable by pseudo_...
    xxstrvariablex = xxxxxxx
    _.. we do something similar by
    xxstrvariablex = MyLengthyStreaming()

    The result is that the Main code execution pauses at this part MyLengthyStreaming() , whilst the function code , Function MyLengthyStreaming( ) is carried out. Then the main code resumes once that function is Ended. Generally the final result of the Function will be to fill the variable MyLengthyStreaming within that function. So then the complete execution of that code line _..
    xxstrvariable[color=white]x[/color = MyLengthyStreaming()
    _.. will return the filled value of MyLengthyStreaming into strvariable.

    The above is a typical progressing when a main code Calls a function code.


    Modification / additional Function
    The strategy now is simply to modify the final string in MyLengthyStreaming within the function.
    For convenience I have done this in another, second function,
    Pubic Function MyLenghtyDiesScreaming_Telekom(ByVal MyLengfyScream As String) As String

    This Function is called from within the first function, in this extra code line which is the main modification to the existing codes:
    Let MyLengthyStreaming = MyLenghtyDiesScreaming_Telekom(MyLengthyStreaming)
    This function differs slightly from the first function in that it contains an argument passed in the brackets ( _ )

    Effectively the purpose of the function code is to use the value in MyLengthyStreaming before returning that modified value back into MyLengthyStreaming. So it modifies the HTML string in MyLengthyStreaming

    Once again , this is one typical way that a function is used

    The Function I have called Function MyLenghtyDiesScreaming_Telekom
    It has the signature line of
    Pubic Function MyLenghtyDiesScreaming_Telekom(ByVal MyLengfyScream As String) As String
    Effectively the signature line Dim’s MyLenghtyDiesScreaming_Telekom as a String variable and MyLenghtyDiesScreaming_Telekom can be used as such in this function code. Assigning a variable to this in a main code will cause the value held by VBA in the variable MyLenghtyDiesScreaming_Telekom at that point to be out in the assigned variable, but first the main code will be paused at this “Ccalling” code line whilst the function code is carried out. So we have the chance to do something in the function to fill that variable, MyLenghtyDiesScreaming_Telekom . We can take one or more things in, in the ( __ ) to use . In this case we want to take a string in and then return it modified , hence the last code line is simply
    MyLenghtyDiesScreaming_Telekom = MyLengfyScream


    _.______

    What is the main modification
    After a lot of experimenting it appeared that the modification which mostly gave the desired end results were to do some simple exchanging of ' and "

    As example a HTML part such as this_..

    <p class=MsoNormalCxSpMiddle><span style='font-size:24.0pt;line-height:115%;
    font-family:GungsuhChe;color:#0070C0'>W24 <span class=SpellE>GungsuhChe</span></span><span
    style='font-size:24.0pt;line-height:115%;font-family: "Franklin Gothic Heavy", "sans-serif";
    mso-fareast-font-family:Batang;color:#0070C0'> <o:p></o:p></span></p>

    _..will be modified to this_..

    <p class=MsoNormalCxSpMiddle><span style="font-size:24.0pt;line-height:115%;
    font-family:GungsuhChe;color:#0070C0">W24 <span class=SpellE>GungsuhChe</span></span><span
    style="font-size:24.0pt;line-height:115%;font-family: 'Franklin Gothic Heavy', 'sans-serif';
    mso-fareast-font-family:Batang;color:#0070C0"> <o:p></o:p></span></p>



    How is the main modification done
    In VBA coding there are many ways to achieve the required modification. My final way is probably not the most efficient. But it is fairly easy to follow through, - I have ‘commented it extensively.


    The modified first function and additional function is here:


    Notes:
    _1: The modification seemed to have no effect on the end result when the modified HTML string was sent in place of the original for other Email Program recipients other than German Telecom ( Deutsch Telekom ( Freemail t-online.de ) ), so I have not bothered to add anything to select use of the function only in the German Telekom recipient case. But this might be a more efficient development to do that later.
    _2: I added a few bits to the function to remove the larger parts of the HTML coding that appeared not to be relevant to the main body text.

    _.__________

    Here is the modified initial function and the new second function:
    http://www.excelfox.com/forum/showth...0528#post10528
    Last edited by DocAElstein; 03-01-2018 at 11:32 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  7. #37
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Some Notes to Distinguish between these 2 Worksheet embedded Button types: ActiveX Control ; Form Control

    Some Notes to help Distinguish between these Two Worksheet embedded Button types:
    ActiveX Control ; and
    Form Control


    In these Threads:


    I am asking for help in understanding and getting a simple Button in a Worksheet. That Button should set of a VBA macro code

    This post is just some notes to clarify the two things that I am talking about…

    Currently there are two ways to do this:
    Form Control
    ActiveX Control


    For the case of a simple Button to start a macro, they appear to work fairly similarly. There is a lot of stuff “behind” them that I know nothing about. I would like to get familiar with them, and if possible both types up and running before deciding which / where to use.

    So this post is just to make clear what I am talking about and give the basic characteristics and features of the two types, just so we all know what we are talking about , ( at least in terms of the very initial introduction of using them initially.

    So…
    Just to be clear what I am talking about..
    The button things that I am talking about
    I am talking about the things you “insert” into a worksheet like this…

    Excel 2007+ (‘_- Find the hidden Development Ribbon Tab, then select the tools controls icon to get both of them ! :-)
    https://support.office.com/en-us/art...AABAAA=Windows
    http://www.jkp-ads.com/Articles/controls01.asp
    Excel 2007+ Form and ActivX Control buttons.jpg https://imgur.com/x2OxFvX
    https://app.box.com/s/st84weg49tjgj25x49iib8h1mv9gb7k4
    Excel 2007+ Form and ActiveX Control button.jpg

    ( To get you back to 2003 Menu stuff should you be familiar and prefer : https://www.youtube.com/results?search_query=ubitmenu https://chandoo.org/wp/2009/10/19/ex...olbar-in-2007/ … and/ or then…. )
    Excel 1997-2003
    (‘_- a) Select the View Tab then select either or both of the controls Tool Bars , http://www.jkp-ads.com/Articles/controls01.asp https://imgur.com/VLv2uvM Excel 2003 Form and ActiveX Control buttons.jpg https://app.box.com/s/me97qpwtmzybm42ed8z876q19bd801o9
    Excel 2003 Form and ActiveX Control buttons.jpg

    or b) Select the View Tab then select the Visual Basic tool bar, from which you can select the tools controls icon to get the ActiveX controls Tool bar :-) https://imgur.com/AqHDBBH http://www.jkp-ads.com/Articles/cont...nts=True#25922 Excel 2003 ActiveX Control button from VB Tool bar.jpg https://app.box.com/s/l40n0ktm5d8uzrswe3czyae2xby60hqa
    Excel 2003 ActiveX Control button from VB Tool bar.jpg
    _.................

    General Stuff about them, what they are..
    Form controls ( not to be confused with UserForms ) were an original part of Excel so allowed you to customise the Worksheet a bit. Probably one reason they were kept is to allow for backward compatibility. ( In the meantime they are probably kept as the alternative ActiveX stuff goes wrong a lot )

    ActiveX was some attempt by Microsoft to combine some other stuff which was itself an attempt to combine or allow communication through a communicating object which then they tried a bit later to extend to embedding that object in stuff… ActiveX controls refer to a specific way of implementing the earlier technologies, and no one has any idea anymore exactly how it all works or what links still exist when they “run” , so it is no surprise it has turned into a mess where things go wrong and no one is quite sure why.
    ActiveX things have a strong reputation of breaking themselves or breaking other stuff, ( maybe because of all the attempt at linking stuff that they are supposed to do? ) so they can really mess things up basically. Viruses for example can slip through all the links or routes that get set up and which no one remembers anything about. ( I keep seeing myself that they suddenly do not work for no apparent reason. I have not managed to get ActiveX controls Buttons to work at all yet on a couple of computers with Excel 2003 ). It sounds like you need to be a bit of a computer expert in a lot of stuff outside Excel if you use ActiveX controls Command Buttons because of all the damage they may cause.
    ActiveX controls
    It would appear that the word ActiveX could almost apply to anything. The ActiveX control things here might be described as the ones optimised for internet, Internet Explorer, but which also work, as I am using them, in Microsoft Office
    ( ActiveX controls don’t work on a Mac: I expect that is because they are attempting to link a lot of Microsoft stuff, so it would be a mammoth task to get them working outside a Microsoft environment, especially as they don’t work very well in the Microsoft environment either , not that is of interest to me )

    Codes associated with / assigned to a “Worksheet Button
    Form Control code

    To see the code already assigned to an Excel Form control button you select the option to assign it which you get in a menu when you click right mouse when the mouse cursor is in it. Then it will highlight the current Sub assigned it.
    ActiveX Control code
    ( Right clicking will have the same effect as left click, that is to say activate/ press the button.
    You need to get into “design mode”, then the right click will get you the code
    …..)
    To see the code with a Command button ActiveX control you first have to select the Design mode icon which is next to the Controls icon in:
    ( For Excel 2007+ the Ribbon from the Visual Basic Development Tab ( https://imgur.com/MqVC6jB ); Excel 2007+ Form and ActivX Control buttons and ActiveX Design Mode .JPG https://app.box.com/s/3udml4bnnn07um7lgeg0uwpuczj8m0pm
    Excel 2007+ Form and ActiveX Control button.jpg
    and
    For Excel 2003 the Visual Basic Development Tool Bar or ActiveX controls Tool bar ( https://imgur.com/yR2491F ). Excel 2003 ActiveX Control button from VB Tool bar.JPG https://app.box.com/s/l40n0ktm5d8uzrswe3czyae2xby60hqa
    Excel 2003 ActiveX Control button from VB Tool bar.jpg
    Once in Design Mode, a right click when the cursor is in a ActiveX controls Command Button will give you the option to view the code , and on selecting that option, the appropriate worksheet code module will pop up.
    )

    Text on the Button:
    ActiveX control

    For an ActiveX control button you also need to do that select the Design mode icon step to be able to edit the text on the ActiveX button ( which you then do by a right click on the button followed by selecting Properties option. Then you can edit the Caption Property from the Property Window that comes up ) , and it appears that the text can only be in a single line. It would appear that the caption is a property of an under Object within the Worksheet Object ( https://imgur.com/MqVC6jB ).
    Form control
    For a Form controls Command Button to change the text you right click on the button then select the text and you can change it and also , it appears have the text on multiple lines

    Codes sorts behind them:
    ActiveX control

    ActiveX controls Command Buttons are a sort of “Eventy Clickies** thingy”, so the code has to go in the Worksheet Code module of the Worksheet in which it is. So a specific ActiveX controls Command Button is a VBA Object thingy. It gets called a Private Sub when you add / Insert it. You can change it to Public Sub , and that does not seem to make much difference
    ( ** So you can have other codes with an ActiveX control, reacting on additional things such as passing over a Button.
    CommandButtonClickies.JPG : https://imgur.com/uxkIpZS
    CommandButtonClickies.jpg

    For the Form control you just have the click
    ……. )
    Form control
    I don’t quite know what sort of a thing a Form controls Command Button is. It is a pseudo a single event ( click ) Thing: You assign one code to it. You can do that, for example, by clicking on the right mouse when the mouse cursor is on it, followed by selecting the option to assign a macro after which the pop up macro assign window should pop up. It appears to me###, based on my experiments, that the code can go anywhere and can be Private Sub or Public Sub, ( although you will not get it offered as one to choose from in the pop up macro assign window, if you make it a Private Sub. ( ###This observation from me contradicts some stuff I have read which says the codes are always written in normal modules… well they ain’t by me .. and no ill effects yet )











    Ref:

    https://www.excelforum.com/excel-pro...ml#post4787311
    https://www.excelforum.com/excel-pro...ml#post4789873

    Attached Images Attached Images
    Last edited by DocAElstein; 03-25-2018 at 08:50 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #38
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    List of, and search of Updates on Computer:

    Some additional notes on things broken ActiveX controls..
    These are just some additional notes in support of other Forum Threads on the Theme of things “broken by / with ActiveX controls”
    http://www.excelfox.com/forum/showth...en-insert-them
    http://www.excelfox.com/forum/showth...ommand-Buttons
    http://www.excelfox.com/forum/showth...0586#post10586




    List of, and search of Updates on Computer:
    Often one hears that updates can cause problems with things “Activia X y controlly” . This Post considers ways to automate seeing what you have and checking for particular ones reported to have given problems with ActiveX controls…….
    ( Actually what we are doing here is to get a list of updates on a computer and / or search through/ for them. So this post may be of general interest when looking for any updates on a computer ).
    Old black box command Dosy Window looking at Files stuff
    Getting a window up that looks like an old black screened computer up and typing simple old computer type commands seems to be what one often uses when delving into these sort of things, so that is a good start point.. ( I think this window thing is possibly similar to the console Window thingy in Visual Basic https://www.mrexcel.com/forum/genera...ml#post4357648 )
    Get it up
    From Hans here: http://www.eileenslounge.com/viewtop...=28682#p222044
    http://www.eileenslounge.com/viewtop...=28682#p222044


    _ Press _ Windows key + r _ to activate the Run dialog
    Windows+r.JPG : https://imgur.com/1KI8nok
    cmdInBoxBar.JPG

    _ Type in box bar _ cmd _ if not already shown
    cmdInBoxBar.JPG : https://imgur.com/L9saGzj
    Windows+r.JPG

    _ Press _ Ctrl+Shift+Enter _ to start an "elevated" command prompt.
    ElevatedCommandPrompt.JPG : https://imgur.com/W1p6frJ
    ElevatedCommandPrompt.JPG

    Get a list of Updates in a text file
    Note: In the following steps you need to type a few things in exactly as given. I have sometimes experienced problems with characters not working and / or pasting in not working. But usually one or a combination of the following will work
    _ (i) If you are lucky you can type all characters.
    _ (ii) Copy text from here and paste in using _ Ctrl+v
    _ (iii) Copy text from here and past in using _ option given when clicking right mouse

    _ To get a text file produced, for example on your desktop, type in a single code line something like these examples , using a file path to suit you:
    Code:
     wmic qfe get hotfixid > C:\Users\Elston\Desktop\UpdatesOnVistaAspire4810TZG25thMarch.txt 
    wmic qfe get hotfixid > C:\Users\Alan\Desktop\UpdatesAcerMartinWin7Pro64Bit26thMarch.txt
    It may take a few seconds, and then you should see a text file appear ( in this case it appeared on my desktop because I put that path in ) looking something like this : https://imgur.com/9Ze2FY6
    Here is the first part of the text file on one computer.
    Code:
    HotFixID                                
    {EF8CD7FC-438D-49E3-A2C7-201052D9F2EF}  
    {8D2CDFAB-0079-43CC-A289-2F7A67F0A4DE}  
    {98D8F490-1F42-4F29-A59B-BF96D23A11BA}  
    {B730F010-3FCF-4E80-8A5A-C1DBEC0CF55A}  
    {B73E5AF4-40C6-4EA9-8F57-CFA70CC72BD6}  
    {BF11577A-6876-45AA-86C9-2BA4CFB8B019}  
    {E359D786-B101-4545-B8AB-8652323CF3CA}  
    {F4139440-5426-4C6F-909B-F71CEB1071B1}  
    {B2FAD7E1-67F9-435D-98BD-A77DBF4E1381}  
    {0CC8FAD2-05D8-4A0A-9E5C-2CDACFF86996}  
    {0EF0D4FB-BB23-4515-AAEA-1240AC2DA525}  
    {800D1A82-D1B0-4ED4-89B4-C666B570ABA5}  
    {8D2CDFAB-0079-43CC-A289-2F7A67F0A4DE}  
    {98D8F490-1F42-4F29-A59B-BF96D23A11BA}  
    {BF11577A-6876-45AA-86C9-2BA4CFB8B019}  
    {E359D786-B101-4545-B8AB-8652323CF3CA}  
    {F4139440-5426-4C6F-909B-F71CEB1071B1}  
    {B73E5AF4-40C6-4EA9-8F57-CFA70CC72BD6}  
    {D6C976E4-E88C-4048-9A6B-39400D2933C7}  
    {FC8EA2A2-65A3-366E-A687-5B63943A800F}  
    {6F8500D2-A80F-3347-9081-B41E71C8592B}  
    {AC76BA86-7AD7-0000-2550-7A8C40011010}  
    {FC8EA2A2-65A3-366E-A687-5B63943A800F}  
    {D758F295-2DE9-4A95-B857-B18BF52A26B2}  
    {E94DDFCD-6D84-40A4-B5DF-24AE25761924}  
    KB971513                                
    KB971512                                
    KB982861                                
    982861                                  
    KB960362                                
    942567                                  
    KB971514                                
    KB2761494                               
    KB2079403                               
    KB2117917                               
    KB2141007
    Here is the uploaded text file obtained from a couple of my computers
    “UpdatesOnVistaAspire4810TZG25thMarch.txt” : https://app.box.com/s/z90o8yj7iz0188yci34mu7gahe2tfhce
    “UpdatesAcerMartinWin7Pro64Bit26thMarch.txt” : https://app.box.com/s/8m96l0e7yh1wcb15y06eaaz6a7vtjzgd

    It would be nice to automate that . I Don’t know how
    Never mind. In any case you should now be able to get a list

    Search the list for specific updates updates
    A few ways…
    ( A way without using the text file )
    _ use the command like : _ wmic qfe get hotfixid | find "KB1234567"
    If KB1234567 is installed, you'll see KB1234567 on the next line, otherwise it'll remain blank. : http://www.eileenslounge.com/viewtop...=28682#p222044
    https://imgur.com/9Ze2FY6


    ( Ways using the text file )
    _ Knacker your eyes looking through the text file
    _ You may be able to do a simple search with your text editor. Using Notepad I can get a search from the following options from the top of the open editor window of something like __
    --- Bearbeiten --- Suchen

    or
    ---- Edit --- Search
    or
    using the shortcut key combination __ Ctrl+f
    You then have a search window in which you can type in what you want to find
    Note however: I think there are sometime some strange characters that are not always visible to you, and then a search may fail as it won’t make a perfect match possibly. I am not too sure exactly what I mean there, but others have told me some quirky things can happen such that that text search fails…
    The next way can help over come that ###

    _ It can easily be automated to search the text file using VBA. I prefer to do this mainly as it is less likely for me to make a mistake, but also as I have found some strange characters coming in to dirty/corrupt the string of the update, and the search can be done for a match on part of the string which will help offset the problem of missing a match due to the string corruption ###
    Here is a simple code.
    Sub CheqUpDates()
    http://www.excelfox.com/forum/showth...0586#post10586

    You input the Updates that you want to search for with one or more spaces in between, and you can type in just part of the character string. All matches will be returned in a string in a message box, and also that string is available to be copied from the immediate window.
    Last edited by DocAElstein; 03-31-2018 at 11:13 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #39
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Code to make Day list from Excel File and show in Pop up Word .htm File

    Code to make Day list from Excel File and show in Pop up Word .htm File.
    MakeTagList Button , Sub MakeTagList()
    This post gives some Overview notes for later reference to help clarify some other more detailed excelfox Threads.

    MakeTagList Button. Briefly, overview:
    The code Sub MakeTagList(), primarily does two things:
    _ makes a simple text file with the important details from a Daily Diet Protocol
    DailyTextFileForEmailAttatchment.JPG : https://imgur.com/MqEmlDF
    DailyTextFileForEmailAttatchment.jpg

    _ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word which can open it in a form which looks similar to a normal Word doc
    That Word File is opened temporarily to show a pretty table form of the important details from a Daily Diet Protocol:
    TemporaryWordPopUp.JPG : https://imgur.com/ZFhsdaQ
    TemporaryWordPopUp.jpg

    _.___________



    _ makes a simple text file with the important details from a Daily Diet Protocol
    arrNuts()
    The start point is that an array is made of used food products from daily pro, arrNuts()
    arrNuts()
    is the = Items() array from a Dictionary. So it is a one dimension array starting at arrNuts(0). Each Element is a row from the from the Daily Excel Diet Protocol File, “ProAktuellex8600x2.xlsm”. It is built up from a line like pseudo
    Code:
             Key:=ProRow with Entry in C column, Item:=Array(FoodProduct in 1st Column A , amount eaten from 3rd Column C  , Kcal total in 9th column I  , Fett , Eiweiß  …. Etc.
    ( The Key is not used currently )

    Rem 5 Text File
    A text file is made from the array with a pipe, _ | _ , as separator like from:
    arrNuts(0)(0) & “|” & arrNuts(0)(1) & “|” & arrNuts(0)(2) ……..etc..
    arrNuts(1)(0) & “|” & arrNuts(1)(1) & “|” & arrNuts(1)(2) ……..etc..
    arrNuts(2)(0) & …..etc..
    The final text file is stored in the same Folder as most of the files to do with daily protocols, that is to say, the currently used main Folder . It is given a name something of the form pseudo
    “MonatsUebersichtAnhang " & Date in format like "mmmm yyyy" & “.txt”
    As example for any day in April, 2018, it will be like
    “MonatsUebersichtAnhang April 2018.txt”
    The main purpose of this text file is to have a file that can be attached to an Email which at the receiving end can then be for recording and using the important daily nutrition values in a graphic
    I use the standard VBA type code lines for this , pseudo like
    Open C: \ __ \ _ \ ¬¬¬-___“MonatsUebersichtAnhang April 2018.txt” For __ As __
    _.___________

    _ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
    Rem 6 .htm Word Template File
    An empty or almost empty .htm Word file, “DailyProtable.htm “ , ( which should be in the main currently used Folder already , - ( download from box if necessary ) ) is converted into a single very long String variable , TotalFile
    I use the fairly standard VBA way often used to get this long string from a .txt file. It seems to work equally well for a .htm file
    Code:
    Rem 6 get Template File as long HTML string . Template file is an almost empty Word .htm file.
    Dim FileNum As Long: Let FileNum = FreeFile(1)    ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
    Dim PathAndFileName As String, TotalFile As String
     Let PathAndFileName = ThisWorkbook.Path & "\" & "DailyProtable.htm" ' Template file never changes
      Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
        TotalFile = Space(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
        Get #FileNum, , TotalFile
      Close #FileNum
    Rem 7 modify main body text section of the template file
    Rem 7
    Things start getting a bit complicated now. It may help at this point to give a summary of the overall goal:
    The final aim of this code and the associated code usually done just after, Sub PetrasDailyProWay1_COM_Way() , is to get a pretty summary table in a HTML code such as it is in a form that can be used for two things:
    _ Temporarily in a Word .htm file which is opened temporarily to show the current stand of a finished or almost finished protocol,
    _ part of that ( the main body part ) can be isolated into a functioning variable , MyLengthyStreaming , and used in the typical LECMO technology code lines of the form
    CDO.Message.HTMLBody = MyLengthyStreaming
    CDO.Message.Send

    ( In actual fact the entire Word.htm HTML string, TotalFile , could usually be used, but I do not do this as
    _ TotalFile has a lot of unnecessary text, presumably needed for Word .htm file recognition
    as well as
    _ there are some subtle problems which need to be addresses with the string before it can be used with German Telekom, as it appears that a Bug means that the German Telekom does not quite recognise correctly that standard HTML coding ( gmail and AOL seem not to have this problem ) https://telekomhilft.telekom.de/t5/E...136416#M129791
    )

    What basically needs to be done is to modify the TotalFile at the main body part. This Part of the text is almost empty at this stage. That almost empty main body part is replaced by a made pretty HTML table
    It is all done in a bit of a messy way, and it is easy to lose track of what is going on. Once again, the purpose of this code:
    The code Sub MakeTagList(), primarily
    _ makes a simple text file with the important details from a Daily Diet Protocol
    _ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc



    _ makes a long string of HTML coding which if written to a text file, (but with the usually given .txt extension replaced with .htm ), is recognised by Microsoft Word and can opens it in a form which looks similar to a normal Word doc
    In Rem 7 we basically embed a Table ( in HTML coding form ) by doing a simple Replace of the start part of the main body in the string from the Template .htm file which looks like this
    <div class=WordSection1> ( You can see that yourself if you open up the .htm file using a simple text editor, and look towards the end MainWordSectionBody.JPG : https://imgur.com/HWjHTyz )
    That existing HTML coding, <div class=WordSection1> , is Replaced by
    <div class=WordSection1> & A made HTML table done in a Function
    That Function is called using the arrNuts() thus:
    ProTble(arrNuts())

    Function ProTble()
    By a bit of trial and error and learning some basic HTML coding I was able to write this function which based on the data given in arrNuts() gives a lot of HTML coding which a Browser ( or Word when it has it included in the long HTML string opened using a .htm extension file ) can understand and produce a table formatted as I want:

    Rem 8
    Back in the main code we now have our original full file text string of HTML coding, TotalFile , modified to include in it the pretty table
    The string is printed out to a text file in the usually way but with the .htm extension instead of the usual .txt extension. This file is currently given the name
    "DailyProtableFilled.htm"
    Code:
    Rem 8 Over write the last ( likely yesterdays ) filled file,  with the modified template file which is mainly still that what Word recognises as we only modified a bit of the main text body
    Dim HighwayToHelloPro As Long ' For rewrite of modified DailyProtable.htm as DailyProtable.htm2
     Let HighwayToHelloPro = FreeFile(0)
     Open ThisWorkbook.Path & "\" & "DailyProtableFilled.htm" For Output As #HighwayToHelloPro ' Will be made if not there, and overwritten as Output rahter than Append
     Print #HighwayToHelloPro, TotalFile
     Close #HighwayToHelloPro
    Rem 9 get Word using Excel
    Rem 9
    The final part of the code, Sub MakeTagList() , which is initiated by the Button, MakeTagList , brings up the file, "DailyProtableFilled.htm" , temporarily in Microsoft Word.

    _.___________

    Required Files:
    Daily Diet Protocol :
    “ProAktuellex8600x2.xlsm” :
    https://app.box.com/s/fpztob9pcp92fl6hh81zgpzumw9ntcp0 ( Current )
    https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )


    Main Makro File:
    “NeuProAktuelleMakros.xlsm” :
    https://app.box.com/s/e93u19xidygreeeenlxyupm750dxe33f ( Current )
    https://www.magentacloud.de/share/38m-zuc2y3#$/ ( April 2018 )


    Usually in practical use, the execution of the last coding would be followed by the sending of the EMail using the Send Pro Mail Button.








    Ref:
    http://www.eileenslounge.com/viewtop...=29556#p228710
    http://www.excelfox.com/forum/showth...0528#post10528

    Last edited by DocAElstein; 04-14-2018 at 04:21 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #40
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Formating Tables sent via EMail using LECMO/CDO Technologies. htm html web site range publish

    Background testing work for this Thread:
    http://www.excelfox.com/forum/showth...and-send-email
    Also related to these Threads and Blogs

    http://www.eileenslounge.com/viewtop...=29556#p228710
    https://msdn.microsoft.com/en-us/lib...toSendingEMail
    http://www.excelfox.com/forum/showth...0632#post10632
    http://www.excelfox.com/forum/showth...once#post10518


    < HTML HTM > coding
    At least until recently, the “language” most commonly used or seen in Internet and World wide web electronic communication is the “pointy bracket” type,” ( Hypertext Markup Language https://en.wikipedia.org/wiki/HTML https://www.youtube.com/watch?v=1gLh...D6F7E289625976 “ ) , which to a large part of most such coding consist of pairs of “start” and “stop” “tags” in a long string to tell something about what is within the tags, like, pseudo code example
    HTML Code:
    <AText>This might be a final text you might “see” in something like an Internet Browser which can “read” HTML coding</AText><nextInstruction>………
    or, for convenience this often is seen in a “.html” file, if viewed in the code form, like
    HTML Code:
      <AText>
      This might be a final text you might “see”…….
      </AText>
    <nextInstruction>………
    ( In a VBA code the last code string would be typically constructed something like this :
    Code:
    = “<AText>” & vbCrLf & “This might be a final text you might “ & “””” & “see” & “””” & ”…….” & vbCrLf & “</AText>” & “<nextInstruction> ….
    Or this:
    Code:
    = “<AText>” & vbCrLf & “This might be a final text you might ““see””…….” & vbCrLf & “</AText>” & “<nextInstruction> ….
    …….. )

    The extra carriage returns and / or Line feeds (vbCr , vbLf , vbCrLf ) , in the total string, as in the last code form would typically be ignored by any
    Various software is available which can read that HTML coding. The most commonly known thereof being the internet browsers most commonly available, such as Internet Explorer from Microsoft, or Google Chrome from Google. Basically an Internet Browsers main function is to receive and read the HTML coding which is typically passed in Internet and World wide web electronic communication
    So basically , if that HTML file is “fed” to something, such as an internet browser, then you, as final user/observer, would finally see something like this:

    ___This might be a final text you might “see” in something like an Internet Browser which can “read” HTML coding

    Here an example of Internet browser, Google Chrome, used for receiving Emails , using the Email provider software of gmail and German Telekom
    SeenIngmailOrGermanTelekom.JPG : https://imgur.com/Oz6z6tq
    SeenIngmailOrGermanTelekom.jpg

    Using this language you can make many other things “appear” in addition to simple text.

    Currently I am interested in extending the “seen” message developed in posts around here, gmailInputRunOnThursday7thJune bw table.JPG : https://imgur.com/Dh38G4O , to include some colouring and possibly heading information

    So I have this:
    gmailInputRunOnThursday7thJune bw table.JPG : https://imgur.com/Dh38G4O
    gmailInputRunOnThursday7thJune bw table.JPG
    But I want something more like this:
    Quote Originally Posted by Thainguyen View Post
    ...... I wonder if we can include like the picture below.
    Attachment 2061
    Equipment PM 2 .jpg
    This is basically the Excel range format in the file from which the Information comes
    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    Equipment PM
    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
    3
    4
    1
    JUKI GKG GL GL SCREEN PRINTER A123
    06.04.2018
    13.04.2018
    15.03.2018
    12.04.2018
    N/A
    N/A
    18
    137
    Juki K3 Screen printer A137
    03.06.2018
    10.06.2018
    N/A
    N/A
    N/A
    N/A
    19
    141
    Heller 1826 MK5 Reflow Oven A138
    N/A
    N/A
    13.05.2018
    10.06.2018
    N/A
    N/A
    20
    142
    NISSAN MCU-112A331.V Forklift A139
    N/A
    N/A
    N/A
    N/A
    13.05.2018
    10.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
    22
    28.01.1900
    Worksheet: Equipment PM

    _.___________________

    HTML tolerates many mistakes + Microsoft files can be saved as .htm/html type
    Here are a couple of interesting and relevant observations:
    _(i) It would appear the HTML coding is quite tolerant of mistakes or having additional code lines, or Tag pairs that it does not recognise. Sometimes a large amount of coding will be accepted, and only a small part will be read and used successfully to give a “seen” output.
    _(ii) Microsoft Word and Microsoft Excel Files can be saved as .htm extension instead of the more common extensions ( .xls , .doc , .xlsx etc…

    Because of these two things, it is possible to save a File in such a way, and then use that as the HTML coding used in the Email sending codes which are being discussed here , ( https://tinyurl.com/y8gxucsl , https://tinyurl.com/yacekv4o , http://www.excelfox.com/forum/showth...mail#post10670 , http://www.excelfox.com/forum/showth...once#post10518 )

    So, pseudo code part like this would be used

    Code:
         Dim strHTML As String: Let strHTML = The String of HTML coding obtained from a saved as .htm Word or Excel File
        .htmlbody = strHTML
    Such a technique is often done. Sometimes only a part of an Excel spreadsheet is wanted and this can also be saved in a .htm extension.
    When using this technique, a lot of extra HTML coding is present which is not needed or recognised when used for the sent Email. As noted, this does not normally cause problems.
    ( often this technique goes by the name of Publishing or range publishing )
    I personally prefer to examine the HTML coding and pick out only the information I need. This is the way I personally prefer to do it

    In the next post I will look at this last way of doing things. I will use the example of the required ….. “….I wonder if we can include like the picture below ….”……
    Quote Originally Posted by Thainguyen View Post
    ...... I wonder if we can include like the picture below.
    Attachment 2061
    Last edited by DocAElstein; 06-11-2018 at 02:33 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •