By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,709 Members | 2,155 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Sending E-Mails via Outlook - One Developer's Approach

Expert Mod 2.5K+
P: 3,282
When using MS Access to manage data, it is often helpful to use the automation features of Access to generate e-mails and send them to your customers. If you happen to be sending an Access Object (like an MS Access-generated Report), you can use the built-in .SendObject Method--but many times, we just want (or need) to generate related correspondence, based upon the work you are doing in the Database.

Below, I will describe the method I use for sending e-mail via MS Outlook. Since that is the e-mail application we use at work, and since it is a standard desktop application for many of us, this is a great place to start. If you use a different e-mail application (not a web-mail service) there may be ways to modify this article's code in order to accommodate, but my focus will be on MS Outlook as the e-mail application.

First, it is possible to add lines of code that generate an e-mail to all your Forms, add attachments as necessary and then properly format things all nice and neat. However, if we do this, we find ourselves "re-inventing the wheel" every time we need to send an e-mail. From a developer's perspective, this is a waste of bits and bytes. A "better" approach may be to standardize the e-mail-sending procedure in a globally accessible Function which does all that work for you. Then, all you have to do is call that Funciton, sending the necessary arguments to it.

Here's how we do it:

First, in a separate, standalone VBA Module, create your e-mail function:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Public Function SendAnEMail(olSendTo As String, _
  5.                             olSubject As String, _
  6.                             olEMailBody As String, _
  7.                             olDisplay As Boolean, _
  8.                    Optional olCCLine As String, _
  9.                    Optional olBCCLine As String, _
  10.                    Optional olOnBehalfOf As String, _
  11.                    Optional olAtchs As String, _
  12.                    Optional SendAsHTML As Boolean) As Boolean
  13. On Error GoTo EH
  14.     Dim olApp       As Outlook.Application
  15.     Dim olMail      As Outlook.MailItem
  16.     Dim strArray()  As String
  17.     Dim intAtch     As Integer
  19.     Set olApp = CreateObject("Outlook.Application")
  20.     Set olMail = olApp.CreateItem(olMailItem)
  21.     With olMail
  22.         .To = olSendTo
  23.         .Subject = olSubject
  25.         If SendAsHTML Then
  26.             .BodyFormat = olFormatHTML
  27.             .HTMLBody = olEMailBody
  28.         Else
  29.             .Body = olEMailBody
  30.         End If
  32.         .CC = olCCLine
  33.         .BCC = olBCCLine
  34.         .SentOnBehalfOfName = olOnBehalfOf
  35.         strArray = Split(olAtchs, "%Atch")
  37.         For intAtch = 0 To UBound(strArray)
  38.             If FileExists(strArray(intAtch)) Then _
  39.                 .Attachments.Add strArray(intAtch)
  40.         Next intAtch
  42.         If olDisplay Then
  43.             .Display
  44.         Else
  45.             .Send
  46.         End If
  48.     End With
  49.     Set olMail = Nothing
  50.     Set olApp = Nothing
  52.     SendAnEMail = True
  54.     Exit Function
  55. EH:
  56.     MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
  57.         "Error: " & Err.Number & vbCrLf & _
  58.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  59.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  60.     SendAnEMail = False
  61.     Exit Function
  62. End Function
You will notice that it is not very complicated. We are just creating an e-mail in the same way that you would anywhere else. However, now this function is accessible from anywhere in your project and all you need to do is call it in order to create the e-mail. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.

You may notice that there is one (and only one) argument for attachments. However, you can add multiple attachments from the calling code. Just keep in mind that you need to separate each attached file with the text "%Atch".

You may also notice a reference to another public function named FileExists(). This ensures you are not trying to attach a file that doesn't exist. I think there are about as many versions of such a function as there are programmers, but here is my version (that I originally stole from Allen Browne--props where props are due).

Expand|Select|Wrap|Line Numbers
  1. Public Function FileExists(ByVal strFile As String, _
  2.                            Optional bFindFolders As Boolean) _
  3.                            As Boolean
  4. 'Purpose:   Return True if the file exists, even if it is hidden.
  5. 'Arguments: strFile: File name to look for. Current directory searched if
  6. '                    no path included.
  7. '           bFindFolders: If strFile is a folder, FileExists() returns False
  8. '                         unless this argument is True.
  9. 'Note:      Does not look inside subdirectories for the file.
  10. 'Author:    Allen Browne. June, 2006.
  11.     Dim lngAttributes As Long
  13.     If Not (IsNull(strFile) Or strFile = "") Then
  14.         'Include read-only files, hidden files, system files.
  15.         lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
  17.         If bFindFolders Then
  18.             'Include folders as well.
  19.             lngAttributes = (lngAttributes Or vbDirectory)
  20.         Else
  21.             'Strip any trailing slash, so Dir does not look inside the folder.
  22.             Do While Right$(strFile, 1) = "\"
  23.                 strFile = Left$(strFile, Len(strFile) - 1)
  24.             Loop
  25.         End If
  26.         'If Dir() returns something, the file exists.
  27.         On Error Resume Next
  28.         FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
  29.     Else
  30.         FileExists = False
  31.     End If
  33. End Function
