Send Email in Excel VBA using CDO and OLMailItem

This tutorial explains how to send email in Excel VBA using CDO and olMailItem.

Send Email in Excel VBA using CDO and OlMailItem

There are mainly two methods of sending email in Excel VBA. The first method is to use CDO Object, another is to use SendObject Method, I will explain each method in the below sections.

Send Email in Excel VBA using olMailItem Object

The first method uses olMailItem, it automatically sends email from the active Outlook account. The sender is whoever triggers the Macro, not sending from designated email account, so it may not be appropriate to use if you want an message to be sent officially from a generic email.

Another disadvantage of olMailItem is that before the message is sent, you may receive a confirmation message below, you must click the Allow button in order to send the message. In addition, the email sens out automatically without allowing you to preview and change the message. If you need preview, you should use VBA Hyperlinks.Add Method.

To use this method, you should enable the following References first

Alt+F11 >Tools > References > Microsoft Outlook 15.0 Object Library

Copy and paste the below code in a Module.

Sub SendEMail()
    Dim outl As Outlook.Application
    Set outl = New Outlook.Application
    Dim mi As Outlook.MailItem
    Set mi = outl.CreateItem(olMailItem)
    
    With mi
        .To = "abc@hotmail.com"
        .Cc = "def@hotmail.com"
        .Body = "test message"
        .Subject = "message from access"
        '.Attachments.Add (strAttachmentLocation)
        .Send
    End With
    Set mi = Nothing
    Set outl = Nothing
End Sub

Send Email in Excel VBA using CDO

CDO.Message allows you to send email using SMTP Server, which means users trigger email to be sent from designated email account instead of users’ own email account.

Below is an example of sending email from Yahoo email scammera1@yahoo.com.hk, which uses 465 as SMTP port and smtp.mail.yahoo.com as SMTP server address, such information can be found in Yahoo Email FAQ. You can also send email from other email providers such as Google, you can find the required information in their websites.

Copy and paste the below code in a Module.

Public Sub SendEmail()
    Const cdoSendUsingPickup = 1
    Const cdoSendUsingPort = 2
    Const cdoAnonymous = 0
    Const cdoBasic = 1     ' Use basic (clear-text) authentication
    Const cdoNTLM = 2  'NTLM  ' Use NTLM authentication
    Dim imsg As Object
    Dim iconf As Object
    Dim flds As Object
    Dim schema As String

    Set imsg = CreateObject("CDO.Message")
    Set iconf = CreateObject("CDO.Configuration")
    Set flds = iconf.Fields

    ' send one copy with SMTP server (with autentication)
    schema = "http://schemas.microsoft.com/cdo/configuration/"
    flds.Item(schema & "sendusing") = cdoSendUsingPort
    flds.Item(schema & "smtpserver") = "smtp.mail.yahoo.com"
    flds.Item(schema & "smtpserverport") = 465
    flds.Item(schema & "smtpauthenticate") = cdoBasic
    flds.Item(schema & "sendusername") = "scammera1@yahoo.com.hk"
    flds.Item(schema & "sendpassword") = "password"
    flds.Item(schema & "smtpusessl") = True
    flds.Update

    With imsg
        .From = "scammera1@yahoo.com.hk"   'Should be same as sendusername
        .To = "scammera1@yahoo.com.hk,terethan@hotmail.com"
        '.Bcc = "test@email.com"
        '.Cc = "test@email.com"
        .Subject = "This is a subject"
        .HTMLBody = "<h1>This is a test message<h1>"        
        'TextBody = "This is a test message"         
        .Sender = "This is the sender"       
        '.Organization = "My Company"        
        '.ReplyTo = "address@mycompany.com"        
        '.AddAttachment "c:\temp\readme.txt"                
         .Send     
   End With
   Set .Configuration = iconf  
   Set iconf = Nothing     
   Set imsg = Nothing     
   Set flds = Nothing 
End Sub

The receiver will see the below email.

If you want to format the email text, you can use MailItem.HTMLBody Property.

For example, to change the text to red color

imsg.HTMLBody = "<Font Color = 'Red'>Enter the message text here. </Font>"

Send email using Hyperlink

Simply insert a button, and then add a hyperlink where you can select recipient’s email address and email subject.

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff197046.aspx?f=255&MSPPError=-2147217396