Using Excel VBA to send emails from my Gmail account

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

@ABabeNChrist
Hi
Its now just after 10.00p.m (German time) on Friday, and I have had no response from you in the meantime.
So presumably you were not available or could not make contact for some reason

I have made no attempt to use the gmail account that you passed on to me by private message, because I had no response to my requests, yet.

I can probably be available on the computer for some time next week. So if you still want some help then perhaps in the meantime you can respond and tell me at what times you can be available

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

Hi ABabeNChrist
Thx for the private message.
I will aim to try to log in with that gmail account you gave me per pm, Tomorrow, at approximately 8.00pm Monday evening, 21 November, (German time). So that will be about 1.00 p.m. in the afternoon, I think in Texas.

_.____________________________

In addition to that, just as another simple experiment, I will pass you now, per private message, a link to a file that I just made, SendUsingGermanTelekomsFromTexas.xls
That is a very small file with just two simple macros in it. Those macros are very similar to those from Hans and me that we have already discussed in this Thread so far, but in that file, the Email addresses and passwords are real ones! – so please do not share that file with anyone else!
I am using one spare German Telekom Email account that I have used many times over several years in such codings. Those accounts have worked successfully in such codings to send Emails to me in Germany, whilst the computer running the codings were in various parts of the world.
I am also using another German Telekom Email account which I just made for you.
You can run those macros anytime that you wish, but perhaps leave a bit of time in between running each one, maybe at least half an hour. If they work, then they will send me a short Email.
( If you wish you can uncomment the code lines of '.CC = "AnyOtherEMailaddress@EMailing.com" and put any Email address you like there, but please don’t use the gmail account that you gave me per pm. We will talk again about that account after I have made some attempt to log in to it here tomorrow, Monday. )




Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

ABabeNChrist wrote:
21 Nov 2022, 12:02
...... I will also try the German Telekom code on my end.
OK, that worked! Good news.
I got both EMails. They arrived at my end at approximately 1.06p.m. German time.
https://i.postimg.cc/sDhCn6zN/Emails-ca ... -Texas.jpg
Emails came from Texas.JPG




I am away now from the computer, and will look in again later this evening (German time) , as planned


Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

OK I tried using the workbook you provided. I first tested as is and it seemed to work perfectly, I then changed out your sending email address with mine and I got an error message on the “Send” line (view error message below). I then tried again using your email address and then added my email address to the '.CC = AnyOtherEMailaddress@EMailing.com. I used both a yahoo and a Gmail email account and it seemed to work. It appears that it doesn’t like to use Gmail addresses (free or paid ones) as a sender.
sample email.jpg
You do not have the required permissions to view the files attached to this post.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

ABabeNChrist wrote:
21 Nov 2022, 12:34
... appears that it doesn’t like to use Gmail addresses (free or paid ones) as a sender.
Hi
Gmail does work sometimes in these CDO send codings. But from my experience, in the past, 2 things can make it difficult to use:
_(i) Sometimes different settings are required when used in different countries. There are two settings that I am talking about here, both: in the account settings; and also in the various coding CDO coding configuration parameters.
_(ii) Gmail is very security conscious. If you have not used the account for some time, or if you are using a different computer, or if you are at a another location using a different IP address, or if you are doing anything else untypical or unusual, then Gmail will initially prevent any use of the account in such coding and will send you somewhere a security warning. Provided you answer any security questions correctly, or confirm that you are using the account, then a second attempt to use the account in the same conditions may work.
So you see, even if we get the coding working with a Gmail account , you can expect that it might not always work first time in the future.

_._______________________

Anyway, as agreed , I will attempt to log into the account you made for me in about 15 minutes time.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

I just received a verification # texted. View private message

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

I tried that number you gave per pm. It appears to be incorrect. Usually there are 6 numbers, not 5 as you gave me
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

OK, the corrected number you re sent me has worked!
I have logged in with one computer.
I will try another computer

So you may or may not get another request shortly
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

OK.
I am now logged in on two computers. (It did not need any further confirmation or code)
I will do some experimenting now. If all goes well, I should not need you anymore today. I will investigate, and probably write some coding for you. I will report back again tomorrow, Tuesday.

You probably will not get anymore requests in the meantime from me or GMail, but if you unexpectedly do, then please do as you have done before, confirm anything, and send me any codes you get.


Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

Hi
( I changed temporarily the phone number for verification on the gmail account you gave me, so as to make it easier for me to work on the security settings: otherwise you would have been bombarded with security messages during my experiments).
I will be mostly away now from the computer or my telephone until later this evening, German time, ( Mid afternoon Tuesday 22 November , Texas time.)

For the time being, please do not attempt to log into ( manually ) the gmail account that you made for me, and if you do get any notifications or security messages, then just ignore them for now. ( I may occasionally try something with the account in the meantime ).

_.____________________________________________-

So today’s experiment: ( Or later if you prefer – it is not a good idea to rush these experiments anyway ### )
I am sending you another workbook, CDOSendMailTexasRange.xls , by private message**. There are a few macros in the file, but just two are of interest to us for now.

Sub TexasOverFlowDemolition( ByVal Usr_____ config parameters ….____ ) , is very similar to all the test macro Sub routines that we have discussed so far. But it is converted to a Sub routine taking arguments, ( so it works a bit like a Public Function). The arguments it takes are Email username and passwords and typical CDO send mail .Configuration parameters.
Here is a screenshot of that sub routine: https://i.postimg.cc/GtFyJ3Vb/Sub-Texas ... lition.jpg - As you can see it has variables to fill in the various .Configuration parameters

So the idea is that we can try out different Email addresses and different CDO mail send .Configuration parameters. So that sub routine needs to be called by a normal Sub routine, which I have written called Sub TestCall_TexasOverFlowDemolition() https://i.postimg.cc/hGYTK5VR/Sub-Test- ... lition.jpg

A text file, called something like TexasOverFlowDemolition____.txt is filled to tell us what worked and what failed.
The text file, TexasOverFlowDemolition____.txt, is created and put in the same folder as where you put the Excel workbook: https://i.postimg.cc/63GRqfh2/Text-File-Report.jpg

So your job to do:
So I would like you to run the sub routine, Sub TestCall_TexasOverFlowDemolition() , https://i.postimg.cc/BnM2V666/Run-Sub-T ... lition.jpg , and then give me a copy of the text file made.
That text file will have real Emails but no passwords in it. Still it is probably best to pass that to me somehow through a private message or attached to an Email to me.
Alternatively, you could just copy the text from that text file, and post that text to me via private message.
A third alternative: the macro also prints the text out to the Immediate Window. https://i.postimg.cc/MK071tJ1/Debug-Pri ... output.jpg - So if you have the Immediate Window open when you run the macro, you can see what is going on, and then copy the information that I need from there

( As before, you can add any extra .CC code lines, so as to have any working Email sends arrive at any Email address you like )

Once I have that text file we can think about what to do next.

( **The workbook CDOSendMailTexasRange.xls has a lot of real Email usernames and passwords, so please don’t share that with anyone. )

_._____________________

A few extra things to note:
### Email providers I am finding are getting increasingly security conscious. (I suppose they have to in order to fight the "bad people", Spammers and the such). Because of this, it is a good idea to take your time and do not do too many experiments too quickly. Otherwise if you rapidly repeat doing things, then a provider may get suspicious and put a temporary ban on an account. Things get very messy then when we try to get the ban lifted.
So try to avoid running the test macros too often in rapid succession. Don’t be too hasty, - in any case I may not be able to look in again until late tonight or tomorrow, so don’t rush to do things.



Alan



_.__________________________________________

Edit, P.S. If anyone else is interested to see if these CDO send mail codings work for them, from their country, then let me know and I will pm a link to the latest workbook. It might be interesting to see how consistently they work.

( But I wont share the workbook with you if you have a username like .. MemberThatJustJoinedToGetAlansEMailPasswords )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

When running the macro as is, it continues to run (or loops) and does not stop. I had to use task manager to stop.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

There is no looping involved in the coding,
but, it can, sometimes hang, for a very long time.
Best to wait until you do not need your computer for a while, and then leave it running.

( If you have the Immediate Window open when running, then you will see at what EMail account attempt, ( at which Call code line ), that it is hanging.
You will see where it hangs , because after every run of Sub TexasOverFlowDemolition( ByVal Usr_____ config parameters ….____ ) ( after it is Called by Sub TestCall_TexasOverFlowDemolition() ), the result of fail or success is shown in the Immediate window( Not done or done )
If it always hangs for a very long time at any Call code line, then you could 'comment out that Call code line in Sub TestCall_TexasOverFlowDemolition(). But best is to let it run to the end. It will almost always complete, but may take occaisionaly a long time if it hangs for a long time at one Call code line )

Remember to clear the Immedite Window before each new run of Sub TestCall_TexasOverFlowDemolition()

You should also see some results in the text file, unless it hanged on the very first Call code line in Sub TestCall_TexasOverFlowDemolition(). If that is the case, then you could, for example, swap the order around of the Calls in Sub TestCall_TexasOverFlowDemolition() and put the German Telekom Call lines at the start, since we think they should work

But please, as I said before, do not get over excited and do many re runs too quickly, or else accounts will start getting banned as suspected spamming, and we will get in a terrible mess

Once again, best is to wait untill your computer is available for a long time, and then leave the coding running
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

Got it thank you

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

Sorry I forgot to mention about the hanging before. I was not sure if it was an issue with my older computers.
(Have you obtained any results in the text file so far?
Note: the text file is added to each time for every run, it does not get overwritten each run. So the results of all attempted runs will be there)
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

I emailed you the results

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

Thx, I saw them.

OK, the results so far in the text files that you sent to my gmail personal EMail look very encouraging.
Indeed, it looks as though the last run completed, ( unless you already swapped any code lines around )

So we can afford to be patient.
Maybe just do one more run as late as you can today, then when you are sleeping, I can take another look tomorrow morning.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

Will do

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

Hi,
The codings generally seem to be working very successfully – the sent .To addresses have received many hundred Emails from our running of the test codings, and none were detected as spam by the receiving Email address, yet… https://i.postimg.cc/VNRrTmLW/Many-EMai ... ddress.jpg
So that’s good news


_.__________________________________

So thoughts on a final working coding for you
I would recommend that you use a coding like the previous one we have tried.
Sub TexasOverFlowDemolition( ByVal Usr_____ config parameters ….____ )

Such a macro can be called a Function or a Sub routine. It makes no difference to change that last macro to
Function TexasOverFlowDemolition( ByVal Usr_____ config parameters ….____ ) As ____
If you made that change, there would be no change to the working of it.
But, If we call it a Function , then it can return something. For example like
Function TexasOverFlowDemolition( ByVal Usr_____ config parameters ….____ ) As Boolean can return us True or False
We could use that to tell us if the .Send was successful or not

I have done a slightly modified main coding in a function form:
Public Function TexasRange( ByVal UsrNme As ………Frm As String ) As Boolean
https://i.postimg.cc/Px9jMXRq/Public-Fu ... -Range.jpg

The way I organise these sort of things is to have a few EMails to try in a loop until one is successful, something like I have done in
Sub TestCall_TexasRangeCDOSendMailAttemptS()
https://i.postimg.cc/RhtzskkL/Sub-Test- ... empt-S.jpg
I have chosen the Email addresses I used based on
_ the results of your recent testing.
_ the results of my recent testing
_ my experience from the last few years.
I think a solution like that is quite good and has been very reliable for me. Its easy to add Email addresses, change Email addresses, change order tried of Email addresses, 'comment any Email addresses out from time to time, etc. etc. ( by Email addresses, I am meaning here the ones used in the .Send code line. The Emails used in the .Send code line, are the ones that often lead to problems, so having a few to try works very well as a final Solution, IMO )


You may possibly prefer to just try a single gmail address, as was your original Thread title and request. ….send emails from my Gmail account …..
So you could try this version of the Calling sub routine, which just uses a single gmail account.
Sub TestCall_TexasRangeCDOSendMailAttempt()
https://i.postimg.cc/j5LTNwv9/Sub-Test- ... ttempt.jpg
I would try it initially, as it stands, because it is using a gmail account that has so far worked the best for both of us.
Then you could experiment with a single gmail account of your choice. ( You will need to change those first two .Configuration parameters which are the Username and App Password
https://i.postimg.cc/G2m8nV4w/gmail-Use ... meters.jpg

_._____________

So for now I will pass you a workbook, TexasRangeCDOSendMail.xls , by private message with those new three macros.

See how you get on with them. ( I will be mostly away now until tomorrow, I doubt I will be able to respond again today )

As ever, please don’t share that workbook, as it has some real Email and Passwords in it


You may have enough info now , but I am happy to help further if you pass any questions or requests in the meantime. ( I have not added the bit to add an attachment. You can probably figure that out from what Hans did for you

_.________________________

Some other small points:
_The smallest of the text files that you sent me at my private gmail address, shows you successful .Configutation parameters. You can copy any of those lines and paste it in to a spare code line here: https://i.postimg.cc/ZRs9bHN9/Add-a-set ... e-line.jpg , which is basically what I did, and also what I do, generally:- What I tend to do, generally, from time to time, is run a set of tests as we did yesterday, using some new Email accounts, then any successful .Configutation parameters I add to my codings. I also tend to swap the order around a bit, mainly so that a different set of .Configutation parameters is tried first. That reduces the likelihood of an account being blocked or banned in the future as suspected spamming

_ As ever, when you experiment with these codings do not re try codings too quickly in succession
( You will most likely never be able to use such codings to rapidly send lots of Emails: Most Email providers are getting increasingly security conscious in the fight against spammers and will usually block temporarily or ban permanently an account that rapidly is sending Emails )

_ One important thing just in case you may not have realised. You can fiddle around with what the recipient sees as where/ who the Email is coming from. So it is less important what Email account you actually use in the .Send
( I personally have created many accounts purely to use in such codings in the .Send.
If you always want to keep a record of what you send at your main Email account, then simply add an extra
.CC = "MymainEMail@Provider.crum"
code line. )



Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Using Excel VBA to send emails from my Gmail account

Post by ABabeNChrist »

Thank you very much I will check it out and let you know my results

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Using Excel VBA to send emails from my Gmail account

Post by DocAElstein »

ABabeNChrist wrote:
25 Nov 2022, 13:19
.... thank you for all of your very helpful assistance. The last workbook you provided worked outstanding. .... made some subtle adjustments .... I am trying to eliminate some of the unnecessary coding or duplicates, but it is a slow process.
You are Welcome…. And Welcome back!!!!

_.____________________--
ABabeNChrist wrote:
25 Nov 2022, 13:19
.... .. .... I am trying to eliminate some of the unnecessary coding or duplicates, but it is a slow process.
I am a bit messy with my coding, and I like to keep everything there, and the 'comments to remind me later what I was doing.

But, If it gets to be a bit difficult and time consuming for you, then let me know and I will pass you a stripped down simplified form tomorrow
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(