Excel VBA CDO邮件 [英] Excel VBA CDO Mail

问题描述

我正在尝试使用Microsoft Office Excel 2007 VBA代码发送邮件，但出现错误:

I'm trying to send a mail with Microsoft Office Excel 2007 VBA code but I'm getting the error:

运行时错误'-2147220973(80040213)':

Run-time error '-2147220973 (80040213)':

自动化错误

我正在使用的代码是:

Dim cdomsg As Object

Set cdomsg = CreateObject("CDO.message")

With cdomsg.Configuration.Fields

  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 25
  .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") = "excel.**********@gmail.com"
  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "**********123"
  ' .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
  .Update

End With

With cdomsg

  .Subject = "Automated mail"
  .From = "excel.**********@gmail.com"
  .To = "**********@hitbts.com" ' https://temp-mail.org/
  .TextBody = "Automated mail"
  .AddAttachment ("*:\*****\***********\****************\***********\*****\*****.xlsm")
  .Send

End With

Set cdomsg = Nothing

我尝试了其他smpt服务器，当我键入计算机的 nslookup 时，cmd中显示的服务器名称和地址IP和另一个IP，但我不知道什么是正确的smpt服务器.

I have tried other smpt servers, the server name and address that shows in the cmd when I type in nslookup, the computer's IP and another IP but I don't know what's the correct smpt server.

答案后

对于以后寻找此代码的任何人，我使用和工作的代码如下(摘自此视频):

To anyone searching for this in the future, the code I used and worked is the following (taken from this video):

Dim Mail As New Message
Dim Config As Configuration
Set Config = Mail.Configuration

Config(cdoSendUsingMethod) = cdoSendUsingPort
Config(cdoSMTPServer) = "smtp.gmail.com"
Config(cdoSMTPServerPort) = 25
Config(cdoSMTPAuthenticate) = cdoBasic
Config(cdoSMTPUseSSL) = True
Config(cdoSendUserName) = "[e-mail]"
Config(cdoSendPassword) = "password123"
Config.Fields.Update

Mail.AddAttachment ("C:\path\file.ext")
Mail.To = "[e-mail]"
Mail.From = Config(cdoSendUserName)
Mail.Subject = "Email Subject"
Mail.HTMLBody = "<b>Email Body</b>"

Mail.Send

请确保更改示例的"[e-mail]"，"password123"，"C:\path\file.ext"和"[e-mail]"以及更改邮件的主题和正文.

Make sure to change "[e-mail]", "password123", "C:\path\file.ext" and "[e-mail]" for the example to work and the subject and body to change the mail.

我还转到了顶部菜单工具"在VBA上，选择参考..."，启用用于Windows 2000库的Microsoft CDO".然后按确定"，如上面链接的视频所示.

I also went to the top menu "Tools" on the VBA, option "References...", enabled "Microsoft CDO for Windows 2000 Library" and pressed OK as shown in the video linked above.

直接链接以启用安全性降低"；来自此处的GMail选项.

Direct link to enable the "Less Secure" option for GMail taken from here.

推荐答案

在使用Gmail时；您是否检查了启用安全性较低的应用程序"是否有所不同? Support.google.com参考

As you're using Gmail; did you check whether enabling 'less secure apps' made a difference? Support.google.com Reference

这篇关于Excel VBA CDO邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！