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
5 Attachment(s)
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
Attachment 1977Attachment 1978
( 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
Quote:
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
Attachment 1974
“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
Attachment 1975
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
Attachment 1976
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..
5 Attachment(s)
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
Attachment 2022
( 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
Attachment 2020
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
Attachment 2021
_.................
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
Attachment 2022
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
Attachment 2021
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
Attachment 2023
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
3 Attachment(s)
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
Attachment 2024
_ Type in box bar _ cmd _ if not already shown
cmdInBoxBar.JPG : https://imgur.com/L9saGzj
Attachment 2025
_ Press _ Ctrl+Shift+Enter _ to start an "elevated" command prompt.
ElevatedCommandPrompt.JPG : https://imgur.com/W1p6frJ
Attachment 2026
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.
2 Attachment(s)
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
Attachment 2053
_ 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
Attachment 2054
_.___________
_ 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
3 Attachment(s)
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
Attachment 2068
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
Attachment 2066
But I want something more like this:
Quote:
Originally Posted by
Thainguyen
Attachment 2067
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