UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Sending Simple Email From Ms-access 2016 64 Bit, Access 2016    
 
   
bowlesj
post Jun 10 2019, 03:38 PM
Post#21



Posts: 289
Joined: 20-May 08



Okay DbGuy, I will let you know what happens and if I get ms-access to send out email I will leave info about it so others can see it.

It could be four or so weeks before I get at it again as I have two much higher projects I really should complete first.

Thanks again,
John
This post has been edited by bowlesj: Jun 10 2019, 03:42 PM
Go to the top of the page
 
stevep
post Jun 11 2019, 07:34 PM
Post#22



Posts: 91
Joined: 9-November 14



I have successfully deployed CDO email as per this earlier thread

https://www.UtterAccess.com/forum/index.php...2051546&hl=

Note that I am storing my gmail user ID and password outside the sub in a table called Lookups.

After setting Google to "allow less secure apps", it has worked flawlessly since then. My suggestion is to copy and paste my sub into your own module and substitute in your own google user id and password and see if it connects and sends the test email. Once you have that licked, you can decide how something like this can fit your needs.

This has turned out to be super useful for me. It appears to be independent of Office or Windows versions within reason.


Go to the top of the page
 
bowlesj
post Jun 11 2019, 08:07 PM
Post#23



Posts: 289
Joined: 20-May 08



Thanks Steve, I will try it tomorrow hopefully. John
Go to the top of the page
 
bowlesj
post Jun 12 2019, 08:28 AM
Post#24



Posts: 289
Joined: 20-May 08



Hi again Steve,

Okay so I put it as a priority and tried it.
I first made sure that I have google set to allow less secure apps.
The exact code is below but I changed my ID and password (the ID is my email address).
I tried it once without check boxing CDO in the references and I got that error which shows in the attached picture.
"Run Time Error: big number - The SMTP server name is required, and was not found in the configuration source"
So I then tried it again after checking "Microsoft CDO for Windows 2000 Library" in the references (see the same attached picture).
I also did a reboot and I get the same error.
I checked this page to make extra sure the server name is correct. https://support.google.com/mail/answer/7126229?hl=en
I am using MS-Access 2016 64 bit on Windows-7 64 bit.
Maybe I need to dig farther into some of the other stuff in your post Steve.