This function can go in the same module, if you wish.

Now, you are ready to "Rock 'n' Roll"!

In your Form, if you want to send an e-mail via clicking a button, here is all you have to do:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SendMail_Click()
  2. On Error GoTo EH
  3.     Dim strSendTo     As String
  4.     Dim strSubject    As String
  5.     Dim strEMailBody  As String
  6.     Dim strCCLine     As String
  7.     Dim strBCCLine    As String
  8.     Dim strOnBehalfOf As String
  9.     Dim strAtchs      As String
  11.     strSendTo = ""
  12.     strSubject = "I Want a Pizza"
  13.     strEMailBody = "I want a pizza <B>NOW</B>!!!"
  14.     strCCLine = ""
  15.     strBCCLine = ""
  16.     strOnBehalfOf = ""
  17.     strAtchs = "C:\File.pdf" & _
  18.                "%Atch" & _
  19.                "C:\Another File.pdf"
  21.     'Generate and Display the E-Mail
  22.     Call SendAnEMail(olSendTo:=strsendto, _
  23.                      olSubject:=strSubject, _
  24.                      olEMailBody:=strEMailBody, _
  25.                      olDisplay:=True, _
  26.                      olCCLine:=strCCLine, _
  27.                      olBCCLine:=strBCCLine, _
  28.                      olOnBehalfOf:=strOnBehalfOf, _
  29.                      olAtchs:=strAtchs, _
  30.                      SendAsHTML:=True)
  32.     Exit Sub
  33. EH:
  34.     MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
  35.         "Error: " & Err.Number & vbCrLf & _
  36.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  37.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  38.     Exit Sub
Obviously, the "Send of Behalf of" feature will only work if you are on a MS Exchange server that allows such things. Additionally, many corporate e-mail servers don't allow for sending directly from outside of Outlook; hence, I just default to displaying the e-mail.


That's about it. It makes sending e-mail a breeze--especially in our office, in which we send out approximately 1,000 various e-mails for each of our six annual projects. Yeah, this saves me a ton of programming time.

If any o' all y'all have any recommended improvements, I am all ears!

hope this hepps!
Aug 29 '18 #1
Share this Article
Share on Google+

Expert Mod 5K+
P: 5,397
. NB: You will need to include the Microsoft Outlook XX.0 Object Library as one of your references.
One can also use late-binding to avoid setting the reference. This is most useful when distributing your application to other clients or when upgrading from one version of Office to another (or downgrading).

For a further discussion on Application Automation:
home > topics > microsoft access / vba > insights > application automation
Aug 31 '18 #2

Expert 5K+
P: 8,634
zmbd brings up a good point regarding Late Binding, but why not have the best of both worlds by using Runtime Conditional Compilation Constants?
Expand|Select|Wrap|Line Numbers
  1. #Const conEARLY_BIND = True
Expand|Select|Wrap|Line Numbers
  1. Public Function SendAnEMail(olSendTo As String, _
  2.                              olSubject As String, _
  3.                              olEMailBody As String, _
  4.                              olDisplay As Boolean, _
  5.                     Optional olCCLine As String, _
  6.                     Optional olBCCLine As String, _
  7.                     Optional olOnBehalfOf As String, _
  8.                     Optional olAtchs As String, _
  9.                     Optional SendAsHTML As Boolean) As Boolean
  10. On Error GoTo EH
  11. Dim strArray()  As String
  12. Dim intAtch     As Integer
  13. Const conEMAIL_ITEM = 0     'If using Late Binding cannot use olMailItem Intrinsic Constant
  15. #If conEARLY_BIND Then
  16.   Dim olApp As Outlook.Application
  17.   Set olApp = New Outlook.Application
  18.   Dim olMail As Outlook.MailItem
  19.   Set olMail = olApp.CreateItem(olMailItem)
  20. #Else
  21.   Dim olApp  As Object
  22.   Dim olMail As Object
  23.   Set olApp = CreateObject("Outlook.Application")
  24.   Set olMail = olApp.CreateItem(0)
  25. #End If
  26. '************************ CODE HAS INTENTIONALLY BEEN OMITTED BELOW ************************
Nov 21 '18 #3

P: 163
I like this approach, the only other upgrade would be to have it pull the email address from a table so you can send to more than one person with out listing them all in the code itself. I have this in one of my database that I put together from the help in the group of awesome programmers. I will post the code if anyone would like to see it tomorrow.
Nov 28 '18 #4

Expert Mod 2.5K+
P: 3,282

Keep in mind that this procedure simply shows the execution of the process. So, it doesn’t matter how you generate the string for the addressees. As long as you build the strings properly, you can send an e-mail from anywhere at any time. Your list of addressees can be quite large, too, as long as each addressee is separated by a semi colon (“;”)—this is my default in Outlook.

The heart of this procedure is to allow you to simply build strings and send an e-mail using a User-Defined procedure, rather than re-creating the wheel every time you have to send a message. This cuts down on the clutter of your projects and allows you to focus more time on development.

Thanks for the input!
Nov 29 '18 #5