Sending email from MS Access

When a new accession is added to our collections management database an email is sent to several of the Kheel Center staff. The email includes the new accession’s collection number and title. The VBA code for doing this is triggered when the Save button is clicked on the New Collections form.

One thing you need to do to make this code work is go to Tools in the VBA Editor and under References make sure that the “Microsoft Outlook 12.0 Object Library” is enabled. If Outlook is open on the machine Access will use that account to send the email, if Outlook is not open Access will open Outlook prompt you for your Outlook log in. Note that once you enable the Outlook Object Library the database will no longer work on a machine which does not have Microsoft Outlook installed.

First declare a few variables:

     Dim strCollNum As String           'Variable for the Collection Number.
     Dim strTitle As String                  'Variable for the Collection Title.
     Dim strTo As String                    'Variable for recipients’ email addresses
     Dim appOutLook As Outlook.Application

     Dim MailOutLook As Outlook.MailItem

     Dim NameSpace As NameSpace

     Dim Folder As Folder

I pick up the collection number and title from the New Collections form

    strCollNum = Nz(Me.CollectionNumber, "No Collection Number Assigned")

    strTitle = Me.CollectionTitle

    'List the recipients for the email

    strTo = "FirstRecipient@somewhere.com; SecondRecipient@elsewhere.edu"

 
This is the code that actually sends the email:

     Set appOutLook = CreateObject("Outlook.Application")

     Set NameSpace = appOutLook.GetNamespace("MAPI")

     Set Folder = NameSpace.GetDefaultFolder(olFolderInbox)

     Set MailOutLook = appOutLook.CreateItem(olMailItem)

     With MailOutLook

          .To = strTo

          'Insert the collection number in the subject line.
          .Subject = "New Collection " & strCollNum & " Accessioned"

          'Insert the collection number and title in the body of the message.
           .Body = "I have created a record for " & strCollNum & ": " & strTitle

          'If Left(Me.Mail_Attachment_Path, 1) <> "<" Then

               '.Attachments.Add (Me.Mail_Attachment_Path)

          'End If

          '.DeleteAfterSubmit = True

          .Send

     End With

The commented-out lines of code can be uncommented if you want to include an attachment, which I have not tried and not keep the email in your Sent Items folder.