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/...95299#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/showth...ll=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...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
Code:
"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:
Code:
"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:
Code:
……."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
Code:
.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 :
Code:
.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
Reply With Quote
Bookmarks