The Six Hat Send Mail Class (Easily Send Email Using VBA)

• Download SixHatSendMail.zip - 12.6 KB

Introduction

You would assume that since Microsoft Office documents, spreadsheets, and reports etc. get emailed more than any other attachment that it would be a slam dunk to send emails using Office Automation. Unfortunately, that’s not always the case. Why? Because all of the current methods of sending email from Office rely on the environment the Office application resides in. So if your application is sitting on a server that emails through Outlook then most likely you will end up sending your emails using Outlook Automation etc.

All of these methods from CDO to Outlook rely on SMTP. So if your environment doesn’t have SMTP or an SMTP server setup and configured then you will not be sending emails at all. That is until now. Last year I wrote an article on “Emailing From Microsoft Access The Easy Way” using the SendGrid API. After working out a few of the kinks with the method it has been a great success. I’ve decided to streamline this method and make it even simpler to use by wrapping it up in a class called: “The Six Hat Send Mail Class”. Now with just a few lines of code you can send emails with multiple attachments with the greatest of ease. How simple? Check out some examples below:

Background

Based off the article that I wrote last year: https://www.linkedin.com/pulse/email-from-microsoft-access-easy-way-anthony-griggs?trk=mp-reader-card

SendGrid API V2: https://sendgrid.com/docs/API_Reference/Web_API/mail.html

Using The Six Hat Send Class:

1. To begin using the class you must first download the class file attached to this article. 

2. Instantiate the class within your code. Example:

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String     

   Set SHSM = New SixHatSendMail

Note: As well as declaring the class I've also declared a String variable for Attachments as well as a variable to return the Email Status directly from the SendGrid API

3. Next just to keep the example simple I am going to hard code a file path into the attachment variable. A couple points about adding attachments:

• Attachments are a collection so you can add as many as you want.
• Each attachment must be no more than 7mb. (Limit according to the SendGrid API)
• To add an attachment simply insert the full file path including file name.

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
   Set SHSM = New SixHatSendMail
   eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

4. Next I'll add a With block to the instantiated class

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
   Set SHSM = New SixHatSendMail
   eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt" 

   With SHSM 
  
   End With

5. Within the With block we will provide our first argument: [EmailTo]. It's important to note that [EmailTo] is a collection. You can provide as many [EmailTo]'s as you like. The [EmailTo] argument excepts two paramaters. 1. (Required): EmailTo and 2. (Optional) EmailToName (The name of the party you are sending the email to)

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
  Set SHSM = New SixHatSendMail
    eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

    With SHSM
        .EmailTo Nz(Me.txtTo, ""), Nz(Me.txtToName, "")

Since [EmailTo] is a collection you can send this email to several people simply by wrapping [EmailTo] into a loop that fits your situation. Here is a full example emailing to all contacts in a loop:

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    Dim rs As DAO.RecordSet

    Set rs = CurrentDb.OpenRecordset("Select * FROM tblContacts")
    Set SHSM = New SixHatSendMail

    If Not (rs.EOF And rs.BOF) Then
         DoWhile Not rs.EOF
              With SHSM
                  .EmailTo Nz(rs.Fields("Email"), ""), Nz(rs.Fields("ContactName"), "")
                  ' Email Body is For Sending HTML Set Argument to True If RTF (Rich Text Format)
                  .EmailBody Nz(Me.txtBody, ""), True
                  ' Set Class Properties
                  .EmailFrom = Nz(Me.txtFrom, "")
                  .EmailReplyTo = Nz(Me.txtReplyTo, "")
                  .EmailSubject = Nz(Me.txtSubject, "")
                  .EmailText = Nz(Me.txtText, "")
             For Each attachment in Attachments
                  ' Add Attachment(Must Be Full Path To File Including File Name)
                  .EmailAttachment = attachment
             Next
                  ' Send Email
                  .SendEmail
                  ' Return Email Status
                  eStatus = .EmailStatus
                  rs.MoveNext
          Wend
         MsgBox "Email has been sent with a returned status of: " & eStatus
    End If
    End With
         

Note: That in the example above I did not actually send mutiple email addresses to the [EmailTo] collection I am actually sending a seperate Email to each person in my contact list. I could have added multiple email addresses to the [EmailTo] collection and sent out a single email... The reason why I usually choose not to use that approach is that it adds a lot of email addresses in the header... anyone who received an email address like this would have all of your contacts. Thus I usually avoid that approach although it performs much better.

However, a good example of adding multiple values to a collection within this class has been provided in the example aboveabove with attachments.

To keep it as simple as possible I am not using any loops within our primary example. So let's go back to our primary example.

6. The next argument we will provide are: EmailFrom, EmailReplyTo, EmailSubject, and EmailText. Note these are single value arguments so you provide them a little differently than we did for EmailTo.

