Solution for Office 365 Sending Email (VBA)

Question

I have search around for few months, but still getting solution.

Sending Email using CDO for Office365, is not working.

Getting Error like Transport failed to connect or Authentication Failure.

Answer 1

Would like to share the solution, I build to resolve sending email thru Office365 SMTP.

1) We need to build a Custom DLL for Excel

2) Pack the DLL as installer, then install in computer (If you wish to share your macro)

3) Consume the DLL thru Excel VBA

Let get start:

1) Create Custom DLL for Excel (source code)

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

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.

3) Consume the DLL thru Excel VBA Go to the Program directory, then select the tlb file add it as reference.

--> if you share your macro to other user, make sure they have install the DLL too

--> they do not need to add reference again, Excel will look for that automatically.

Now you can consume the 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

Thanks