In this post, we will be giving you the basic code required to open up Outlook from Access and set the subject and body fields.
There are two ways to bind to the Outlook Object Library: early and late. Early binding means that you bind by making a reference to the relevant Outlook Object Library. Late Binding means that you bind during the procedure. Early binding is more robust and should be used where possible. Late Binding, however, is necessary if you are writing code that may be interpreted by different versions of MS Office and, therefore, cannot reference the same version of Outlook.
Early Binding
You will first need to set a reference to the Outlook object model:
Open up the VBA editor by clicking ALT + F11.
Click on Tools>References
Scroll down and look for Microsoft Outlook xx.0 Object Library. The list of libraries is in alphabetical order. The xx.0 part will be either 12.0, 14.0 or 15.0 depending on your version of Access (2007, 2010 and 2013 respectively). Make sure it is checked and click OK.
You are now ready to automate Outlook from Access!
The Code
Public Function openOutlookSingleEmailAddress(strEmailAddress As String, _
strSubject As String, _
strMessage As String) As Boolean
On Error GoTo ErrorHandler
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 1 ''''''''''''''''''''
''''''''''''''''''' Declarations '''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim olRecipient As Outlook.Recipient
Dim olAttachment As Outlook.Attachment
' Create the Outlook session.
Set olApp = CreateObject("Outlook.Application")
' Create the message.
Set olMail = olApp.CreateItem(olMailItem)
With olMail
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 2 ''''''''''''''''''''
''''''''''''''''''' Recipients '''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add the To recipient(s) to the message.
Set olRecipient = .Recipients.Add(strEmailAddress)
olRecipient.Type = olTo
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 3 ''''''''''''''''''''
''''''''''''''''''' Attachments'''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Set the Subject, Body, and Importance of the message.
.Subject = strSubject
.Body = strMessage
.Importance = olImportanceHigh 'High importance
' Resolve each Recipient's name.
For Each olRecipient In .Recipients
olRecipient.Resolve
Next
'.SentOnBehalfOfName = "John Doe"
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 4 ''''''''''''''''''''
''''''''''''''' Display, Save, Send'''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
.Display
End With
openOutlookSingleEmailAddress = True
ExitFunction:
Set olRecipient = Nothing
Set olMail = Nothing
Set olApp = Nothing
Exit Function
ErrorHandler:
openOutlookSingleEmailAddress = False
Resume ExitFunction
End Function
The Explanation
Note: We have broken the code down into 4 segments: Declarations, Recipients, Attachments and Display, Save and Send. As we progress through the posts, we will be updating only certain segments to make assimilation easier.
Public Function openOutlookSingleEmailAddress(strEmailAddress As String, _ strSubject As String, _ strMessage As String) As Boolean
The name of the function is openOutlookSingleEmailAddress and it takes three arguments: strEmailAddress, strSubject and strMessage.
On Error GoTo ErrorHandler ... ErrorHandler: openOutlookSingleEmailAddress = False Resume ExitFunction
These lines of code are important. Although we are fundamentally performing an action and a sub-procedure would seemingly be more appropriate, it is important that we are able to tell if the code has performed as intended. In the case of opening up Outlook, it is obvious that it has either worked or not (Outlook is either open or not) but what if we were just sending an email without requiring Outlook to be opened? Unless we were able to get some information from the function, we would not know if email(s) had been sent.
So, if an error occurs the function will return a value of false and we can use an if..else…end if clause to perform actions based on the result.
e.g.
if openOutlookSingleEmailAddress("john@hotmail.com", "Subject", "Message")=true then
msgbox "You have successfully sent the email."
else
msgbox "Unable to send the email."
end if
This code will enable us to send an informative message to the end user letting them know whether the code has run successfully.
Dim olApp As Outlook.Application Dim olMail As Outlook.MailItem Dim olRecipient As Outlook.Recipient Dim olAttachment As Outlook.Attachment
In order to manipulate Outlook from Access we will need to use certain elements from the Outlook object model.
- Outlook.Application is the Outlook application itself (we imagine that was obvious).
- Outlook.MailItem is the instance of the email.
- Outlook.Recipient is the recipient of the emails. These can represent the to, cc or bcc fields.
- Outlook.Attachment is the attachment object.
' Create the Outlook session.
Set olApp = CreateObject("Outlook.Application")
Having declared what objects we require, we need to instantiate them. In this code, we are asking Access to get or create an instance of Outlook. In English, this means that we want Access to look for an open copy of Outlook to manipulate. If it can’t find one, it will open up a copy of Outlook. Simple right!
Note: If you have an open copy of Outlook, you will need to make sure you run it as an administrator or you will get an error.
' Create the message. Set olMail = olApp.CreateItem(olMailItem)
Having declared the MailItem, we now need to instantiate it. This basically means “please create a mail item that I can use to send the email.”
' Add the To recipient(s) to the message. Set olRecipient = (olMail).Recipients.Add(strEmailAddress) olRecipient.Type = olTo
We now need to add the email address to the “To” field of the email. To do this, we instantiate (that word again) a recipient and add the passed in strEmailAddress argument. Again, translated to English we have: We take strEmailAddress and add it to olMail.Recipients!
' Set the Subject, Body, and Importance of the message. (olMail).Subject = strSubject (olMail).Body = strMessage (olMail).Importance = olImportanceHigh 'High importance
Having set the recipients of the mail, we can set certain other attributes such as the subject, body and importance of the email. Experiment by commenting out each of the above lines to see what effect it has on the mail item.
' Resolve each Recipient's name. For Each olRecipient In (olMail).Recipients olRecipient.Resolve Next
Adding the recipient’s to the mail item is not enough! We need to resolve the email addresses to ensure that they are valid.
'.SentOnBehalfOfName = "accessallinone@outlook.com"
If we want to add a name or email address that we are “sending on behalf of” we need to set the .SentOnBehalfOfName attribute. We will be leaving this blank for now but please note that it can be set.
(olMail).Display
This line of code displays the Outlook window.
openOutlookSingleEmailAddress = True
If we get this far and haven’t generated any errors, we can return the value of true for the function.
Late Binding
The Code
Option Compare Database
Option Explicit
Public Function openOutlookSingleEmailAddress(strEmailAddress As String, _
strSubject As String, _
strMessage As String) As Boolean
On Error GoTo ErrorHandler
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 1 ''''''''''''''''''''
''''''''''''''''''' Declarations '''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Dim olApp As Outlook.Application
'Dim olMail As Outlook.MailItem
'Dim olRecipient As Outlook.Recipient
'Dim olAttachment As Outlook.Attachment
Dim olApp As Object
Dim olMail As Object
Dim olRecipient As Object
Dim olAttachment As Object
Const olMailItem = 0
Const olTo = 1
Const olImportanceHigh = 2
' Create the Outlook session.
Set olApp = CreateObject("Outlook.Application")
' Create the message.
Set olMail = olApp.CreateItem(olMailItem)
With olMail
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 2 ''''''''''''''''''''
''''''''''''''''''' Recipients '''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add the To recipient(s) to the message.
Set olRecipient = .Recipients.Add(strEmailAddress)
olRecipient.Type = olTo
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 3 ''''''''''''''''''''
''''''''''''''''''' Attachments'''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Set the Subject, Body, and Importance of the message.
.Subject = strSubject
.Body = strMessage
.Importance = olImportanceHigh 'High importance
' Resolve each Recipient's name.
For Each olRecipient In .Recipients
olRecipient.Resolve
Next
'.SentOnBehalfOfName = "accessallinone@outlook.com"
''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''' 4 ''''''''''''''''''''
''''''''''''''' Display, Save, Send'''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''
.Display
End With
openOutlookSingleEmailAddress = True
ExitFunction:
Set olRecipient = Nothing
Set olMail = Nothing
Set olApp = Nothing
Exit Function
ErrorHandler:
openOutlookSingleEmailAddress = False
Resume ExitFunction
End Function
The Explanation
'Dim olApp As Outlook.Application
'Dim olMail As Outlook.MailItem
'Dim olRecipient As Outlook.Recipient
'Dim olAttachment As Outlook.Attachment
Dim olApp As Object
Dim olMail As Object
Dim olRecipient As Object
Dim olAttachment As Object
Const olMailItem = 0
Const olTo = 1
Const olImportanceHigh = 2
The important thing to note here is that we have replaced declarations such as Dim olApp As Outlook.Application with Dim olApp As Object (this is the essence of late binding) and we have had to turn items such as olMailItem into constants.
Aside from these changes, the code works the same as with early binding.
Testing The Function
In order to test the function, we require a form and some code behind.
This is the form we will use. It has three boxes that correspond to the arguments of the openOutlookSingleEmailAddress function.
The Code
Here is the code behind:
Private Sub cmdTest_Click() On Error GoTo ErrorHandler 'validation With Me If IsNull(.txtEmailAddress) Or .txtEmailAddress = "" Then Err.Raise -100 End If If IsNull(.txtSubject) Or .txtSubject = "" Then Err.Raise -100 End If If IsNull(.txtMessage) Or .txtMessage = "" Then Err.Raise -100 End If If openOutlookSingleEmailAddress(.txtEmailAddress, .txtSubject, .txtMessage) Then MsgBox "Operation performed successfully" Else Err.Raise -101 End If End With ExitSub: Exit Sub ErrorHandler: If Err.Number = -100 Then MsgBox "Please ensure all fields are filled out." Else MsgBox "Unable to send email(s)" End If Resume ExitSub End Sub
The Explanation
Private Sub cmdTest_Click()
This sub procedure will fire when the cmdTest button is clicked.
On Error GoTo ErrorHandler ... ExitSub: Exit Sub ErrorHandler: If Err.Number = -100 Then MsgBox "Please ensure all fields are filled out." Else MsgBox "Unable to send email(s)" End If Resume ExitSub End Sub
In Access you can raise your own errors using the Err.Raise method. You can give the errors numbers and then display certain messages based upon the numbers provided. In the above code, any errors that have an error number of -100 will result in a message being displayed instructing the user to ensure all fields are filled out before proceeding.
If IsNull(.txtEmailAddress) Or .txtEmailAddress = "" Then Err.Raise -100 End If If IsNull(.txtSubject) Or .txtSubject = "" Then Err.Raise -100 End If If IsNull(.txtMessage) Or .txtMessage = "" Then Err.Raise -100 End If
This code checks that the fields on the form are filled out. If one of them isn’t filled out, an error will be raised with an error number of -100.
If openOutlookSingleEmailAddress(.txtEmailAddress, .txtSubject, .txtMessage) Then MsgBox "Operation performed successfully" Else Err.Raise -101 End If
The function openOutlookSingleEmailAddress returns either true or false. If the function returns true, the “Operation performed successfully” message will be displayed. If the function returns false, an error will be raised with a number of -101.
The Test
Using the criteria for the fields supplied above (“TestEmail@hotmail.com”, “Test Subject”, “Test Message”) we get the following result when we click the Test button.
We have successfully opened up an instance of Outlook and added a recipient, subject and message.
Note: In the coming posts, the Test Email forms will have fields that exactly match the arguments of the emailing functions.
The Conclusion
In this post, we have provided you with the basic code required to open up a window in MS Outlook and set certain attributes including the recipients, subject and body of the message.
In the coming posts, we will be embellishing on this code and showing you how to set the CC and BCC fields, include attachments and send an email without Outlook being visible (amongst other things).
