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.
You will be prompted to choose a file format to attach before sending the email.
|ObjectName||Optional. Name of Object to include|
|To||Optional. Recipient’s Email address, use comma to separate email. For example, “email@example.com, firstname.lastname@example.org”|
|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 email@example.com and firstname.lastname@example.org.
DoCmd.SendObject , , , "email@example.com,firstname.lastname@example.org",, , "This is the subject", "This is the content", False
The below code attach a table “enrollment” to email@example.com
DoCmd.SendObject acSendTable ,"Enrollment" , , "firstname.lastname@example.org,email@example.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 = "firstname.lastname@example.org" .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 email@example.com, 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") = "firstname.lastname@example.org" flds.Item(schema & "sendpassword") = "password" flds.Item(schema & "smtpusessl") = True flds.Update With imsg .From = "email@example.com" 'Should be same as sendusername .To = "firstname.lastname@example.org,email@example.com" '.Bcc = "firstname.lastname@example.org" '.Cc = "email@example.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 = "firstname.lastname@example.org" '.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.