Note: the only required arguments for this class are: EmailTo, EmailFrom, AND either (EmailText OR EmailBody) all the rest are optional. A gotcha to know about however is that none of the class arguments will accept Null. They will however accept an empty string. Thus you will note that I have wrapped all of my values with the Nz() Function.

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
  Set SHSM = New SixHatSendMail
    eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

    With SHSM
        .EmailTo Nz(Me.txtTo, ""), Nz(Me.txtToName, "")
        .EmailFrom = Nz(Me.txtFrom, "")
        .EmailReplyTo = Nz(Me.txtReplyTo, "")
        .EmailSubject = Nz(Me.txtSubject, "")
        .EmailText = Nz(Me.txtText, "")

EmailText OR EmailBody?

If you are sending a plain text message and don't care about formatting and line break placement then EmailText will work just fine. However if you care about formatting then most likely you will want to specify your formatting using EmailBody which excepts HTML so that you can format your document how you want it.

With Microsoft Access in mind (but also other members of the Office Suite) I threw in a Rich Text Format (RTF) to HTML converter. Thus you can format your RTF source how you want it then set the RTF to HTML option value to true like this:

        ' Email Body is For Sending HTML Set Argument to True If RTF (Rich Text Format)
        .EmailBody Nz(Me.txtBody, ""), True

As Illustrated above within Microsoft Access. I am using a textbox with the Text Format property set to "Rich Text". This way I can keep my applied formatting. If you look at the code snippet above I've set the EmailBody value to the RTF textbox name and then set the IsRTF option to True. Setting the RTF option to true will convert the Rich Text Format to HTML.

7. Email Attachments. The EmailAttachment argument takes the full file path including file name. I've already provided an example of adding multipe attachments above. But in our primary example we are going to keep it simple and using the hard coded file path we added to our eAttachment variable earlier

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
  Set SHSM = New SixHatSendMail
    eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

    With SHSM
        .EmailTo Nz(Me.txtTo, ""), Nz(Me.txtToName, "")
        .EmailFrom = Nz(Me.txtFrom, "")
        .EmailReplyTo = Nz(Me.txtReplyTo, "")
        .EmailSubject = Nz(Me.txtSubject, "")
        .EmailText = Nz(Me.txtText, "")
        ' Add Attachment(Must Be Full Path To File Including File Name)
        .EmailAttachment = eAttachment

8. Send Email: We next Send the Email simply by calling the SendEmail procedure like so:

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
  Set SHSM = New SixHatSendMail
    eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

    With SHSM
        .EmailTo Nz(Me.txtTo, ""), Nz(Me.txtToName, "")
        .EmailFrom = Nz(Me.txtFrom, "")
        .EmailReplyTo = Nz(Me.txtReplyTo, "")
        .EmailSubject = Nz(Me.txtSubject, "")
        .EmailText = Nz(Me.txtText, "")
        ' Add Attachment(Must Be Full Path To File Including File Name)
        .EmailAttachment = eAttachment
        ' Send Email
        .SendEmail

9. Getting The Email Status. After you've sent the email you can return the email success status. To do this I'm going to use the eStatus variable we declared at the begining to capture the value. Lastly I like to add a message box or some kind of feedback that Email(s) have been successfully sent. Since I am only sending out a single email in this primary example then I added the Status to the message. See the full example below:

Private Sub SendEmail()
    Dim SHSM As SixHatSendMail
    Dim eAttachment As String
    Dim eStatus As String
    
    Set SHSM = New SixHatSendMail
    eAttachment = "C:\Users\sixHat\Desktop\Email Header.txt"

    With SHSM
        .EmailTo Nz(Me.txtTo, ""), Nz(Me.txtToName, "")
        .EmailFrom = Nz(Me.txtFrom, "")
        .EmailReplyTo = Nz(Me.txtReplyTo, "")
        .EmailSubject = Nz(Me.txtSubject, "")
        .EmailText = Nz(Me.txtText, "")
        ' Add Attachment(Must Be Full Path To File Including File Name)
        .EmailAttachment = eAttachment
        ' Send Email
        .SendEmail
        ' Return Email Status
         eStatus = .EmailStatus
         MsgBox "Email has been sent with a returned status of: " & eStatus
    End With
End Sub

Setting SendGrid Credentials

You can set your SendGrid Credentials during Each Call for example:

With SHSM
     .SendGridUser = "Your_SendGrid_UserName"
     .SendGridPassword = "Your_SendGrid_Password
End With

Or if you look at the top of the class you can set these credentials globally:

' @@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET SEND GRID DEFAULT CREDENTIALS 
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET SEND GRID DEFAULT CREDENTIALS 
Private Const SEND_GRID_USER = "Your_SendGrid_UserName" 
Private Const SEND_GRID_PASS = "Your_SendGrid_Password" 
Private Const SEND_GRID_KEY = "" 
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET SEND GRID DEFAULT CREDENTIALS 
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@ SET SEND GRID DEFAULT CREDENTIALS