Continued from last post…
So I had a go at all the 5 ways discussed in the last post…
First I spent quite a while researching all the info…
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.
Here is the current list:
http://www.excelfox.com/forum/showth...0514#post10514
I will update it from time to time
I made a start, and share what I have done, for fun, but it is a bit biased towards what I want to do
What I am doing.
I am looking to do the following in a fairly direct way, and preferably in a few different Ways
Prepare a message in Microsoft Word. Some simple formatting may be present. That formatted message will be sent per EMail with an Excel File.
To Clarify: The Excel file is sent as an attachment. The Word File is NOT sent: I prepare my message conveniently in Word using formatting if required. I want the receiving person to see in his Email main body something similar to what I have prepared in the Word document
The first way is probably the most “Deep down fundamental way”.
The second way is a “bit further up”. Some of the basic stuff discussed in the first way is therefore applicable/ relevant to the second way also.
_1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA.
Brief Code Description:
General overview
This way is With the Created Microsoft CDO (Collaboration Data Objects ) object library available in VBA.
and
‘With my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof;
http://schemas.microsoft.com/cdo/configuration/
LCD 1.0 Library :
The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running “runtime” usage / at an instance in time , “instance of” ) orientated goes to the name of Object Linking and Embedding.Code:lcdSMTPAuthenticate lcoBasic AliAs smtpauthenticate 1 lcdSMTPUseSSL AliAs smtpusessl lcdSMTPServer AliAs smtpserver "securesmtp.t-online.de" ' Sever info '"smtp.gmail.com" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de" 465 lcdSendUsingMethod lcdSendUsingPort AliAs sendusing 2 lcdSMTPServerPort AliAs smtpserverport lcdSendUserName AliAs Doc.AElstein lcdSendPassword AliAs Bollox lcdSMTPConnectionTimeOut AliAs smtpconnectiontimeout AbOut 30 or thereAbOuts thereof; https://brettdotnet.wordpress.com/2013/06/06/send-a-gmail-email-from-excel-using-cdo/
At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. (Collaboration Data Objects / Linking Configuration Data Cods Wollups )
An important object there goes by the name of Message.
As its name suggests this object is primarily concerned with electronic messaging, Emails.
Full Code description
Code is here: http://www.excelfox.com/forum/showth...0516#post10516
And here : https://pastebin.com/HUuXSvsR
Sections Walkthrough
'Rem 1) Library made available / referencing the required object
This is general stuff, but I am doing this a bit differently to most of the codes I found, so I explain the different ways that I know about. I am doing a form of Late Binding
There are two main ways, explaining with my example, within Excel.
First it should be said that regardless of which way is done , the external things such as the API codes are still used in the ddl way so are imported at run time.
_1 Early Binding
To use the stuff in Early Binding you have to do something extra. The “extra” thing done by early Binding I expect mainly organises the stuff in a Class Module type way. The most noticeable difference then is that as is the case when you build your own class module , you get the intellisense assistance
The extra thing to be done is adding the reference ( In our case -- Tools --- references ---- scroll down and check Microsoft CDO for Windows 2000 )
In layman’s terms we made Excel aware of the library of stuff
1a) Basic Early binding ( using a variable ) “Method Set”
Dim Msg As CDO.Message
_ Set Msg = New CDO.Message
The first line effectively makes the variable of the type required, and the second effectively fills the variable creating an actual instance of it. Instance in this respect means that everything possibly with the thing is made available. The Dim is done at compile and so Excel VBA Knows about / is aware of it allowing intellisense. The Set effectively “makes it live.”
1b) Auto instancing Early binding “Method Dim” ( using an auto instancing variable )
Dim Msg As New CDO.Message
Excel VBA knows about the type once again. But the instance has not been created. VBA is written generally in such a way that if it “knows” about a type but does not have it instance , it
_(i) has no record of it being instanced, will always check on encountering the variable,
and
_(ii) if it has not been instance then it will do so. ( But nether the less on encountering it will still check every time.
_2 Late Binding.
This could be useful for sharing if you are not sure of what libraries someone has available. You do not make the reference check on the Library.
You use a VBA function ( CreateObject( ) ) which at run time makes the instance of the type
2a) Variable way.
Dim Msg As Object
_ Set Msg = CreateObject(“CDO.Message”)
Because of the use of a string argument the compiler is not interested in the stuff there. The function is then written such as to search for the object of the class CDO.Message.
2b) With End With way
This works similarly to 2a) but removes the need for a variable. You can see how this works with my code.
I chose the last way 2b) in my code as I had never seen it done this way in an Email with VBA code, so I am hoping there might be a good reason for that so when something goes badly wrong because of that in my code then I might learn something useful
‘Rem 2 ‘ protocols / conventions for interactions in Electronic Messaging
There are broadly 2 parts to this:
_Some deep down technical stuff
and
_ the data to be sent and format / conventions for passing that thereof
'2a) ---- … This section provides the configuration information for the remote SMTP server …
The technology and coding used goes back almost 20 years and had a lot to do with early attempts at internet connections. When adding the information required some long repetitive path strings are often seen possibly just because it was never tidied up because people were scared by the Elves. There are some constants available in the Early Binding case which hide these. I wrapped then up in my own Library available through the Linking Configuration Data_Cods Wollups, LCD 1.0 Library, Interface using my object, LCD_CW = “http://schemas.microsoft.com/cdo/configuration/”
I doubt anyone really knows WTF is going on here, but the required information sounds reasonable required infomatiuon. Some further notes on the individual parts are in the ‘Comments
'2b) ' Data to be sent
This section is fairly self explanatory. The conventional data from the user used in an Email is given in the required syntax.
The only point of particular importance to note here is the addition of the Email main Body text.
This can be specified as simple text.
However, the computer language used mostly for the transmission of data, at least until more recent years was HTM ( https://en.wikipedia.org/wiki/HTML ). Almost all things reading transmitted electronic messages will recognise this.
The option is available to provide the main text body in this language as a single text string.
The tools available for producing this language are extensive.
Amongst these tools is the possibility to save a standard Word document in a form of HTM. This can further be converted to the simple main “inner body” text part such that what you “see” in a Word document can be isolated in the HTM language form which you can then give as the Email main body text which then in most receiving Internet browsers should reproduce fairly closely the seen format in the final received Email.
It is convenient to put that final MTM language single string in a functionalled variable, strWordHTMInnerText, which is then given in the code.
Details to produce that are given here :
http://www.excelfox.com/forum/showth...0517#post10517
Rem 3 Do it
Finally the execution takes place with the
_ .Send
It is not always obvious that something is happening, but an indication can be seen from the _ [Active] _ typically shown in the VB Editor window title:
Code[Active].JPG : https://imgur.com/poib3Zp
_.____________________________
Note:
If using gmail you will likely need to check / adjust some security settings. The first few You Tube Videos show this clearly.
See YouTube Video list : https://tinyurl.com/y74cez9n
https://www.codeguru.com/csharp/.net...-Using-CDO.htm
https://www.rondebruin.nl/win/s1/cdo.htm
http://www.paulsadowski.com/wsh/cdo.htm
_2 ) Way 2)
Office Software.. one of ‘em
Microsoft Outlook ( an Active X Out Of Control )
The main use of the Outlook software is “to do Email stuff”, so usually you will have at least one Email account “registered in it” You can do this at the set up or later.
The internet is full of stuff on this, but there is no clear explanation of what it is or what it should do or how you do anything with it.
I had a go , and here are some notes on my failed attempts:
http://www.excelfox.com/forum/showth...0513#post10513
After many hours I was still none the wiser, but it is worth doing all that what I did ( whatever it was ) , anyway as you may need some of that information later in one or more of the ways to send an Email using VBA.
The internet is flooded with Blogs and code to use Outlook from within Excel VBA to send Emails.
The codes are incredibly short and simple.
Looking at my previous code for the CDO way , _ Sub PetrasDailyProWay1_COM_Way() _ , then the code to use Outlook from within Excel VBA to send Emails is basically the CDO way code without the part
This is reasonable: All this set up information is somehow already in the Outlook on your computer.. Approximately we are saying that all the “From” information , sender addresse, password etc.. is already held somewhere and the code access that..Code:'2a) 'With --------------------* my Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof; http://schemas.microsoft.com/cdo/configuration/ ......This section provides the configuration information for the remote SMTP server
But: – there is the catch…
A very important point overlooked by many, even many authors of published code is that:
You must have a “working” Outlook set up.. I don’t. I could not figure out how to it.
So I currently do not think it is possible to use this for a “stand alone” way to use Excel VBA to automate Email activity
Almost half the authors of published codes that I spoke to were not actually aware of this. By co incidence they had “working” Outlooks on their computer, but has intended their codes as “standalone” VBA Excel codes just using the Outlook Libraries.., A couple decided to re read their Blog comments after I enlightened them….
In short: If you already are very competent in Outlook and use it regularly without VBA, then doing things in it with VBA ( from Outlook, Excel, Word ) is extremely easy and just requires a few code lines and the information / Blogs on the internet are extensive. If you are not confidently using Outlook manually, then way 2 is not really a realistic option
_._______________________
Ways 1 and 2 Conclusions
So that covers the first two ways.
The first, CDO way , works very well.
The second , Outlook way , isn’t really a way in the sense of what is being considered here.
Most people using way 2 successfully had no idea what they were doing.
Way 2 is like saying you are using Excel VBA to do some very simple actions on a spreadsheet, but, for some reason you have to make a big complicated Excel spreadsheet requiring all sorts of excel options set up with information that you need a lot of general Computer knowledge to do with all sorts of complicated stuff outside Excel
Reply With Quote
Bookmarks