Office 365 发送电子邮件 (VBA) 的解决方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40759447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Solution for Office 365 Sending Email (VBA)
提问by Eric K.
I have search around for few months, but still getting solution.
我已经搜索了几个月，但仍然得到解决方案。
Sending Email using CDO for Office365, is not working.
使用 CDO for Office365 发送电子邮件不起作用。
Getting Error like Transport failed to connect or Authentication Failure.
获取错误，如传输无法连接或身份验证失败。
回答by Eric K.
Would like to share the solution, I build to resolve sending email thru Office365 SMTP.
想分享解决方案，我构建解决通过 Office365 SMTP 发送电子邮件的问题。
1) We need to build a Custom DLL for Excel
1）我们需要为Excel构建一个自定义DLL
2) Pack the DLL as installer, then install in computer (If you wish to share your macro)
2) 将 DLL 打包为安装程序，然后安装在计算机中（如果您想共享您的宏）
3) Consume the DLL thru Excel VBA
3) 通过 Excel VBA 使用 DLL
Let get start:
让我们开始吧：
1) Create Custom DLL for Excel (source code)
1）为Excel创建自定义DLL（源代码）
The important spot make everything work is the domain
使一切正常工作的重要点是域
client.Credentials = new System.Net.NetworkCredential(Email, Password, "outlook.com");
If the domains are wrong or empty it will not work.
如果域错误或为空，它将不起作用。
using System.Net.Mail;
namespace Eric_Library
{
public class SMTP
{
public string oSMTP(string Email, string Password, string subject, string htmlBody,
string[] Attachments,
string To, string Cc, string Bcc)
{
Email = Email.Trim();
try
{
if (!Email.EndsWith("@outlook.com", StringComparison.CurrentCultureIgnoreCase))
throw new Exception("Your domain is not matching");
System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient("smtp.office365.com");
client.TargetName = "STARTTLS/smtp.office365.com";
client.UseDefaultCredentials = false;
//Domain name can be "company.com" or "outlook.com" or etc
client.Credentials = new System.Net.NetworkCredential(Email, Password, "outlook.com");
client.EnableSsl = true;
client.Port = 587;
client.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
System.Net.Mail.MailMessage msg = new System.Net.Mail.MailMessage();
msg.From = new MailAddress(Email);
msg.CC.Add(Email);
msg.Subject = subject;
msg.Body = htmlBody;
msg.IsBodyHtml = true;
if (string.IsNullOrEmpty(To))
throw new Exception("To cannot be blank");
else
{
To.Replace(";", ",");
msg.To.Add(To);
}
if (!string.IsNullOrEmpty(Cc))
{
Cc.Replace(";", ",");
msg.CC.Add(Cc);
}
if (!string.IsNullOrEmpty(Bcc))
{
Bcc.Replace(";", ",");
msg.Bcc.Add(Bcc);
}
if (Attachments.Count() > 0)
{
foreach (var item in Attachments)
{
if (!string.IsNullOrEmpty(item))
{
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(item);
msg.Attachments.Add(attachment);
}
}
}
client.Send(msg);
return "Message Sent : " + DateTime.Now.ToString();
}
catch (Exception ex)
{
return ex.Message;
}
}
}
}
}
*** Remember to check Register for COM interop, else you will not able to add it as Reference in VBA
*** 记得勾选 Register for COM interop，否则你将无法在 VBA 中将其添加为 Reference
2) Pack the DLL as installer (My project name is Office365 SMTP Library) Create installer is really easy, remember to grap these 2 files into installer, then build it.
2）将DLL打包为安装程序（我的项目名称是Office365 SMTP Library） 创建安装程序非常简单，记得将这2个文件打包到安装程序中，然后构建它。
3) Consume the DLL thru Excel VBA Go to the Program directory, then select the tlb file add it as reference.
3) 通过 Excel VBA 使用 DLL 转到 Program 目录，然后选择 tlb 文件将其添加为参考。
--> if you share your macro to other user, make sure they have install the DLL too
--> 如果您将宏共享给其他用户，请确保他们也安装了 DLL
--> they do not need to add reference again, Excel will look for that automatically.
--> 他们不需要再次添加引用，Excel 会自动查找。
Now you can consume the DLL
现在您可以使用 DLL
Private Sub test_oMail()
Dim oMsg As Office365_SMTP_Library.SMTP
Set oMsg = New Office365_SMTP_Library.SMTP
Dim nArr_Attach() As String
ReDim nArr_Attach(1)
nArr_Attach(0) = "C:\Users\Public\Pictures\Sample Pictures\Chrysanthemum.jpg"
nArr_Attach(1) = "C:\Users\Public\Pictures\Sample Pictures\Koala.jpg"
Debug.Print oMsg.oSmtp("email", "password", _
"Testing Subject", "<p>First Paragraph</p><p>Second Paragraph</p>", _
nArr_Attach, "TO", "CC", "BCC")
End Sub
--> Pass in the attachment as array, so that you can have as much as you wish --> but remember the max limit is 30MB for Office365 per email
--> 将附件作为数组传递，以便您可以随心所欲 --> 但请记住，Office365 每封电子邮件的最大限制为 30MB
Thanks
谢谢
