Avoid Outlook Integration - Send Email using SMTP via GMail

Outlook integration in MS Access is a real pain. Not only is it prone to version issues, but the "Outlook Security" debacle is still fresh in the minds of many a developer. Although integration with the rest of the Office suite (particularly Excel) is a wonderful feature of Access, I find it best to avoid any dependency on Outlook. Not only does it depend on your client having a properly configured Outlook account, any emails sent from your app will show the user's email as the "From" address, which can be undesirable behavior.  So, if you're trying to get away from using methods like SendObject or (for more advanced developers) Outlook automation, what options do you have? The best option, in my opinion, is using GMail.

GMail? How could I automate a web-based email service through a desktop database, you might ask. In the past, Microsoft made this easy by packaging the Collaboration Data Objects (CDO) dll that included the SMTP schema directly within Windows. That stopped with Windows Vista, and many developers gave up on sending via SMTP and reluctantly moved to Outlook integration. Even if you are able to download the CDO libraries, you are still stuck with the problem of packaging the dll with your application and risking broken reference issues. However, it is possible to access Microsoft's SMTP services schema directly, without dealing with dll's, to push your email out. Here's how:

1. Set up a GMail account for your application to send through (e.g., your.app@gmail.com).

2. Place the following code module in your application (with appropriate changes for your app).

3. Call the function from your application's code using the "Usage" suggestion in the module header. Note that you can send to multiple recipients by separating them with a semicolon (;) and attach multiple files using the same method.

4. Enjoy having total control over your emails!

Option Compare Database
Option Explicit

'***********************************************************
'  Module SendViaGMail
'  Written by Topher Ritchie, tophersaccesstips.blogspot.com
'  Send emails via a GMail account
'  Usage:
'  SendSMTP("user1@email.com; user2@email.com, etc.","Subject string", _
'      "Body string","C:\Path\To\Attachment1.file; C:\Path\To\Attachment2.file, etc.")
'  Returns a Boolean value of True if no errors were noted.
'  You are free to use, modify, or distribute this code, provided
'  this header remains in place.
'***********************************************************

Public Function SendSMTP(ToString As String, Optional SubjectString As String, Optional BodyString As String, Optional AttachmentString As String) As Boolean
Dim iCfg As Object
Dim iMsg As Object
Dim i As Integer
On Error GoTo Err_Handler:

    Set iCfg = CreateObject("CDO.Configuration")
    Set iMsg = CreateObject("CDO.Message")

    With iCfg.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your.app"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your.password"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "your.app@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Update
    End With

    With iMsg
        .Subject = SubjectString
        .To = ToString
        .HTMLBody = BodyString
        If Not (IsMissing(AttachmentString) Or Nz(AttachmentString) = "") Then
            For i = 1 To CountInStr(AttachmentString, ";") + 1
                .AddAttachment Left(AttachmentString, IIf(Nz(InStr(AttachmentString, ";")) = 0, Len(AttachmentString), Nz(InStr(AttachmentString, ";")) - 1))
                AttachmentString = Right(AttachmentString, IIf(Nz(InStr(AttachmentString, ";")) = 0, Len(AttachmentString), Nz(InStr(AttachmentString, ";")) + 1))
            Next i
        End If
     
         Set .Configuration = iCfg
        .Send
    End With

    Set iMsg = Nothing
    Set iCfg = Nothing
 
    SendSMTP = True
 
Err_Exit:
    Exit Function

Err_Handler:
    SendSMTP = False

Resume Err_Exit

End Function


Public Function CountInStr(StringToCheck As String, StringToFind As String) As Long
    CountInStr = (Len(StringToCheck) - Len(Replace(StringToCheck, StringToFind, ""))) / Len(StringToFind)
End Function