vba - How to use SMTP mail with Gmail accounts after Jan 1st 2025 - Stack Overflow

admin管理员组

文章数量:1123264

I am the Fixtures Secretary for the Seniors Section of my golf club where a great deal of admin work was done manually. I am trying to automate things as much as possible and sending emails is one task. I need a solution where Outlook is not the answer - hence trying to use Gmail - as 4 of the committee of 6 only have the New Outlook app (not part of Office) and this does not yet support automation.

So far, I have found a number of plausible solutions to the problem of setting up automated emails in VBA that use Gmail here on Stack overflow and YouTube. Those that were able to demonstrate they worked are all from on/before 2020. A number of them refer to getting your Google account to use Less Secure Apps. This is now phased out and not an option. My Gmail account and email were setup on 2ns Jan 2025.

I have setup 2 stage verification activated and generated App Passwords and used them as described in various replies to similar questions in this and other forums (fora).

Nothing gets me past the error message that the SendUsing configuration is invalid.

In the error message box I get Run-time error '-2147220960 (80040220) The "SendUsing" configuration is invalid.

My code is below and in the References I have 'ticked' Microsoft CDO for Windows 2000 library. I have yet to find a more uptodate version of CDO. I am running Windows 11 Pro 24H2 on an i7 11700K CPU with 32Gb RAM.

Option Compare Database
' Global variables
Dim newMail As CDO.Message
Dim newConfiguration As CDO.Configuration
Dim Fields As Variant
Dim msConfigURL As String

' References include Microsoft CDO for Windows 2000 Library

Sub Send_Email()
‘ Create error handler
On Error GoTo errHandle

' Create the new instances of the objects
Set newMail = New CDO.Message
Set newConfiguration = New CDO.Configuration

' Set all the default values
newMail.Configuration.Load -1

' Put in the message info
With newMail
   .Subject = "VBA Test"
   .From = “**********@gmail”           ‘ A valid and working Gmail account with email enabled
   .To = “*********@outlook”                ‘ An email address that I keep for testing purposes
   .TextBody = "Test message sent using VBA script in Access"

End With

' Set the configuration
msConfigURL = ";

' Make the Fields
Set Fields = newConfiguration.Fields

With Fields
   .Item(msConfigURL & "/sendusername") = "**********@gmail"
   .Item(msConfigURL & "/sendpassword") = "**************"      ‘ I have tried the account password and the generated App Password
   .Item(msConfigURL & "/smtpusesssl") = True
   .Item(msConfigURL & "/smtpauthenticate") = 1
   .Item(msConfigURL & "/smtpserver") = "smtp.gmail"
   .Item(msConfigURL & "/smtpserverport") = 465
   .Item(msConfigURL & "/sendusing") = 2

   ' Update the configuration
   .Update

End With

' Transfer the configuration
newMail.Configuration = newConfiguration

' Send the email
newMail.Send

MsgBox "Email has been sent", vbInformation

' Exit lines for routine
exit_line:
   ' Release object from memory
   Set newMail = Nothing
   Set newMessage = Nothing

   Exit Sub

' Error handling
errHandle:
Select Case Err.Number
    Case -2147220973  'Could be because of Internet Connection
        MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description
   Case Else   'Report other errors
       MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description

End Select

Resume exit_line

End Sub

What am I missing? How silly am I going to feel when it is pointed out to me?

本文标签： vbaHow to use SMTP mail with Gmail accounts after Jan 1st 2025Stack Overflow