Send Email in Access VBA using CDO SendObject olMailItem

This tutorial explains how to send email in Access VBA using CDO, SendObject and olMailItem

Send Email in Access VBA using CDO, SendObject, OlMailItem

There are mainly two methods of sending email in Access 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 Access VBA using SendObject Method

To send email using SendObject Method is simple, because it does not require SMTP sever address and port number. When you run SendObject, whoever run the code will trigger to send email from its Outlook, instead of sending from a specific server email. It is a little bit strange because if you develop an application that sends confirmation email to user who have just signed up the system, the user will see he is sending himself a confirmation, not a confirmation sent from the server.

Note that before the email is sent, a security message will pop up, you must press “Allow” button to send the email.

 

Syntax of SendObject Method

Docmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

ObjectType	Optional. To attach data of Access Object in the email message. Object Name Value Description acSendForm 2 Send a Form. acSendModule 5 Send a Module. acSendNoObject -1 (Default) Don’t send a database object. acSendQuery 1 Send a Query. acSendReport 3 Send a Report. acSendTable 0 Send a Table. You will be prompted to choose a file format to attach before sending the email.
ObjectName	Optional. Name of Object to include
OutputFormat	Optional.
To	Optional. Recipient’s Email address, use comma to separate email. For example, “abc@a.com, def@b.com”
Cc	Optional. Cc Email address
Bcc	Optional. Bcc Email address
Subject	Optional. Subject of Email
MessageText	Optional. Message of Email
EditMessage	Optional. Set TRUE to edit message before send, FALSE to send without edit. Deafult is TRUE
TemplateFile	Optional. The path of HTML file to use as template

Example of sending email using SendObject Method

The below code sends an email to receiver@hotmail.com and receiver2@hotmail.com.

DoCmd.SendObject , , , "receiver@hotmail.com,receiver2@hotmail.com",, , "This is the subject", "This is the content", False

The below code attach a table “enrollment” to receiver@hotmail.com

DoCmd.SendObject acSendTable ,"Enrollment" , , "receiver@hotmail.com,receiver2@hotmail.com",, , "This is the subject", "This is the content", False

Send Email in Access VBA using olMailItem Object

To use this method, you should enable the followings first

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

This method is very similar to SendObject Method, but it cannot send Access Objects as attachment, it is simpler but it also has the annoying Security Warning message.

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"
        .Body = "test message"
        .Subject = "message from access"
        '.Attachments.Add (strAttachmentLocation)
        .Send
    End With
    Set mi = Nothing
    Set outl = Nothing
End Sub

Send Email in Access VBA using CDO

CDO.Message allows you to send email using SMTP Server, which means users trigger email to be sent from specific 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.

I have extracted the core CDO.Message Properties in the below code such as CC, AddAttachment, etc as comments for your reference.

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"
        Set .Configuration = iconf
        .Send
    End With

    Set iconf = Nothing
    Set imsg = Nothing
    Set flds = Nothing
End Sub

The receiver will see the below email.

 Outbound References

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

 

Send Email in Access VBA using CDO SendObject olMailItem