VBA结合Google SMTP发送Gmail失败问题补充：OAuth实施后的适配困境

Hey there, let's work through your Gmail VBA issue step by step—this is super common now that Google has phased out traditional password-based SMTP access, so you're not alone!

Your original CDO code relied on plain username/password authentication, which Google no longer allows for most personal Gmail accounts (unless you enable "less secure apps," which is blocked by default now). The OAuth code you tried works in test mode because Google lets unvalidated apps run for testing purposes, but production mode requires full app verification—which indeed needs a domain you don't have. Since this is just for your personal local use with a single account, you don't need to go through that whole verification hassle.

Here are two straightforward fixes tailored to your scenario:

1. Use Google App Passwords (Recommended) #

If you have two-factor authentication (2FA) enabled on your Gmail account (and you absolutely should for security!), you can create an App Password—a unique 16-digit password specifically for your VBA script. This lets you keep using your original CDO code with minimal changes:

• Log into your Google Account, go to Security settings
• Look for "App Passwords" (this only shows up if 2FA is turned on)
• Create a new password, select "Mail" as the app and "Other (custom name)" as the device (name it something like "Excel VBA Mailer")
• Copy the generated 16-digit password, then paste it into your VBA code in place of your old password

2. Add Yourself as a Test User for OAuth #

If you want to stick with the OAuth approach, you can avoid the "unverified" warning by adding your own Gmail account as a test user in your Google Cloud project:

• Go to the Google Cloud Console and open your Gmail API project
• Navigate to the "OAuth Consent Screen" tab
• Under "Test Users," add your xxx@gmail.com account
• Now your app will work without the verification warning, even when you're using it beyond the initial test session (test mode allows up to 100 test users, so just your account is fine)

Here's your original code with the password field updated to use an App Password:

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxxx@gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YOUR_GENERATED_APP_PASSWORD" ' Replace with your 16-digit App Password
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With
With CDO_Mail
 Set .Configuration = CDO_Config
End With
CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

• If you don't have 2FA enabled yet, take 2 minutes to turn it on—it's a critical security step, and it unlocks the App Password feature
• The test user approach for OAuth is great if you want to learn more about modern authentication, but App Passwords are the quickest fix to get your existing code working again

内容的提问来源于stack exchange，提问作者SeniorTom