Tests and Notes for EMail Threads [Archive] - Excel, Access, PowerPoint and Word VBA Macro Automation Help

View Full Version : Tests and Notes for EMail Threads

DocAElstein

02-23-2018, 03:16 PM

Further notes in support of answer to this Thread:
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once?p=10518#post10518

Microsoft Outlook.
WTF is that and HTF do you do anything with it, and WTF is it supposed to do.
I didn't know. And still don't......
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.

But I had a go
Microsoft Outlook: what is that ( using manually )
You would normally get the software to run on its own ( visible as it were ) in a similar way to which you might get Word or Excel to start, for example
Find it single click on it:
FindOutlook Start AllProgrammes Microsoft MicrosoftOutlook.JPG : https://imgur.com/LaGs6HA
FindOutlook Start TypeInSearchBox Outlook.JPG : https://imgur.com/IbFOSHz
Make a Desktop icon from a Copy/ paste and double click on it :
MicrosoftOutlook Make a desktop Icon to double click on.JPG : https://imgur.com/ZNNPmOI
The first time you try to open it with a click or two, a set up starts.
Outlook2003Start.JPG https://imgur.com/tSQDoTe
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.
I had a go,
the start was OK:
Outlook2003Start.JPG https://imgur.com/R71pKfy
Outlook2003Start2.JPG https://imgur.com/XUFMpEm
These following steps took me a few hours of Emails, Internet surfing and annoying Telephone calls to my Internet provider before I
_ chose IMAP here : Outlook2003Start3ServerType.JPG : https://imgur.com/Jmnd6Vb
and
_ got the two required things to put in the 2 server information bars, and other stuff to fill in this : Outlook2003Start4ServerConfiguration.JPG : https://imgur.com/NXNAt9J

Von: "Doc.AElstein@t-online.de" <Doc.AElstein@t-online.de>
An: "elston, alan" <Doc.AElstein@t-online.de>
Pop3
* Serveradresse Port* Sicherheit
Posteingang securepop.t-online.de 995 SSL / TLS
Postausgang securesmtp.t-online.de 465 SSL
*
E-Mails über IMAP4 abrufen
* Serveradresse Port* Sicherheit
Posteingang secureimap.t-online.de 993 SSL
Postausgang securesmtp.t-online.de 465 SSL

From: "Doc.AElstein@t-online.de" <Doc.AElstein@t-online.de>
To: "elston, alan" <Doc.AElstein@t-online.de>
pop3
Server address Port Security
Inbox securepop.t-online.de 995 SSL / TLS
Outbox securesmtp.t-online.de 465 SSL

Retrieve emails via IMAP4
Server address Port Security
Inbox secureimap.t-online.de 993 SSL
Outbox securesmtp.t-online.de 465 SSL

Outlook2003Start4ServerConfiguration.JPG : https://imgur.com/NXNAt9J
MyTelekomNameUsernamePassword.JPG : https://imgur.com/K6qZgsE
TelekomInternetConfiguration.JPG : https://imgur.com/Z3XcsJu

Then I hit Finish:
Outlook2003Start5Fertig.JPG : https://imgur.com/wIMvqBb ´
I get an error in the left Pane atz that point or later as well sometimes :
Outlook2003Start6LeftpaneErrror.JPG : https://imgur.com/35XLQv6

could not connect to the server secureimap t online.JPG : https://imgur.com/UqEZtQe
Fehler (0x800CCC0E) beim Ausführen der Aufgabe "Suchen nach neuen Nachrichten in den abonnierten Ordnern auf secureimap.t-online.de.": "Der Download des Ordners "(null)" von Konto "secureimap.t-online.de" vom IMAP-Mailserver ist fehlgeschlagen. Fehler: Die Verbindung zum Server konnte nicht hergestellt werden. Falls dieser Fehler weiterhin auftritt, wenden Sie sich an den Serveradministrator oder den Internetdienstanbieter."

Fehler (0x800CCC0E) beim Ausführen der Aufgabe "secureimap.t-online.de: Posteingang - Auf neue E-Mail überprüfen.": "Der Download des Ordners "Posteingang" von Konto "secureimap.t-online.de" vom IMAP-Mailserver ist fehlgeschlagen. Fehler: Die Verbindung zum Server konnte nicht hergestellt werden. Falls dieser Fehler weiterhin auftritt, wenden Sie sich an den Serveradministrator oder den Internetdienstanbieter."

Error (0x800CCC0E) while performing the task "Search for new messages in the subscribed folders on secureimap.t-online.de.": "Downloading the folder" (null) "from account" secureimap.t-online.de "from IMAP mail server failed Error: Unable to connect to server If this error persists, contact your server administrator or ISP. "

Error (0x800CCC0E) when executing the task "secureimap.t-online.de: Inbox - Check for new e-mail.": "The download of the folder" Inbox "of account" secureimap.t-online.de "from IMAP- Mail server failed Error: Unable to connect to server If this error persists, contact your server administrator or ISP. "

Every time I open Microsoft Outlook after that I get a pop up : could not connect to the server secureimap t online.JPG : https://imgur.com/UqEZtQe

Es Konnte keine Verbindung zum Server hergestellt werden. secureimap.t-online.de befindet sich jetzt im Offlinemodus

It could not connect to the server. secureimap.t-online.de is now in offline mode

So I am still none the wiser, but It is worth doing all that anyway as you may need some of that information later in one or more of the ways to send an Email using VBA.

DocAElstein

02-28-2018, 12:22 AM

_1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA
Main Code , Sub PetrasDailyProWay1_COM_Way() ,
and
Function Code for solution to this Thread and Post
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once?p=10518#post10518