CODE
Private Sub cmdSendEmail_Click()

    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    
    Dim strGMailID As String
    Dim strGMailPWD As String
    strGMailID = "My_Email_Address_ID@gmail.com"
    strGMailPWD = "My_Email_Password"


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields
    
     With Flds
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = strGMailID
        .Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = strGMailPWD
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "smtp.gmail.com" 'smtp mail server
        .Item("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 25 'stmp server
        .Update
    End With

    With iMsg

        Set .Configuration = iConf
        .To = "My_Email_Address_ID@gmail.com"
        .From = strGMailID
        .Subject = "Test Email From Access " & Time
        .TextBody = "Test the CDO process"
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing

End Sub

This post has been edited by bowlesj: Jun 12 2019, 08:38 AM
Attached File(s)
Attached File  error_message.png ( 75.64K )Number of downloads: 0
 
Go to the top of the page
 
cheekybuddha
post Jun 12 2019, 09:14 AM
Post#25


UtterAccess VIP
Posts: 11,289
Joined: 6-December 03
From: Telegraph Hill


CODE
' ...
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 25 'stmp server
' ...


This looks wrong - I think you need port 587 or 465

See here

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 12 2019, 09:25 AM
Post#26


UtterAccess VIP
Posts: 11,289
Joined: 6-December 03
From: Telegraph Hill


OK, you also seem to have used uppercase 'CDO' in you configuration item strings - this is a case sensitive property and must be lower case.

I just tried and used port 465 with success:
CODE
Private Sub cmdSendEmail_Click()

  Dim iMsg As Object
  Dim iConf As Object
  Dim Flds As Variant
  
  Dim strGMailID As String
  Dim strGMailPWD As String
  strGMailID = "My_Email_Address_ID@gmail.com"
  strGMailPWD = "My_Email_Password"


  Set iMsg = CreateObject("CDO.Message")
  Set iConf = CreateObject("CDO.Configuration")

  iConf.Load -1
  Set Flds = iConf.Fields
  
  With Flds
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True        ' NOTE - lowercase 'CDO' in this property string and all the others too
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = strGMailID
    .Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = strGMailPWD
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "smtp.gmail.com" 'smtp mail server
    .Item("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 465 'stmp server
    .Update
  End With

  With iMsg
    Set .Configuration = iConf
    .To = "My_Email_Address_ID@gmail.com"
    .From = strGMailID
    .Subject = "Test Email From Access " & Time
    .TextBody = "Test the CDO process"
    .Send
  End With

  Set iMsg = Nothing
  Set iConf = Nothing

End Sub

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jun 12 2019, 09:27 AM
Post#27


UtterAccess VIP
Posts: 11,289
Joined: 6-December 03
From: Telegraph Hill


I see what is happening!!!

The UA forum software is trying to be helpful and is uppercasing the string 'CDO' when it comes across it!!! pullhair.gif

Just make sure all occurrences within the 'http://schemas.microsoft.com/' strings are lowercase.

--------------------


Regards,

David Marten
Go to the top of the page
 
bowlesj
post Jun 12 2019, 10:25 AM
Post#28



Posts: 289
Joined: 20-May 08



Success!

Thanks to Steve to for providing the code and to David for noticing the corrections I had to make as indicated here.
===> Correction: I had to change every occurrence of CDO to lowercase including in the (CreateObject statements) and I put a comment on every such line.
===> Correction: Only port 465 worked. I put in comments about all the ports settings I was trying.

Note: it works even if I remove the reference to "Microsoft CDO for Windows 2000 Library" as mentioned in a prior post with a picture.

CODE
Private Sub cmdSendEmail_Click()
        'this one works 2019/June/12 on Access 2016 64 bit with Windows 7 64 bit.

    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant
    
    Dim strGMailID As String
    Dim strGMailPWD As String
    strGMailID = "My_Email_Address_ID@gmail.com"
    strGMailPWD = "My_Password"


    Set iMsg = CreateObject("CDO.Message")  'CDO has to be lower case here
    Set iConf = CreateObject("CDO.Configuration") 'CDO has to be lower case here

    iConf.Load -1
    Set Flds = iConf.Fields
    
     With Flds
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = True 'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = 1 'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = strGMailID 'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = strGMailPWD 'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpserver") = "smtp.gmail.com" 'smtp mail server  'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/sendusing") = 2 'CDO has to be lower case here
        .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 465 'port setting that worked. 'CDO has to be lower case here
        .Update
    End With
    '    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 25 'stmp server   'this port setting failed but it took a while
    '    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 587 'stmp server  'this port setting gives an immediate error
    '    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = 465 'stmp server  'this port setting worked

    With iMsg

        Set .Configuration = iConf
        .To = "My_Email_Address_ID@gmail.com"
        .From = strGMailID
        .Subject = "Test Email From Access " & Time
        .TextBody = "Test the CDO process"
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing

End Sub

This post has been edited by bowlesj: Jun 12 2019, 10:46 AM
Go to the top of the page
 
theDBguy
post Jun 12 2019, 10:43 AM
Post#29


Access Wiki and Forums Moderator
Posts: 75,521
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Now that you know you can use CDO with GMail and have all the correct settings, I am just curious if you could try my demo again using those same settings and let me know what I need to fix to make it work. Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Jun 12 2019, 10:50 AM
Post#30


UtterAccess VIP
Posts: 11,289
Joined: 6-December 03
From: Telegraph Hill


Works for me, DBG! wink.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
bowlesj
post Jun 12 2019, 11:19 AM
Post#31



Posts: 289
Joined: 20-May 08



Hi DBguy, I got yours to work after I made these three changes to match the above code that worked. I am pretty sure I have not missed anything here. I also chose 465 as the port. Maybe defaults should be set. John

Set cdoConfig = CreateObject("CDO.Configuration") 'I changed this CDO to lower case
Set cdoMessage = CreateObject("CDO.Message") 'I changed this CDO to lower case
cdoConfig.Load -1 'I added this code in which was not there.




This post has been edited by bowlesj: Jun 12 2019, 11:23 AM
Go to the top of the page
 
bowlesj
post Jun 12 2019, 11:33 AM
Post#32



Posts: 289
Joined: 20-May 08



These are the settings I used.
Attached File(s)
Attached File  DBguySettings.png ( 18.59K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Jun 12 2019, 11:36 AM
Post#33


Access Wiki and Forums Moderator
Posts: 75,521
Joined: 19-June 07
From: SunnySandyEggo


Hi David and John. Thanks for letting me know. When I went to my GMail settings, I couldn't find the "allow less secure apps" setting. I know I found it before because that's how I tested the demo in the past, but I missed the setting this time when this thread started. So, I thought maybe GMail (Google) changed the rules of the game again and doesn't allow unsecured apps anymore because maybe they want all apps to get a token now. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Jun 12 2019, 11:38 AM
Post#34


UtterAccess VIP
Posts: 11,289
Joined: 6-December 03
From: Telegraph Hill


I too couldn't find where to set the 'Allow less secure apps' when I looked.

But I know it's set because they occasionally email me to unset it!!!

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Jun 12 2019, 11:38 AM
Post#35


Access Wiki and Forums Moderator
Posts: 75,521
Joined: 19-June 07
From: SunnySandyEggo


Hi John,

QUOTE
These are the settings I used.

Did you have to change anything in the code behind the form at all?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bowlesj
post Jun 12 2019, 11:40 AM
Post#36



Posts: 289
Joined: 20-May 08



I have to have the less secure apps setting or I don't properly get my emails from my website form which is just http
Go to the top of the page
 
theDBguy
post Jun 12 2019, 11:41 AM
Post#37


Access Wiki and Forums Moderator
Posts: 75,521
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
I have to have the less secure apps setting or I don't properly get my emails from my website form which is just http

Correct! But that's a Google/GMail restriction and not of CDO's.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bowlesj
post Jun 12 2019, 11:46 AM
Post#38



Posts: 289
Joined: 20-May 08



I just changed these as mentioned above.

Set cdoConfig = CreateObject("CDO.Configuration") 'I changed this CDO to lower case
Set cdoMessage = CreateObject("CDO.Message") 'I changed this CDO to lower case
cdoConfig.Load -1 'I added this code in which was not there.


The less secure settings are found by clicking the 3 dots at the top of google in the top right, clicking settings, clicking manage your google account, on the left clicking security, scroll down a ways and adjust it.
This post has been edited by bowlesj: Jun 12 2019, 11:57 AM
Go to the top of the page
 
bowlesj
post Jun 12 2019, 11:51 AM
Post#39



Posts: 289
Joined: 20-May 08



This is the code but the CDO needs to be changed to lower case with ctrl+h search and replace


CODE
Private Sub cmdSend_Click()
On Error GoTo errHandler

Dim cdoConfig As Object
Dim cdoMessage As Object

'check form input
If Me.txtServer & "" = "" Then
    Me.txtServer.SetFocus
    MsgBox "Please enter the server's address", vbInformation, "Required"
ElseIf Not IsNumeric(Me.txtPort) Then
    Me.txtPort.SetFocus
    MsgBox "Please enter a port number.", vbInformation, "Required"
ElseIf Me.txtUsername & "" = "" And Me.txtAuth > 0 Then
    Me.txtUsername.SetFocus
    MsgBox "Please enter your username.", vbInformation, "Required"
ElseIf Me.txtPwd & "" = "" And Me.txtAuth > 0 Then
    Me.txtPwd.SetFocus
    MsgBox "Please enter the password.", vbInformation, "Required"
ElseIf Me.txtFrom & "" = "" Then
    Me.txtFrom.SetFocus
    MsgBox "Please enter your email address.", vbInformation, "Required"
ElseIf Me.txtTo & "" = "" Then
    Me.txtTo.SetFocus
    MsgBox "Please enter the recipient's email address.", vbInformation, "Required"
ElseIf Me.txtSubj & "" = "" Then
    Me.txtSubj.SetFocus
    MsgBox "Please enter a subject line.", vbInformation, "Required"
ElseIf Me.txtMsg & "" = "" Then
    Me.txtMsg.SetFocus
    MsgBox "Please enter a message.", vbInformation, "Required"
Else

'create email object
Set cdoConfig = CreateObject("CDO.Configuration") 'I changed this CDO to lower case
Set cdoMessage = CreateObject("CDO.Message") 'I changed this CDO to lower case
cdoConfig.Load -1  'I added this code in

'setup server configuration
With cdoConfig.Fields
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserver") = Me.txtServer  'I chose  smtp.gmai'.com
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpserverport") = Me.txtPort  'I chose 465
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpauthenticate") = Me.txtAuth 'I chose basic which send out value 1 here
    .Item("http://schemas.microsoft.com/CDO/configuration/sendusing") = Me.txtSendUsing 'I chose port which sends out a 2 here
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpusessl") = Me.txtSSL 'I chose yes which sends out a -1. Maybe I should try true
    .Item("http://schemas.microsoft.com/CDO/configuration/smtpconnectiontimeout") = Me.txtTimeout  'I used 60

    'warning: it's a security risk to hard-code your username and password
    If Me.txtAuth > 0 Then
        .Item("http://schemas.microsoft.com/CDO/configuration/sendusername") = Me.txtUsername
        .Item("http://schemas.microsoft.com/CDO/configuration/sendpassword") = Me.txtPwd
    End If
    .Update
End With

'build and send email message
With cdoMessage
    Set .Configuration = cdoConfig
    .From = Me.txtFrom
    .To = Me.txtTo
    '.Cc = "copy@email.address"
    '.Bcc = "blind.copy@email.address"
    .Subject = Me.txtSubj
    .TextBody = Me.txtMsg
    '.HTMLBody = "<h2>Use this to send email as HTML.</h2>"
    '.AddAttachment "c:\foldername\filename.ext"
    .Send
End With

MsgBox "Message sent.", vbInformation, "Done!"

End If

errExit:
    Set cdoConfig = Nothing
    Set cdoMessage = Nothing
    Exit Sub
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
    
End Sub

This post has been edited by bowlesj: Jun 12 2019, 11:55 AM
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    17th June 2019 - 07:48 PM