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 "firstname.lastname@example.org" .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.