Option Explicit ' Daily Diet plan, Sending of Notes and an Excel File
Sub PetrasDailyProWay1_COM_Way() ' Allow access to deep down cods wollops from Microsoft to collaborating in particular in the form of messaging. An available library of ddl library functions and associated things is available on request, the Microsoft CDO for Windows 2000. We require some of these ' CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). API: interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other (Windows based usually) software’s.
' Rem1 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. At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available ====================#
With CreateObject("CDO.Message") ' Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
'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
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection. https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
.Configuration(LCD_CW & "smtpusessl") = True ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details. ' SSL protocol has always been used to encrypt and secure transmitted data
.Configuration(LCD_CW & "smtpauthenticate") = 1 ' ... possibly this also needed .. When you also get the Authentication Required Error you can add this three lines.
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "securesmtp.t-online.de" '"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 SMTP is just used to mean the common stuff..... Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 25 ' 465or25fort-online ' 465 'or 587 'or 25 ' The port of type somehow refered to by the last line
'
.Configuration(LCD_CW & "sendusername") = "excelvbaexp@gmail.com" ' "Doc.AElstein@t-online.de" ' .... "server rejected your response". AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
.Configuration(LCD_CW & "sendpassword") = "Bollocks" ' "Bollox"
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 ' Or there Abouts ;) :)
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially .. .Configuration.Load -1 ' CDO Source Defaults
'End With ' -------------------* my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof;
'2b) ' Data to be sent
'.To = "Doc.AElstein@t-online.de"
.To = "excelvbaexp@gmail.com"
.CC = ""
.BCC = ""
.from = """Alan"" <Doc.AElstein@t-online.de>"
.Subject = "Bollox"
'.TextBody = "Hi" & vbNewLine & vbNewLine & "Please find the Excel workbook attached."
.HTMLBody = MyLengthyStreaming
.AddAttachment "G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\Übersicht aktuell.xlsx" ' ' Full File path and name. File must be closed
Rem 3 Do it
.Send
End With ' CreateObject("CDO.Message") (Rem 1 Library End =======#
End Sub
Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object " Full path and file name of Word doc saved as .htm "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\ProMessage.htm"): Debug.Print FileObject
'1(iii) sets up the data "stream highway"
Dim Textreme As Object: Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2) ' reading only, Opens using system default https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
Let MyLengthyStreaming = Textreme.ReadAll ' Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
Textreme.Close
Set Textreme = Nothing
Set Fso = Nothing
Rem 2 possible additions to MyLengthyStreaming

Last bit of Function ( must go here in the excelfox Test Sub Forum in HTML Tags as there are HTML Tags in the final text string string and this makes a mess in normal BB code tags, because in excelfox Test Forum HTML is activated ) :

Rem 2
Let MyLengthyStreaming = "Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------" & MyLengthyStreaming & "-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======"
End Function

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg.9h5lFRmix1R9h78GftO_ iE)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h77HSGDH 4A)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h76fafzc EJ)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h759YIjl aG)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9h74pjGcb Eq)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg.9h5uPRbWIZl9h7165DZd jg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein

02-28-2018, 12:37 AM

Function Code for solution to this Thread and Post
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once?p=10518#post10518

HTML For CDO.Message.HTMLBody in VBA Emails sending

Linked in my Binding Function, MyLenghtyString LBF_MLS
In support of this Thread:
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once

HTM / HTML is a very typical electronic message language recognised by most software devices associated with Email and similar.
In two ways considered in this Thread , http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once?p=10512#post10512 , the main Message Text body to be sent in an Email can be supplied as a single HTML code string.

One convenient way to supply this is with a simple Word.doc file which can simply saved with a htm file extension
Word doc to htm.JPG : https://imgur.com/vhRE9CC

By opening this with a simple text editor, the actual text along with much more htm code detail can be revealed
LastBitOfProMessage htm.JPG : https://imgur.com/mT6l40I
LastBitOfProMessage htm 2.JPG : https://imgur.com/s0U8419

This is the actual text required to be given after the an Email data filling code line like:
_ .HTMLBody =

The actual file held anywhere will likely include all sorts of computery stuff in addition to that text.
We can get at just the text in several ways.
A typical way in VBA is to make use of one of a number of Object Orientated stuff held in the Visual Basic FileSystemObject Object. This is in turn part of the Bundle in the available to application programs (such as Excel VBA) Library, Microsoft Scripting Runtime

The way this works is as follows.
For a given file, a large object can be made within the Microsoft Scripting Runtime Library Class type Module like Library, ** Polymorphically speaking.
The Microsoft Scripting Runtime FileSystemObject Object GetFile method returns this object requiring only its full file path in order to “Get at it” . ( The returned object is pseudo in the streaming runtime instant direct compiling linking .Net technology held as a running link, ( indeed by assigning the object to, or using in an environment of, String will itself return that arguments string reference ) )
**:From Microsoft documentation: Visual Basic provides polymorphism through multiple ActiveX interfaces. In the Component Object Model (COM) that forms the infrastructure of the ActiveX specification, multiple interfaces allow systems of software components to evolve and break existing code.
In this sense interface is a set of related properties and methods. Much of the ActiveX specification is concerned with implementing standard interfaces to obtain system services or to provide malfunctionality to other programs.
The actual processes involved are in the meantime so messed up that it is a wonder that anything still works, and I doubt it will be long before nothing does.
The large FileObject in the Microsoft Scripting Runtime Library Class type Module like Library has information , amongst other things of neighbouring things , and as is typical in this mixed up messed up process , a short tem path or highway is made, and more often than not a “text stream object”, something like a continuous stream of data or like a highways going around in circles, and this will only be of a runtime existence, or at any rate should.. during this lifetime it can be “read”. I guess for any file of any type data within it will be recognised as such and can be handled in this simple text stream way.

The original coding goes quite a way back and does not really fit in Object Orientated Visual basic hierarchical structure of the original implementation of File I/O in Visual Basic. But it does at lest work well in getting at text stream string things which we are interested in

The available methods and the such reflect all the above…
-…So code will have a string getting section that..
1(i) makes available the Library of stuff, objects, Methods etc.
1(ii) makes the big File Object
1(iii) sets up the data “stream highway”
1(iv) pulls in the data, in our case into a simple string variable

_.____
I have decided for my requirement to use a “Function” for this, not just to house tidily the above steps, but also as I may add some additional bits from time to time too the main inner body string for my Email message, which the main function of this all is to produce.
To recap on the Function idea here ( http://www.excelfox.com/forum/showthread.php/2232-Excel-VBA-comma-point-thousand-decimal-separator-number-problem#post10503 )

In end effect I want a String. In fact in the main code in which this should be embedded has this as a variable
Pseudo, Linked in my Binding Function, ObjectLinkedEbeded Stuff
In place of an actual static linked variable_...
Dim MyLenghtyString As String
_ Let MyLenghtyString = “static linked at pseudo Compile String”

_.. I have
Function MyLenghyString(Export) As String
_ Pall MyLenghyString()_Import
_.. or Let MyLenghtyString = “direct linked runny runable library”

The end result is that in my code I will have simply pulling of

_ .HTMLBody = MyLengthyStreaming

Function Code description:
Rem 1
This uses the File System Object way discussed above to finally produce a long text string in variable _ MyLengthyStreaming _ This string probably has a of unnecessary stuff as well as the required part of the HTML code, but appears to be able to be handled and manipulated as if it were just the required part. Presumably the rest is ignored by things such as internet browsers

Rem 2
This allows for some extra simple string data to be added. If you are not familiar with HTML code then you can easily get the required string from text to HTML converters of which there are many freely available in internet
Note: If you have any “ in your required HTML string, then you will need to replace them in the given string in the VBA code with “”
http://www.excelfox.com/forum/showthread.php/2222-apply-NumberFormat#post10448

' https://support.microsoft.com/en-in/kb/186118
https://www.youtube.com/watch?v=nj8mU3ecwsM
https://www.youtube.com/watch?v=f8s-jY9y220&t=1813s

Note: ' path in code must be changed to reflect where you save .htm file
Pubic Function MyLengthyStreaming() As String

Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object " Full path and file name of Word doc saved as .htm "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\ProMessage.htm"): Debug.Print FileObject ' path in code must be changed to reflect where you save it
'1(iii) sets up the data "stream highway"
Dim Textreme As Object: Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2) ' reading only, Opens using system default https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
Let MyLengthyStreaming = Textreme.ReadAll ' Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
Textreme.Close
Set Textreme = Nothing
Set Fso = Nothing
Rem 2 possible additions to MyLengthyStreaming

Rem 2
Let MyLengthyStreaming = "Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------" & MyLengthyStreaming & "-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======"
End Function

MyLengthyStreaming = "Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------" & MyLengthyStreaming & "-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======"
MyLengthyStreaming = "Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------" & MyLengthyStreaming & "-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======"

Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object " Full path and file name of Word doc saved as .htm "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\ProMessage.htm"): Debug.Print FileObject
'1(iii) sets up the data "stream highway"
Dim Textreme As Object: Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2) ' reading only, Opens using system default https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
Let MyLengthyStreaming = Textreme.ReadAll ' Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
Textreme.Close
Set Textreme = Nothing
Set Fso = Nothing
Rem 2 possible additions to MyLengthyStreaming
Let MyLengthyStreaming = "Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------" & MyLengthyStreaming & "-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======"
End Function

Results Example:

Used htm Word File.JPG : https://imgur.com/mwihFBT
"ProMessage.htm" ( Saved from Word as .htm ) : https://app.box.com/s/cbtodk5srg76a5lowfemrdvei91mfmdq
1969

Recieved Email gmail.jpg : https://imgur.com/x0NybLa :

'.To = "Doc.AElstein@t-online.de"
.To = "excelvbaexp@gmail.com"
1972

Recieved EMail Telekom : https://imgur.com/wqPJSCt
Recieved EMail Telekom 2.JPG : https://imgur.com/o5mRkak

.To = "Doc.AElstein@t-online.de"
'.To = "excelvbaexp@gmail.com"
19701971

_.________________________________________________ ____________________________

Uploaded file had to be done as .docx to get it to upload at excelfox ( .htm were not permitted to be uploaded )
To use in code it must be resaved as .html ( ' and path in code must be changed to reflect where you save it )

DocAElstein

03-01-2018, 06:02 PM

HTML as seen in Text Editor, for this Post:
http://www.excelfox.com/forum/showthread.php/2146-%E0%A4%AC%E0%A5%8D%E0%A4%B2%E0%A5%89%E0%A4%97-%E0%A4%95%E0%A5%8B%E0%A4%B6%E0%A4%BF%E0%A4%B6-%E0%A4%95%E0%A4%B0-%E0%A4%B0%E0%A4%B9%E0%A4%BE-%E0%A4%B9%E0%A5%88-%D8%A8%D9%84%D8%A7%DA%AF%D8%B2-%DA%A9%DB%8C-%DA%A9*Trying-Blogs?p=10524#post10524

OpenProMessageHTMLWithTextEditor.JPG : https://imgur.com/4zev9Kv

ProMessageHTMLInTextEditor.JPG : https://imgur.com/eTUd17q

<body lang=DE style='tab-interval:35.4pt'>

<div class=WordSection1>

T Andale
Mono

 T Arial

T Arial Black

T Comic Sans
MS

T Courier New

T Georgia

T Helvetics

T Impact

T Tahoma

T Terminal

T Times New Roman

T Trebuchet
MS

<o:p> </o:p>

W9 Verdana<o:p></o:p>

W11 Arial Narrow<o:p></o:p>

W14 Batang<o:p></o:p>

W16 Calibri<o:p></o:p>

W18
Cambri Math<o:p></o:p>

W20 FangSong<o:p></o:p>

W22 Gungsuh<o:p></o:p>

W24 GungsuhChe <o:p></o:p>

W26 Times New Roman<o:p></o:p>

W28 Franklin Gothic Heavy<o:p></o:p>

</div>

</body>

</html>

DocAElstein

03-01-2018, 09:54 PM

Function codes discussed in this Post:
http://www.excelfox.com/forum/showthread.php/2146-%E0%A4%AC%E0%A5%8D%E0%A4%B2%E0%A5%89%E0%A4%97-%E0%A4%95%E0%A5%8B%E0%A4%B6%E0%A4%BF%E0%A4%B6-%E0%A4%95%E0%A4%B0-%E0%A4%B0%E0%A4%B9%E0%A4%BE-%E0%A4%B9%E0%A5%88-%D8%A8%D9%84%D8%A7%DA%AF%D8%B2-%DA%A9%DB%8C-%DA%A9*Trying-Blogs?p=10527#post10527

Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object " Full path and file name of Word doc saved as .htm "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\ProMessageTelekom.htm"): Debug.Print FileObject
'1(iii) sets up the data "stream highway"
Dim Textreme As Object: Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2) ' reading only, Opens using system default https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
Let MyLengthyStreaming = Textreme.ReadAll ' Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
Textreme.Close
Set Textreme = Nothing
Set Fso = Nothing
Let MyLengthyStreaming = MyLenghtyDiesScreaming_Telekom(MyLengthyStreaming) ' After this code line is done we have the string modified so that it gives the correct results in German Telekom Freemail t-online.de
Rem 2 possible additions to MyLengthyStreaming
'
'
'
'
End Function
'
' The second function below is mainly intended to make a modification to get the correct results in German Telekom Freemail t-online.de , but also the large html text not required from the start and a small amount at the end is also removed. (It does not need to be removed as it appears that it is ignored)
Public Function MyLenghtyDiesScreaming_Telekom(ByVal MyLengfyScream As String) As String ' Effectively this 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 fist the main code will be paused at this "calling" 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
Dim CntPus As Long ' A number constant for the positions of characters used in a couple of places. Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
' Take off all the first lot on unecessary required HTML
Let CntPus = InStr(1, MyLengfyScream, "<div class=WordSection1>", vbTextCompare) ' return the position (starting from the fist character , Looking in the string , for that text , doing a text comparison which is case insensitive )
Let MyLengfyScream = Mid(MyLengfyScream, CntPus + 26)
' Add to this array below all possible fonts in quotes I have to use Variant type as the VBA Array( ) Method used below pruduces a 1 dimmansional Array of Variant types. I may assing a dynamic Array of variant types to what the VBA Array( ) Function returns
Dim arsFonts() As Variant: Let arsFonts() = Array("""Andale Mono""", """Times""", """serif""", """Arial""", """sans-serif""", """Arial Black""", """Comic Sans MS""", """Courier New""", """Georgia""", """Helvetics""", """Impact""", """Tahoma""", """Terminal""", """monaco""", """Times New Roman""", """Trebuchet MS""", """Verdana""", """Arial Narrow""", """Batang""", """Calibri""", """Cambri Math""", """FangSong""", """Gungsuh""", """GungsuhChe""", """Franklin Gothic Heavy""")
Dim arschFont As Variant ' It is a required syntax that the stearing element in the For Each loop to be Variant type or Object type, ( the object type can be Object or ther specific object. if I do not specify specifically then VBVA defaults to all simialr ngs in the thing you are going through ' http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192
' Look for things like "Font" and replace the " with an arbitrary string like ScrotumSack , so "Font" becomes ScrotumSackFontScrotumSack
For Each arschFont In arsFonts() ' Loop to look for and replce each Font held in "s with the same font but in 's
If InStr(1, MyLengfyScream, arschFont, vbTextCompare) > 1 Then ' case a Font in quotes , like "font" , so for that font in quotes... and ...
Dim FontSingleScrQuote As String: Let FontSingleScrQuote = Replace(arschFont, """", "ScrotumSack", 1, 2, vbBinaryCompare) ' ...Make a that font in ScrotumSack like ScrotumSackfontScrotumSack ... and ... I use ScrotumSack arbitrarily as I find it funny and I doubt anyone else does.. does use it, so I won't have that already in the text. I cannot go straight to using the ' because if I do that now then I won't be able to distinguisch the existing ' which I want to change to " in the next bit
Let MyLengfyScream = Replace(MyLengfyScream, arschFont, FontSingleScrQuote, 1, -1, vbTextCompare) ' .... replace all "fonts" with ScrotumSackfontsScrotumSack
Else ' no arsch Font in My lengfy scream
End If
Next arschFont
' replace any ' with " This is mainly intended to replace enclosed in ' strings like askjhhsa ='kasjhScrotumSackfontScrotumSackfcb qwq 63 = Bollocks' jdgsjag with askjhhsa ="kasjhScrotumSackfontScrotumSackfcb qwq 63 = Bollocks" jdgsjag
Let MyLengfyScream = Replace(MyLengfyScream, "'", """", 1, -1, vbTextCompare)
' Scratch my Scrotum sacks, - that is to say replace them with a with ' I can do this now since the existing ' have been changeed to " so the ScrotumSacks , which were originally "s , can now be chnged to 's
Let MyLengfyScream = Replace(MyLengfyScream, "ScrotumSack", "'", 1, -1, vbTextCompare)
' take last unecessary bit of HTML off
Let CntPus = InStrRev(MyLengfyScream, "</div>", -1, vbTextCompare) ' get the position counting from the left but looking from the right ( in MyLengfyScream , of </div> , start looking from end , make text comparison which is case insensitive )
Let MyLengfyScream = Left(MyLengfyScream, CntPus - 1)
' Finally we set here what is actually returned by virtue of effectively putting something in the pseudo variable MyLenghtyDiesScreaming_Telekom
Let MyLenghtyDiesScreaming_Telekom = MyLengfyScream
End Function

DocAElstein

10-06-2019, 05:02 PM

Tests and notes required for Threads cocerning EMail and VBA

http://www.excelfox.com/forum/showthread.php/2043-PLEASE-HELP-!!-Want-to-Send-emails-by-clicking-an-email-hyperlink-in-the-excel-sheet

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein

10-13-2019, 03:07 PM

In support of this thread:
http://www.excelfox.com/forum/showthread.php/2378-Mail-the-files-by-vba-to-a-specified-email-id
( Details: https://stackoverflow.com/questions/48020578/excel-vba-cdo-mail/55451963#55451963 )

Current working ( gmail send ) pro macro, October 2019

'
Sub PetrasDailyProWay1_COM_Way(ByVal SmptySvrPrt) ' Allow access to deep down cods wollops from Microsoft to collaborating in particular in the form of messaging. An available library of ddl library functions and associated things is available on request, the Microsoft CDO for Windows 2000. We require some of these ' CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). API: interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other (Windows based usually) software's.
Call DieseArbeitsmappe1.FillMeGlobsUpMate
' Rem1 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. At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available ====================#
With CreateObject("CDO.Message") ' Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
'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
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection. https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
.Configuration(LCD_CW & "smtpusessl") = True ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details. ' SSL protocol has always been used to encrypt and secure transmitted data
.Configuration(LCD_CW & "smtpauthenticate") = 1 ' ... possibly this also needed .. When you also get the Authentication Required Error you can add this three lines.
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "securesmtp.t-online.de" '"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 SMTP is just used to mean the common stuff..... Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = SmptySvrPrt ' 465 or 25 for t-online.de ' 465 'or 587 'or 25 ' The port of type somehow refered to by the last line
'
.Configuration(LCD_CW & "sendusername") = "123456789012345678909123456@gmail.com" '"123456789012@gmail.com" ' "12345678901@gmail.com" ' "123456789012@t-online.de" ' .... "server rejected your response". AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
.Configuration(LCD_CW & "sendpassword") = "123456789012345678" ' "1234567890123" ' ' "123456789012345" ' "12345678901"
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 ' Or there Abouts ;) :)
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially .. .Configuration.Load -1 ' CDO Source Defaults
'End With ' -------------------* my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof;
'2b) ' Data to be sent
'.To = "123456@gmail.com"
.To = "123456789012@t-online.de"
'.To = "12345678901@gmail.com"
.cc = "123456789012@gmail.com"
.BCC = ""
.from = """Stinkpflopp"" <12345678901234567890123456@gmail.com>"
.Subject = "Pro für " & DieseArbeitsmappe1.LisWbProWb.Name
'.TextBody = "Hi" & vbNewLine & vbNewLine & "Please find the Excel workbook attached."
.HTMLBody = MyLengthyStreaming
'.htmlbody = ProTble
' Add all text file attachments
Dim DirTxtFl As String: Let DirTxtFl = Dir(ThisWorkbook.Path & "\" & "*.txt")
Do While DirTxtFl <> ""
If VBA.Left$(DirTxtFl, 22) = "MonatsUebersichtAnhang" Then .AddAttachment ThisWorkbook.Path & "\" & DirTxtFl
Let DirTxtFl = Dir
Loop
'.AddAttachment ThisDocument.Path & "\MallaMessage2.docm" '"G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\Übersicht aktuell.xlsx" ' ' Full File path and name. File must be closed
'.AddAttachment ThisDocument.Path & "\MallaMessage2.txt"
Rem 3 Do it initially attempt with 25 , then in Malta as well maybe with 465
On Error GoTo Malta ' Intended to catch a possible predicted error in the next line when running the routine in Malta, or/ and an error in the second attempt at a code run ' if the next line errors, then I scheduule the routine to run again with "smtpserverport") = 465
.send
On Error GoTo 0
MsgBox Prompt:="Done (with " & SmptySvrPrt & ")" ' This will typically give either "Done (with 25)" or else "Done (with 465)" if the routine worked
End With ' CreateObject("CDO.Message") (Rem 1 Library End =======#
Exit Sub ' Normal routine end for no error exceptional errected situation
Malta: ' Intended to catch a predicted error when running the routine in Malta, or/ and an error in the second attempt at a code run
If SmptySvrPrt = "465" Then MsgBox Prompt:="Also did not work with 465 , Oh Poo!": Exit Sub ' case error with attempt with 465
Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "NeuProAktuelleMakros.xlsm'" & "!'ProAktuelleMacrosMtsch.PetrasDailyProWay1_COM_Wa y ""465""'"
' On Error GoTo -1: On Error GoTo 0 ' I do not need this as the End Sub will effectively bring down the errection state
End Sub
Sub plopy(ByVal stink As String)

DocAElstein

10-19-2019, 09:32 PM

In Support of this Thread
https://telekomhilft.telekom.de/t5/E-Mail-Programme/Die-Nachricht-wurde-nicht-an-den-SMTP-Server-gesendet-werden-Der/m-p/4219080#M72050
https://telekomhilft.telekom.de/t5/E-Mail-Programme/Die-Nachricht-wurde-nicht-an-den-SMTP-Server-gesendet-werden-Der/m-p/4219080#M72050
https://telekomhilft.telekom.de/t5/E-Mail-Programme/Die-Nachricht-wurde-nicht-an-den-SMTP-Server-gesendet-werden-Der/m-p/4229774#M72110

Main procedure, ( example Calling procedure in next post )

Sub GermanTelekom_Send_FromTests(ByVal UsrNme As String, ByVal PsWd As String, ByVal CDO_Msg_Find As String) ' CDO_Msg_Find used here is a local variable belonging to this routine - it "dies" when the routine ends: it is seperate from CDO_Msg_Find in the calling routine - I could have used different names in the two routines and that would have had no effect on anything
With CreateObject("CDO.Message") ' ' -------------------* with LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = "True" '
.Configuration(LCD_CW & "smtpauthenticate") = 1
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "securesmtp.t-online.de" '
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2
.Configuration(LCD_CW & "smtpserverport") = 465 ' or 465
.Configuration(LCD_CW & "sendusername") = UsrNme '
.Configuration(LCD_CW & "sendpassword") = PsWd
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.fields.Update '
'End With ' -------------------* End with LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof
' Data to be sent
.To = "Doc.AElstein@t-online.de"
.CC = "" ' "mail2taste@gmail.com"
.BCC = ""
.From = CDO_Msg_Find
.Subject = "Hello from " & UsrNme & " using GermanTelekom_Send_FromTests" '
.TextBody = "Hi" & vbCr & vbLf & "Testing. Please ignoor this EMail"
' Do it
On Error GoTo Bed ' Intended to catch a possible predicted error in the next line when running the routine
.send
On Error GoTo 0
Debug.Print "Done " & Format(Now(), "hh mm") & " with Username: " & UsrNme & vbCr & vbLf
End With ' End With CreateObject("CDO.Message") (Rem 1 Library End =======#
Exit Sub
Bed:
Debug.Print "Fail " & Format(Now(), "hh mm") & " with Username: " & UsrNme & vbCr & vbLf & " " & Err.Number & ": " & Err.Description & vbCr & vbLf
End Sub
'
'
'
'

DocAElstein

10-19-2019, 09:49 PM

Example Calling procedure for previous post

Sub Testies_GermanTelekom_Send_FromTests() ' https://telekomhilft.telekom.de/t5/E-Mail-Programme/Die-Nachricht-wurde-nicht-an-den-SMTP-Server-gesendet-werden-Der/m-p/4219080#M72050
Dim CDO_Msg_Find As String
'_1) Some basic points: ( Relevant for when there in no < > pair in the .From string, and no Commas , )
Let CDO_Msg_Find = " f @wx"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " f n @wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " f n ""k@wxyz"" "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " f @w x " ' Fail: contradicts _1(ii)
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' -2147220979: Es ist mindestens eines der Felder "Von" oder "Absender" erforderlich, es wurde jedoch keines der Felder gefunden.

Let CDO_Msg_Find = "e g ""@wxyz "" " ' Fail: conradicts _1(ii)
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' -2147220979: Es ist mindestens eines der Felder "Von" oder "Absender" erforderlich, es wurde jedoch keines der Felder gefunden.

Let CDO_Msg_Find = "@wxyz" ' Fail: contradicts _1(vi)
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' -2147220975: Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0x800ccc6f. Die Serverantwort lautet 554 5.7.1 Database error. / This is not one of your allowed aliases -2147220975: Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0x800ccc6f. Die Serverantwort lautet 554 5.7.1 Database error. / Der angegebene Absender ist keiner Ihrer erlaubten Aliase.

Let CDO_Msg_Find = "e f 1234 g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = "e f ""1234"" g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = "This is a very long piece of text. It seems to have no effect kjsh 12345 g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = "This is a very long piece of text. It seems to have no effect Dies ist ein sehr langer Text. Es scheint keine Wirkung zu haben kjsh 12345 g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

' In the above working examples, for example, imagine an Email address such as MaxHaedroom@t-online.de ,
' Then in that case , I would see this in the InBox list , .._
' MaxHaedroom InBox.JPG : https://imgur.com/qxZyvAO , https://imgur.com/pou2a45
'_.. and this is how the Email looks
' Von_ MaxHaedroom in EMail.JPG : https://imgur.com/fXDCQO7

'_--------------------------------

'_2) Choosing the text in the InBox list.
'_2a) Using a single comma ,
'-2a)(i) Using a comma at some distant point to the right of a long text

Let CDO_Msg_Find = "12345678,9012345678901234567890 This is a very long piece of text. It ..... ..... kjsh 12345 g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

'_2b) Multiple commas

Let CDO_Msg_Find = "12345678,9012345678901234567890 This is a very long piece of te , xt. It ..... ..... kjsh 12345 g hash@wxyz "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

'2)c) The effect of quotes in the string
'2)c)(i) We no longer can have a space before the @ if we want to choose the InBox text ,... This will not error, and the Email will be sent. But The InBox will show the Email address used in the program's sendusername code line .From = " nnnnnn f "" n m n"" n @wx "

Let CDO_Msg_Find = " nnnnnn f "" n m n"" n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

' 2c)(iii) ... the position of the first quote seems irrelevant
Let CDO_Msg_Find = " n""nnnnn f n m n"" n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " n""nnnnn f n m n""n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " n""nnnnn f n m n"" n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

' 2c)(iv) The position of the second quote pair is important, the next two are OK....

Let CDO_Msg_Find = "n""nnnnn f n m n""n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

Let CDO_Msg_Find = " n""nnnnn f n m n"" n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' ... the next will result in the InBox text reverting back to the Email address used in the program's sendusername code line
Let CDO_Msg_Find = " n""nnnnn f n m n"" y n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

' 2d) effect of comer and quotes combination
' 2d)(i) If a comer appears within the pair of VBA quotes, then it is taken as a simple literal comer, rather than "text choice type" of comer discussed in 2a) and 2b)
Let CDO_Msg_Find = "hhkhk,h n""nnnn,n f n m n"" n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' In InBox list is hhkhk , h n nnnn,n f n m n

' 2d(ii)
Let CDO_Msg_Find = "hhkhk,h n""nnnn,n f n m n"" kk n@wx "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' In InBox list hhkhk ,

' 2e) Use of < > within the .From string
' Here 5 working examples for 2e)
Let CDO_Msg_Find = "hh < 9 @ > ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is hh ggt nn mk

Let CDO_Msg_Find = " < jjhh""h "" h @ kk 89 uit> "
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is ... In this case in the InBox will be the Email address used in the program's sendusername code line , ( unless the Email is recognised as one in your address book, in which case the name from your address book will be shown )

Let CDO_Msg_Find = " m < jjhh""h "" h @ kk 89 uit> n"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is m n

Let CDO_Msg_Find = "hh < 9 @ m > ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is hh ggt nn mk

Let CDO_Msg_Find = "hh ""< @ >"" ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is hh @ ggt nn mk

Let CDO_Msg_Find = "hh ""< Spammer@t-online.de >"" ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
' in InBox list is hh Spammer@t-online.de..

' Note: in the last 5 examples, after the Von: In the Email , the shown text string will appear , ( unless the Email is recognised as one in your address book, in which case the name from your address book will be shown. This is perhaps a bit unexpected, as it is replacing the shown text string with the name, rather than replacing the Email address with the name, as was noted as the case in 1) !!! )

' 2e)(v) The following two examples will error: -2147220975: Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0x800ccc6f. Die Serverantwort lautet 554 5.7.1 Database error. / This is not one of your allowed aliases -2147220975: Die Nachricht konnte nicht an den SMTP-Server gesendet werden. Der Transportfehlercode lautet 0x800ccc6f. Die Serverantwort lautet 554 5.7.1 Database error. / Der angegebene Absender ist keiner Ihrer erlaubten Aliase.
Let CDO_Msg_Find = "hh < @ x > ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)
Let CDO_Msg_Find = "hh < @ > ggt < jjhh""h "" h @ kk 89 uit> nn mk"
Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

'_------------------------------------

'Let CDO_Msg_Find = ""
' Call GermanTelekom_Send_FromTests("MaxHaedroom@t-online.de", "MaxHaedSend", CDO_Msg_Find): Call GermanTelekom_Send_FromTests("Bouncing-Tigger@t-online.de", "Tiggysending.!*", CDO_Msg_Find)

End Sub

DocAElstein

10-20-2019, 12:28 AM

Post to be added to later
( need the url first )

DocAElstein

10-20-2019, 12:28 AM

In support of this Forum Thread answers
https://stackoverflow.com/questions/58525487/excel-vba-cdo-message-email-sending-accounts-work-less-and-less
https://stackoverflow.com/questions/58525487/excel-vba-cdo-message-email-sending-accounts-work-less-and-less/59756971#59756971
https://stackoverflow.com/questions/22625292/send-email-using-cdo-no-longer-working

CDO Message Email Sending accounts work less and less…

I don't have a full solution yet, but a workable fix. This post is sharing the fix that I am currently using. I expect the problem that I have had may become an increasing one as time goes on, so someone may benefit from this answer.

There are two main parts to the solution, _1 and _2 , which I summarise below, along with some other notes, _3 …

_1 Collect and list valid Email accounts
_1 Collect and list valid Email accounts from various providers and note the various CDO Message Configuration/Settings. Do this by registering accounts and/or if possible ask people if they have any spare accounts they no longer need. It seems that older existing accounts have often a more likely chance to work. Add that data to a Calling program of the form of my Program_(i) …..

_2 Program solution
_2(i) Program_(ii)
Rewrite a typical hard coded CDO Message sending program thus: The main Message sending program should be converted to a function, taking in the Username, Password and the various CDO Message Configuration/Settings that you need for a typical CDO Message send macro. It attempts to .Send an Email with those configuration parameters. It builds up a single long string, CunFikaNation, containing all the configuration parameters of any successful accounts
_2(ii) Program_(i)
The initial calling program might in the practice be very large containing all the accounts I have collected from time to time. For demo purposes here I have done a small representative sample of the providers I have tested so far. ( I have changed/ disguised only the passwords and usernames. All the configuration parameters are those that I am using. Also, I have left the provider bit after @ in the username as it really is, so as to make it easier to see to which provider the configuration parameters apply )

In my actual work, I would add to that first macro and / or re run it from time to time to get an updated list on accounts that are likely to work successfully.

_2(iv) Program_(iv)
Finally a similar program to _2(ii) is set off by the calling program _2(iii).

_2(iii) Program_(iii)
This uses the recently obtained long string containing the CDO relevant details of all the successful working accounts, CunFikaNation , Calling Program_(vi) , each time with the next account details until a successful .Send is achieved.

In the practice, Programs _(iii) and _(iv) would be my actual real life macros and would be doing what I finally want to do. This would be sending a message contain various things from text files and excel files , both in the Email main body, and attached to the mail. In this demo version, it just sends a simple message…..

"Hi from xxxxxx@somemail.com,"
" Testing automated EMail sending. Please ignoor this EMail"

_3 Some further notes about Email Providers.
This is just based on my recent limited experience.
gmail : A few strange problems here, associated with the Less Secure Apps setting and related warnings thing.. This is reported in an answer to another recent Thread : https://stackoverflow.com/questions/58286932/cdo-gmail-macro-some-accounts-work-some-don-t-message-could-not-be-sent-to-sm/59695299#59695299
If using gmail you are going to have to get used to continually re setting manually the Less Secure Apps setting, and continually being contacted to check the validity of the account use. These problems seem to reduce if you are regularly using the account from the same location

Yandex : Somewhat random results, blocked half the time. Will often work when the program is run from the location at which the account was registered.

Yahoo : I cannot get any accounts to work

Outlook : I cannot get any accounts from here to work. In addition, the accounts when used manually are prone to problems/bugs. This seems to be increasingly typical in Microsoft stuff

AOL : I cannot get any accounts to work

Gmx : These work well, but then the accounts are often blocked. Contacting their support usually clears this problem. So it is like an indirect longer way around version of the gmail "warning/ On / Off less secure App" thing.

German Telekom : Currently consistently working the best, even with new accounts. As per Sod's Law an important function available by most providers, forwarding incoming mail, is not available, but a workaround to that works reliably. ( That is the subject of another Forum Thread answer http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11554&viewfull=1#post11554 )
( Note: German Telekom have a very quirky working .From accepted syntax. This can be exploited to make spam mails look genuine. I have done extensive test with this and reported it to German Telekom https://telekomhilft.telekom.de/t5/E-Mail-Programme/Die-Nachricht-wurde-nicht-an-den-SMTP-Server-gesendet-werden-Der/m-p/4229774#M72110 )
Spammers are also using my results very effectively.

_._______________________________

Demo macros
( In all the coding I have replaced the real EMail addresses and passwords with made up ones)

Here below is a version of the sort of thing I am talking about. In this particular demo example I am using a global variable, CunFikaNation , to store account info. (I am just doing that so as to make the demo version a bit more flexible for anyone wanting to try it. In my own work in Excel. I prefer to store things like that in a spare worksheet range, usually in a single cell

CunFikaNation is a simple string, but it has the account CDO configuration parameters separated by a space, and that information for multiple accounts is separated by a vbCr & vbLf, so you end up with a CunFikaNation string, for example for a gmail account and a German telekom account looking like this

"xxxx@t-online.de 123456789 True 1 securesmtp.t-online.de 2 465 30 xxxx@t-online.de
yyyyyy@gmail.com 12345678 True 1 smtp.gmail.com 2 465 30 yyyyyy@gmail.com"

Or in other words like:

"xxxx@t-online.de" & " " & "123456789" & " " & "True" & " " & "1" & " " & "securesmtp.t-online.de" & " " & "2" & " " & "465" & " " & "30", xxxx@t-online.de & vbCr & vbLf & "yyyyy@gmail.com" & " " & "12345678" & " " & "True" & " " & "1" & " " & "smtp.gmail.com" & " " & "2" & " " & "25" & " " & "30" & " " & "yyyyy@gmail.com"

By the way, that final form above can also be obtained from my coding. That final form can then be hard coded into a final macro. (This is one of the final solutions that I use in my final CDO message .Sending coding).
Another practical way to store that string is in a worksheet cell. In my demo coding , both the above forms are pasted out, one in cell A1 and the other in Cell B1

Just to clarify that, we are talking about like, in the case of the gmail account details, this is the configuration parameter details:

……."smtpusessl" = "True" '
……. "smtpauthenticate" = 1
' ' Sever info
…………… "smtpserver" = "smtp.gmail.com" '
' The mechanism to use to send messages.
…………"sendusing") = 2
……….."smtpserverport" = 465 ' 25 ' 587
………. "sendusername" = "yyyyy@gmail.com" '
………."sendpassword" = "12345678"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30
…………
……………. …

and the extra last parameter I use in the string is the .From , which for simplicity I am taking in the demo coding as just the Email address

.From = "yyyyy@gmail.com"

The practical use idea of my fix
The VBA strings collection Split function can be used within the third macro, firstly to split by the vbCr & vbLf to get all the accounts, and then it can be used again to split by the spaces , " " , to get the CDO message send configuration parameters for each account.

The first couple of macros, are what I would use to test out a lot of different Email accounts. I would probably add a spare account as the receiver , in other words, like in the second macro, pseudo :

.To = "AnySpareAccount@anyprovider.com"

The first few times I run the first macro , I would probably see if I can get any non working sendusername/sendpassword accounts to work. For example I would check manually the security settings of any non working accounts from gmail or Gmx to see if the first attempts have resulted in the accounts being blocked.

The final run of the first macro, will cause my full long string, CunFikaNation , to be filled with the configuration parameters of what are likely to be working accounts. ( CunFikaNation is set to "" each time the first macro is run, and also a text file with that string is made, or if already present, then overwritten with this string as its content
In my actual final files I have then
Either
a string stored in a worksheet with the configuration parameters of a lot of what are likely to be working accounts ,
or
alternatively , anther pair of macros, referenced at the end of my coding can be used to convert the entire text string from the text file into a code line which I hard code into my main CDO sending macro.

The third and forth macros are of the form of my final coding used in my final files. When attempting to send an Email , it goes through the list while a Boolean variable , VlagaMir, remains at the value of 0. That variable is Referred to in the CDO Mail send macro and is set to True as soon as a mail .Send has been successful

In the sample coding I have masked the real Email Username and Email Passwords , but the other configuration parameters are what I use. Usually in the test I did, The German Telekom accounts always work. Mostly the gmail , GMX and Yandex I could get to work , but I often need to fiddle around with them to get them to work, as mentioned in _3

There is nothing new or special in this solution , its all fairly simple coding, but it may be useful to someone in the future using the CDO Message type sending coding. My guess is that providers may have to tighten up their security and account blocking software , so relying on a single address and provider may not be a reliable solution

Alan

The coding is in the next 4 posts, ( and also here, if some wierdo their has not deleted it, https://stackoverflow.com/questions/58525487/excel-vba-cdo-message-email-sending-accounts-work-less-and-less/59756971#59756971 )

DocAElstein

10-20-2019, 12:28 AM

kxXKal

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195)
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110 (https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein

01-15-2020, 01:39 AM

( I have changed the EMail Addresses and Passwords to fake ones. But the configuration parameters are those which I am using )

[code][color=blue]Option Explicit
''_-(ii) "sendusername" , "sendpassword" , "smtpusessl" , "smtpauthenticate" , "smtpserver" , "sendusing" , "smtpserverport" , "smtpconnectiontimeout"
'Sub ScrudOverFlowDemolition(ByVal UsrNme As String, ByVal PssWrd As String, ByVal SlutPussly As String, ByVal PatheticCake As String, ByVal ServiceChef As String, ByVal WayntkerUsed As String, ByVal ConnectingDoor As String, ByVal WaitSecs As String, ByVal Snd_Frm As String)
Dim CunFik() As String ' CDO Account configuration
Dim CunFikaNation As String ' CDO Account configurations, CunFik(x)s seperated by vbCr & vbLf is "sendusername" "sendpassword" "smtpusessl" "smtpauthenticate" "smtpserver" "sendusing" "smtpserverport" "smtpconnectiontimeout"

'_- Program_(i)
' ( '_-(ii) ScrudOverFlow..("sendusername","sendpassword","smtpusessl","smtpauthenticate","smtpserver","sendusing","smtpserverport","smtpconnectiontimeout", .From )
Sub TestCall_ScrudOverFlowDemolition()
Let CunFikaNation = ""

Rem 1 Collect of accounts and their configuration parameters
' gmail
Call ScrudOverFlowDemolition("12345678901@gmail.com", "hkjhddhdkjdhakj", "True", "1", "smtp.gmail.com", "2", "465", "30", "ExcelVBAExp@gmail.com")
Call ScrudOverFlowDemolition("1234567890@gmail.com", "oqowzriqwzrizorz", "True", "1", "smtp.gmail.com", "2", "465", "30", "mail2taste@gmail.com")
Call ScrudOverFlowDemolition("1234567890@gmail.com", "sjshfskjhkjhkjh", "True", "1", "smtp.gmail.com", "2", "25", "30", "mail2taste@gmail.com")
Call ScrudOverFlowDemolition("123456789012345@gmail.com", "kjhKJDHkjdhAKJDHAD", "True", "1", "smtp.gmail.com", "2", "465", "30", "excellearning12@gmail.com")
Call ScrudOverFlowDemolition("123456789012345@gmail.com", "lKAHDKAKDHdkh", "True", "1", "smtp.gmail.com", "2", "465", "30", "JaneAmbrose1958@gmail.com")
Call ScrudOverFlowDemolition("123456789@gmail.com", "hfashsafhskjhah", "True", "1", "smtp.gmail.com", "2", "465", "30", "CDOMsgExp@gmail.com")
Call ScrudOverFlowDemolition("123456789@gmail.com", "lkasfjlkasjfalksjfaslkfj", "True", "1", "smtp.gmail.com", "2", "25", "30", "CDOMsgExp@gmail.com")
' Yandex
Call ScrudOverFlowDemolition("1234567890123456@yandex.com", "goiowxrhposfulfq", "True", "1", "smtp.yandex.com", "2", "465", "30", "developmentstest@yandex.com")
Call ScrudOverFlowDemolition("1234567890123456@yandex.com", "ahetlyqkymthpglq", "True", "1", "smtp.yandex.com", "2", "465", "30", "developmentstest@yandex.com")
Call ScrudOverFlowDemolition("12345678901@yandex.com", "9664989931a", "True", "1", "smtp.yandex.com", "2", "465", "30", "CDOMsgScrot@yandex.com")
' Yahoo
Call ScrudOverFlowDemolition("123456789@yahoo.com", "ashfasklhasha", "True", "1", "smtp.mail.yahoo.com", "2", "465", "30", "yangsfool@yahoo.com")
' Outlook
Call ScrudOverFlowDemolition("12345678901234@Outlook.com", "saslkfaslkfaslkfflksafjh*", "True", "1", "smtp-mail.outlook.com", "2", "587", "30", "MollyBrennholz@Outlook.com")
Call ScrudOverFlowDemolition("1234567890123@Outlook.com", "slshfkshfshfhs", "True", "1", "smtp-mail.outlook.com", "2", "587", "30", "excellearning@Outlook.com")
'Call ScrudOverFlowDemolition("1234567890123@Outlook.com", "lKHDhdlkdhhddh", "True", "1", "smtp-mail.outlook.com", "2", "465", "30", "1234567890123@Outlook.com") ' This line takes a long time
' GMX
Call ScrudOverFlowDemolition("1234567890@gmx.net", "asfhasklfhkfhsklfh", "True", "1", "mail.gmx.net", "2", "465", "30", "GiMiCDOMsg@gmx.net")
Call ScrudOverFlowDemolition("123456789012@gmx.net", "assjhasjkdhdkhasdh", "True", "1", "mail.gmx.net", "2", "465", "30", "Doc.AElstein@gmx.net")
Call ScrudOverFlowDemolition("12345@gmx.com", "ssshfhfasf", "True", "1", "mail.gmx.com", "2", "465", "30", "Vixer@gmx.com")
' AOL
Call ScrudOverFlowDemolition("123456789@aol.com", "kjshkjashfkjsahf", "True", "1", "smtp.aol.com", "2", "587", "30", "aliarseol@aol.com")

' German Telekom
Call ScrudOverFlowDemolition("12345@t-online.de", "khflksflksfhashffskfh", "True", "1", "securesmtp.t-online.de", "2", "465", "30", "Jayae@t-online.de")
Call ScrudOverFlowDemolition("1234567890@t-online.de", "lkklshflhffhsfh", "True", "1", "securesmtp.t-online.de", "2", "465", "30", "CDOMsgTest@t-online.de")
Call ScrudOverFlowDemolition("1234567890123@t-online.de", "kADHadhkhadhakjdh", "True", "1", "securesmtp.t-online.de", "2", "465", "30", "CDOMsgScrotum@t-online.de")
'

If CunFikaNation <> "" Then Let CunFikaNation = Left(CunFikaNation, Len(CunFikaNation) - 2) ' I do not need the last vbCr & vbLf
Rem 2 Store the final string Configuration parameters
' 2a) In the Immediate window
Debug.Print CunFikaNation
' 2b) Send CunFikaNation to a text file, ( the file will be made if it does not exist, or it will be overwritten if it does exist
Dim Highway2 As Long: Let Highway2 = FreeFile(0) [color=darkgreen]'range 1

DocAElstein

01-15-2020, 01:39 AM

lkjxALKDLKJ

DocAElstein

01-15-2020, 09:50 PM

[code][color=darkgreen]' https://stackoverflow.com/questions/58525487/excel-vba-cdo-message-email-sending-accounts-work-less-and-less?noredirect=1#comment103375857_58525487
' ' Allow access to deep down cods wollops from Microsoft to collaborating in particular in the form of messaging. An available library of ddl library functions and associated things is available on request, the Microsoft CDO for Windows 2000. We require some of these ' CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). API: interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other (Windows based usually) software's.
'_- Program_(ii) "sendusername" , "sendpassword" , "smtpusessl" , "smtpauthenticate" , "smtpserver" , "sendusing" , "smtpserverport" , "smtpconnectiontimeout"
Sub ScrudOverFlowDemolition(ByVal UsrNme As String, ByVal PssWrd As String, ByVal SlutPussly As String, ByVal PatheticCake As String, ByVal ServiceChef As String, ByVal WayntkerUsed As String, ByVal ConnectingDoor As String, ByVal WaitSecs As String, ByVal Snd_Frm As String)
'Rem1 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. At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available ====================#
With CreateObject("CDO.Message") ' Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
'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
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection. https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
.Configuration(LCD_CW & "smtpusessl") = SlutPussly ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details. ' SSL protocol has always been used to encrypt and secure transmitted data
.Configuration(LCD_CW & "smtpauthenticate") = PatheticCake ' ... possibly this also needed .. When you also get the Authentication Required Error you can add this three lines.
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = ServiceChef ' "smtp.gmail.com" ' "securesmtp.t-online.de" '"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 SMTP is just used to mean the common stuff..... Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = WayntkerUsed ' Based on the LCD_OLE Data Base of type DBTYPE_I4 , 2 will use the default account
.Configuration(LCD_CW & "smtpserverport") = ConnectingDoor ' 465 or 25 for t-online.de ' 465 'or 587 'or 25 ' The port of type somehow refered to by the last line
.Configuration(LCD_CW & "sendusername") = UsrNme ' .... "server rejected your response". AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
.Configuration(LCD_CW & "sendpassword") = PssWrd
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 ' Or there Abouts ;) :)
' Intraction protocol is Set/ Updated
.Configuration.fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially .. .Configuration.Load -1 ' CDO Source Defaults
'End With ' -------------------* my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof;
'2b) ' Data to be sent
.to = "123456789012@t-online.de"
.CC = "" ' 1234567890@gmail.com"
.BCC = ""
.From = Snd_Frm '
.Subject = "Hello from " & UsrNme & "" '
.TextBody = "Hi" & vbCr & vbLf & "Testing automated EMail sending. Please ignoor this EMail"
' add header for this Account in log text file.
Dim Highway1 As Long: Let Highway1 = FreeFile(0) [color=darkgreen]'range 1

DocAElstein

01-15-2020, 09:50 PM

LCJÖSJSÖJ

DocAElstein

01-15-2020, 09:53 PM

[code][color=darkgreen]' NOTE: This is an extra macro that can be used to fill the global variable, CunFikaNation . This can be useful in development since the global variable is often emptied. It is also useful for checking error handling in the next coding, since you can modify the text file, then refil the global variable , CunFikaNation from it
Sub GetthelastCunFikaNation()
Dim Highway2 As Long: Let Highway2 = FreeFile(0) [color=darkgreen]'range 1

DocAElstein

01-15-2020, 09:53 PM

DJJDASO

DocAElstein

01-15-2020, 09:55 PM

[code]
[color=darkgreen]' '_- Program_(iv)
Sub CDOSendMailAttempt(ByRef FlagerMe As Boolean, ByVal UsrNme As String, ByVal PssWrd As String, ByVal SlutPussly As String, ByVal PatheticCake As String, ByVal ServiceChef As String, ByVal WayntkerUsed As String, ByVal ConnectingDoor As String, ByVal WaitSecs As String, ByVal Snd_Frm As String)
'Rem1 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. At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available ====================#
With CreateObject("CDO.Message") ' Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
'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
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection. https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
.Configuration(LCD_CW & "smtpusessl") = SlutPussly ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details. ' SSL protocol has always been used to encrypt and secure transmitted data
.Configuration(LCD_CW & "smtpauthenticate") = PatheticCake ' ... possibly this also needed .. When you also get the Authentication Required Error you can add this three lines.
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = ServiceChef ' "smtp.gmail.com" ' "securesmtp.t-online.de" '"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 SMTP is just used to mean the common stuff..... Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = WayntkerUsed ' Based on the LCD_OLE Data Base of type DBTYPE_I4 , 2 will use the default account
.Configuration(LCD_CW & "smtpserverport") = ConnectingDoor ' 465 or 25 for t-online.de ' 465 'or 587 'or 25 ' The port of type somehow refered to by the last line
.Configuration(LCD_CW & "sendusername") = UsrNme ' .... "server rejected your response". AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
.Configuration(LCD_CW & "sendpassword") = PssWrd
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 ' Or there Abouts ;) :)
' Intraction protocol is Set/ Updated
.Configuration.fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially .. .Configuration.Load -1 ' CDO Source Defaults
'End With ' -------------------* my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof;
'2b) ' Data to be sent
.to = "123456789012@t-online.de"
.CC = "" ' 1234567890@gmail.com"
.BCC = ""
.From = Snd_Frm ' """Avinash_gMail_Send"" <" & UsrNme & ">"
.Subject = "Hello from " & UsrNme & "" ' "Pro für " & DieseArbeitsmappe1.LisWbProWb.Name
.TextBody = "Hi" & vbCr & vbLf & "Testing automated EMail sending. Please ignoor this EMail"
'.HTMLBody = MyLengthyStreaming
'.htmlbody = ProTble
' Add text file attachments
' make file if it does not exist, or add to it
Dim Highway1 As Long: Let Highway1 = FreeFile(0) [color=darkgreen]'range 1

DocAElstein

01-15-2020, 09:55 PM

CJKÖLSC<JKC

DocAElstein

11-15-2022, 01:45 AM

POST FOR LATER USE

DocAElstein

11-15-2022, 01:48 AM

In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916
This is the example coding used throughout the next posts

Part 1
Log in and enable 2 – Step Verification https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads?p=19038&viewfull=1#post19038
https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads/page3#post19038

Part 2 Generate Apps Password https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads?p=19039&viewfull=1#post19039
https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads/page3#post19039

Part 3 Using a gmail "Apps password" in a CDO send mail coding https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads?p=19040&viewfull=1#post19040
https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads/page3#post19040

Option Explicit
Sub SendUsinggmail()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets.Item(1)
Rem EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2
.Configuration(LCD_CW & "smtpserverport") = 25
'
.Configuration(LCD_CW & "sendusername") = "mail2taste@gmail.com" '
.Configuration(LCD_CW & "sendpassword") = "mvxyzxjfgxabcbwo" ' - This is the so called "App Pasword" '_- https://eileenslounge.com/viewtopic.php?p=300957#p300957 ' "FallyOverStkPlop.!*" ' "qkl4diPlopsyPoo.!*"
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With ' ---------------------- my Created LCDCW Library
'With ' --- ' Data to be sent------ my Created LCDCW Library
Dim strHTML As String: Let strHTML = "" & ws.Range("B2").Value & "How are you Today?"
.To = "doc.aelstein@gmail.com" '
.CC = "doc.aelstein@t-online.de"
'.CC = "doc.aelstein@gmail.com"
'.CC = "excelvbaexp@gmail.com"
'.BCC = ""
.from = """SendingFrom gmail"" <YourEMailAddresseOrAnyCrap>"
.Subject = ws.Range("A2").Value
.htmlbody = strHTML
'.AddAttachment ThisWorkbook.Path & "AnyFile.xyz"
.Send ' Do it
End With ' CreateObject("CDO.Message") -----my Created LCDCW Library
End Sub

That coding when run, will send some simple text from range A2:B2

https://i.postimg.cc/tTxWWhK0/Coding-send-simple-text-via-gmail.jpg
4615
https://i.postimg.cc/tTxWWhK0/Coding-send-simple-text-via-gmail.jpg (https://postimages.org/)

DocAElstein

11-15-2022, 11:43 AM

In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

The Problem
Previously we could use our normal Username and password for any gmail account in a CDO send mail type coding, (provided that we make one adjustment to our default account settings: We must enable less secure apps in gmail account
https://www.youtube.com/watch?v=Ee7PDsbfOUI )
However, since end of May 2022, Google gmail are usually not allowing this to be done.
https://i.postimg.cc/DwWgVM37/Less-Secure-Apps-no-longer-available.jpg
4609 https://i.postimg.cc/DwWgVM37/Less-Secure-Apps-no-longer-available.jpg (https://postimages.org/)

So if we now run a previously working CDO send mail which uses a normal free mail gmail account, then we experience the typical error which we used to get if we had not enable less secure apps:
https://i.postimg.cc/bNVTBjM6/Typical-Error-For-No-Enable-Less-Secure-Apps.jpg
4610 https://i.postimg.cc/bNVTBjM6/Typical-Error-For-No-Enable-Less-Secure-Apps.jpg (https://postimages.org/)

In this and the next and nearby posts, a solution will be discussed.

A Solution
The short answer:
There has been in parallel for some time, (I don’t know exactly how long), another way to use gmail in CDO send type codings. It finally works similar to how other Email providers have always organised these things: You need to organise a second password to be used in things such as the CDO send mail codings.

The solution often goes under the names of, or you hear often the word when discussing these things of: _ "2 step verification"_ ; _ "Apps passwword"

Here is the shortened story of what you actually do:
There are broadly speaking 2 main parts to what you do
_Part1) You must enable (Turn on) 2 step verification on the gmail account
_Part2) You must generate an "Apps password"
_3) That "Apps password" is now an extra second password for that gmail account which, amongst other things, is what you use in the sendpassword part of the configurations in a CDO send message type coding. ( In other words, you no longer use your main password in the coding. Instead use the generated "Apps password". )

Ref
https://stackoverflow.com/questions/72802522/no-longer-able-to-send-e-mails-with-vba-and-google-smtp-additional-info
https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c/54960767#54960767

DocAElstein

11-15-2022, 12:31 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

Here is the shortened story of what you actually do:
There are broadly speaking 2 main parts to what you do
_Part1) You must enable (Turn on) 2 step verification on the gmail account
_Part2) You must generate an "Apps password"
_3) That "Apps password" is now an extra second password for that gmail account which, amongst other things, is what you use in the sendpassword part of the configurations in a CDO send message type coding. ( In other words, you no longer use your main password in the coding. Instead use the generated "Apps password". )

https://eileenslounge.com/viewtopic.php?p=300957#p300957

The full detailed walkthrough story

Part 1 Log in and enable 2 – Step verification

_1) Log in to your gmail account as usual. _ https://accounts.google.com/ServiceLogin

https://i.postimg.cc/XJCfXHzS/1-Log-in-username.jpg

https://i.postimg.cc/GmKYhMkt/1-Log-in-Password.jpg

(This should be the account you want to use to send Emails using the CDO send mail type coding)

( _1b) If you are already logged into your gmail Email, then go to Manage your Google Account
https://i.postimg.cc/P5Sz78wc/1b-Manage-your-Google-Account.jpg

_2) Go to Security
https://i.postimg.cc/yxCFRmMJ/2-Security.jpg

_3) Go to 2- Step verification
https://i.postimg.cc/6pWfzhyn/3-2-Step-Verification.jpg

https://i.postimg.cc/DzDQvk5P/3-go-to-2-Step-Verification.jpg

_3b) You may probably need to log in again
https://i.postimg.cc/PfDyqxgp/3b-Possibly-log-in-again.jpg
, and on doing so you will likely be asked to make some verification of some sort or another. (Typically I use the default SMS, which then arrives on my mobile phone)
https://i.postimg.cc/1zYB8NR6/3b-Verification-for-log-in-againlog-in-again.jpg

https://i.postimg.cc/V6xFRQCk/3b-Verification-code-on-my-mobile-phone.jpg

https://i.postimg.cc/bwp3WhFG/3b-Verification-for-log-in-againlog-in-again.jpg

_4 ) The 2 –Step verification setting up proceeds

https://i.postimg.cc/s22KfJzs/4-procedding-with-2-Step-Verification-set-up.jpg

_4b) Once again we are typically asked for verification

https://postimg.cc/KKBBVjMv jpg

https://i.postimg.cc/gkvggJ4F/4b-once-again-mobile-Phone-number-verification.jpg

Add verification code https://i.postimg.cc/gkvggJ4F/4b-once-again-mobile-Phone-number-verification.jpg

_5) We finally get to turn it on

https://i.postimg.cc/Z5HVhM1w/5-Finally-get-to-turn-it-on.jpg

_5b) We are finally turned on: We have 2- Step verification enabled.

https://i.postimg.cc/br19VCxH/5b-We-are-finally-turned-On.jpg

So that’s it, finally for the first part, 2 – Step Verification is turned on

Notes:
_ Google gmail is very security conscious, and during the process you will likely get security warnings many times and likely be asked to confirm that you are the one doing things to your account. You should try to respond to these quickly, or else they may think something bad is going on and they will block your account which will throw an almighty spanner in the works and things will get very complicated!!
https://i.postimg.cc/DyVCQsrP/Flooded-with-security-Alerts.jpg

_ from now on, you will likely have a more complicated , 2 step verification process when trying to log into this gmail Email account
https://i.postimg.cc/pLRzvN53/We-now-have-always-to-make-a-second-verification-step-in-logging-in-to-the-gmail-account.jpg
https://i.postimg.cc/SR9CSkrb/We-now-have-always-to-make-a-second-verification-step-in-logging-in-to-the-gmail-account.jpg
https://i.postimg.cc/XNBwck01/We-now-have-always-to-make-a-second-verification-step-in-logging-in-to-the-gmail-account.jpg

In the next post we look at the second part, part 2 , of what you have to do, creating/ generating an "Apps password"

DocAElstein

11-15-2022, 02:07 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

Here is the story so far
We have done _Part_1) , enabled (Turned on) 2 step verification on the gmail account

We now need to do _Part2) generate / create an "Apps password"

_Part2) generate / create an "Apps password"

The start point is getting logged into the gmail account that you want to use in the CDO send Mail coding. ( see sections _1) and _2) from the previous post https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads?p=19038&viewfull=1#post19038
https://excelfox.com/forum/showthread.php/2380-Tests-and-Notes-for-EMail-Threads/page3#post19038 )

Go to App passwords ( Note it shows None, because we have not got any yet )
Click on the file >
https://i.postimg.cc/t4LsFjJm/Go-to-App-passwords-click-on-the-file.jpg

As ever, we will be asked to verify https://i.postimg.cc/jSWWW5x7/verify-again.jpg , and I usually choose to get an SMS on my mobile phone
https://i.postimg.cc/BQ7LQD03/verify-again-I-choose-sms-on-my-mobile-phone.jpg
https://i.postimg.cc/g2rhvsDr/verify-again-I-chose-and-got-sms-on-my-mobile-phone.jpg
https://i.postimg.cc/sfcGqqHv/verify-again-I-chose-sms-on-my-mobile-phone.jpg

We now need to make some selections
https://i.postimg.cc/pr2hyJrW/We-now-need-to-make-some-selections.jpg
I chose Mail and Windows Computer – that was a bit of a guess on my behalf. I am using CDO mail send coding on my LapTop.
https://i.postimg.cc/nr0sm99v/make-some-selections-Mail.jpg
https://i.postimg.cc/j56nsFs3/make-some-selections-Windows-Computer.jpg

I create ( generate )
https://i.postimg.cc/B6f1f0G1/I-create-Generate.jpg

I have a 16 character code Generated for me
https://i.postimg.cc/KYMgWYC2/I-have-a-16-character-code-Generated.jpg
Note: It is probably advisable to copy this and keep it stored safely somewhere for future reference.
It should be a 16 character text string, probably looking something like this
nvutkxjfkxyzdbwo

That’s it. That is the so called apps password
https://i.postimg.cc/nh5DTmR2/Thats-it-finished.jpg

We are now ready to use this newly created (Generated) app password in a CDO send mail coding

In the next post, Part3, we will use this newly created (Generated) app password in a CDO send mail coding

DocAElstein

11-15-2022, 04:39 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

Here is the story so far
We have done _Part_1) , enabled (Turned on) 2 step verification on the gmail account
and
_Part2) generate / create an "Apps password"

This next part, Part3, we will use this newly created (Generated) app password in a CDO send mail coding

Part 3) Using a gmail "Apps password" in a CDO send mail coding

We have already done the hard work. Now all we have to do is go to any CDO send mail coding that had previously worked using the normal password, and now replace that password with our newly created Apps Password
https://i.postimg.cc/c1XvvtnF/The-sendpassword-should-be-the-App-Password.jpg 4611
https://i.postimg.cc/c1XvvtnF/The-sendpassword-should-be-the-App-Password.jpg (https://postimages.org/)

If we now run that coding, we should now have success.
In that example coding, I was attempting to send an Email to my Google freemail gmail.com account and my German Telekom freemail t-online.de account,
https://i.postimg.cc/0yJjXtH4/Sending-on-my-gmail-and-telekom-accounts.jpg 4612
https://i.postimg.cc/0yJjXtH4/Sending-on-my-gmail-and-telekom-accounts.jpg (https://postimages.org/)

and, Bingo! – it worked:

https://i.postimg.cc/d1wbd1yB/Arrived-at-my-German-Telekom-freemail.jpg
https://i.postimg.cc/Dzkkv5fR/Arrived-at-my-gmail.jpg
46134614

https://i.postimg.cc/d1wbd1yB/Arrived-at-my-German-Telekom-freemail.jpg (https://postimages.org/)

https://i.postimg.cc/Dzkkv5fR/Arrived-at-my-gmail.jpg (https://postimages.org/)

Ref
https://stackoverflow.com/questions/72802522/no-longer-able-to-send-e-mails-with-vba-and-google-smtp-additional-info
https://eileenslounge.com/viewtopic.php?p=300925#p300925

DocAElstein

11-15-2022, 04:39 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916
https://eileenslounge.com/viewtopic.php?f=27&t=38916&p=301029#p301029

Remove Apps Password and turn off 2 – Step Verification

Remove Apps Password

https://i.postimg.cc/44v6h3CJ/Manage-your-Google-Account.jpg
https://i.postimg.cc/qqG2f1ct/Security.jpg
https://i.postimg.cc/0QDpnkb0/App-passwords.jpg
https://i.postimg.cc/g22HpbTG/Log-in-to-verify-its-you.jpg
https://i.postimg.cc/bwVHNV7k/Verify-that-its-you-Text.jpg
https://i.postimg.cc/fbFK5q5D/verification-code-arrived-on-Mobile-Phone.jpg
https://i.postimg.cc/rFc9hrG0/Verify-that-its-you-give-code-recieved-on-Mobile.jpg
https://i.postimg.cc/ZnzLz91Q/Delete-App-password.jpg
That’s it (https://i.postimg.cc/43KvKbGM/app-password-revoked.jpg )

Turn off 2 – Step Verification

https://i.postimg.cc/44v6h3CJ/Manage-your-Google-Account.jpg
https://i.postimg.cc/qqG2f1ct/Security.jpg
https://i.postimg.cc/tgL3FZCg/Signing-into-Goole-2-Step-Verification.jpg
https://i.postimg.cc/g22HpbTG/Log-in-to-verify-its-you.jpg
https://i.postimg.cc/bwVHNV7k/Verify-that-its-you-Text.jpg
https://i.postimg.cc/fbFK5q5D/verification-code-arrived-on-Mobile-Phone.jpg
https://i.postimg.cc/rFc9hrG0/Verify-that-its-you-give-code-recieved-on-Mobile.jpg
https://i.postimg.cc/x8G3ffQ1/TURN-OFF.jpg
https://i.postimg.cc/8kLHBHvg/TURN-OFF.jpg
That’s it

DocAElstein

11-15-2022, 04:39 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

<s.,cnsnc

DocAElstein

11-15-2022, 04:39 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

nndbds

Ref
https://stackoverflow.com/questions/72802522/no-longer-able-to-send-e-mails-with-vba-and-google-smtp-additional-info
https://eileenslounge.com/viewtopic.php?p=300925#p300925

DocAElstein

11-15-2022, 04:39 PM

…continued … In support of this forum post
https://eileenslounge.com/viewtopic.php?f=27&t=38916

ncsnss

Ref
https://stackoverflow.com/questions/72802522/no-longer-able-to-send-e-mails-with-vba-and-google-smtp-additional-info
https://eileenslounge.com/viewtopic.php?p=300925#p300925