If you use the later versions of Microsoft Access, you will have probably come across the tool to set and manage emails to normally Microsoft Outlook from a selected table or query and select individual valid records but this can be problematic with connection, server and services if not correctly configured.

There is a custom built solution which talks about sending email using Microsoft Access though it is not a free utility, it does have a free locked demo to evaluate and the following video (5 mins) explains a bit more about it…

If you are feeling brave enough, you can build your own version and in fact the author of this tool has started by giving you some Access VBA code too!

Sending Email Using Microsoft Access VBA

So here’s the main Access VBA code for you to adopt along with a simple form which is really very basic and is purely here to demonstrate the code action of either creating or sending emails via Outlook.

The form would normally consist of a number of textboxes, checkboxes, drop-down controls of a typical email screen – just look at your Outlook email and copy and rebuild the controls and functionality as reference. In my simple example, it will look like this:

The red text highlights are the names for the control so you can marry up the VBA code. Remember to name controls – makes life a lot easier!

In a separate module via the microsoft Access database VBA editor view, we need to create the main code procedure as shown below:

Public Sub SendEmailOutlook(strTo As String, strSubject As String, strBody As String, _
    strFile As String, Optional bFileAttach As Boolean = False, Optional bPreview As Boolean = False)
    
    On Error GoTo SendEmailOutlookErr
    
    Dim strEmail As String
    Dim strMsg As String
    Dim oLook As Object
    Dim oMail As Object
    
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    With oMail
        .ReplyRecipients.Add "ben@accessdatabasetutorial.com"
        .to = strTo
        '.body = sBody
        .htmlbody = strBody
        .Subject = strSubject
        If strFile <> "" Then
            .Attachments.Add (strFile)
        End If
        If bFileAttach = True Then
            'THIS IS WHERE YOU CODE YOUR FORM TO ATTACH FILE(S)...
            '.Attachments.Add (CurrentProject.Path & "\XYZ.XXX")
        End If
        If bPreview = True Then
            .display
        Else
            .Send
        End If
    End With
    
    If bPreview = False Then
        Set oMail = Nothing
        Set oLook = Nothing
    End If
    Exit Sub
    
SendEmailOutlookErrExit:
        Exit Sub
    
SendEmailOutlookErr:
        MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
        Resume SendEmailOutlookErrExit
End Sub

Copy the above code and amend if necessary. You will need to still add, edit and enhance the code here but as things stand for now, it will work.

Back to your sample form, on the cmd_Send command button, locate the On Click event from the Property Sheet’s Event tab and choose [Event Procedure].

Now add the following line of code in between the signatures for this control so it will look something like:

Save both the form and module and run the form.

The next step will be to stop and understand the Access VBA code which is your homework this week 🙂

Of course, you are not just going to refine the above to be more flexible and end up with a richer tool, but you will need to add more code to record data and log emails since this is after all a database application and the reason you are going to fire emails from such a system in the first place.

For now though, sending email using Microsoft Access using VBA code is a better approach than the built in tool supplied.

To learn more about form designs, check out my eBooks and offers. You know the drill by now they all come with 30 day email support and 100% money back guarantee if not satisfied.

Tags: access vba and email sending, email in ms access, how to send email via microsoft access, Microsoft Access VBA, using access vba