Easy Outlook Email Integration

Over several years of participating in forums, and working on my own projects, I always felt it was a bit awkward to create and send new emails through Excel. Invariably, every time I found that I needed email code, I ended up heading off to a site to copy an example (usually from my colleague Ron de Bruin's excellent site), then customizing to make it work.

The goal of this article is to provide an even easier way to add email functionality to your Excel (or any other Office) project... something easy enough for beginner coders to use as effectively as master coders. I wanted a re-usuable chunk that I could just drop into my project with ease, and I believe I've accomplished that here.

The steps are actually quite simple:

  1. Download and unzip the class module at the end of this article
  2. Import it into your project (in the VBE, right click your project name, click Import, and select the file)
  3. Add a new module to your project
  4. Code a simple routine to send your email.

If the last line sounds intimidating, don't let it be. It's actually very simple.

Basic Code Container Required:

Code:
Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add email details here...

    End With

    'Release the email object
    Set oEmail = Nothing
End Sub

As long as you have that, we can start to code the details we want.

Email Methods:

Adding Recipients:
You can add as many recipients as you'd like, including To, CC and BCC, using the following methods.
  • AddToRecipient
  • AddCCRecipient
  • AddBCCRecipient
Subject and Body:
The Subject and Body can be added using these methods.
  • Subject
  • Body
Signatures
To add a signature, you only need to provide the name of the signature (not the path). Do be aware, however, that you can use HTML signature, but not those containing imags, as they do not embed properly in Outlook. HTML signatures containing images are ignored.
  • AddSignatureHTML
  • AddSignatureText
Attachments
You can add as many attachments as you'd like using the following method.
  • AttachFile
Sending/Previewing
If you'd like to have Outlook create an email but not send it, use the Preview method. If you'd just like to send it without reviewing it, the use the Send method.
  • Preview
  • Send

Examples:
So let's look at how this works...

Example 1

Create an email to one person with an attachment. (To send automatically, just change .Preview to .Send)

Code:
Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "To_someone@somedomain.com"
 
        'Set the subject
        .Subject = "The file you requested"

        'Set the body
        .Body = "Here is the file you requested."
 
        'Add an attachment
        .AttachFile = "D:My DocumentsReport.xls"

        'Preview the email (or use .Send to send it)
        .Preview
    End With
    'Release the email object
    Set oEmail = Nothing
End Sub

Example 2
Create an email to one person, CC two people, with two attachments. (To send automatically, just change .Preview to .Send)

Code:
Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "To_someone@somedomain.com"

        'Add a couple of people to CC on the email
        .AddCCRecipient = "CCing_someone@somedomain.com"
        .AddCCRecipient = "CCing_someone_else@somedomain.com"

        'Set the subject
        .Subject = "The files you requested"

        'Set the body
        .Body = "Here are the files you requested."

        'Add a couple of attachments
        .AttachFile = "D:My DocumentsSpecial Report.xls"
        .AttachFile = "D:My DocumentsAnother Report.xls"

        'Preview the email (or use .Send to send it)
        .Preview
    End With
    'Release the email object
    Set oEmail = Nothing
End Sub

The key point to notice here is that, to add another person of file... just add another one. Nothing overly complicated!

Example 3
Email two people, Carbon Copy one person, Blind Carbon two and add a plain text signature. (To send automatically, just change .Preview to .Send)

Code:
Public Sub EmailViaOutlook()'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "To_someone@somedomain.com"
        .AddToRecipient = "To_someoneelse@somedomain.com"

        'Add a couple of people to CC on the email
        .AddCCRecipient = "CCing_someone@somedomain.com"

        'Add a blind carbon recipient
        .AddBCCRecipient = "BlindCarbon_someone@somedomain.com"
        .AddBCCRecipient = "BlindCarbon_someoneelse@somedomain.com"

        'Set the subject
        .Subject = "The files you requested"

        'Set the body
        .Body = "Hi there" & vbNewLine & vbNewLine & _
                "Here are the files you requested."

        'Add a signature
        .AddSignatureText = "My Signature"  '<-- needs to be the name of your signature file from Outlook

        'Preview the email (or use .Send to send it)
        .Preview
    End With
    'Release the email object
    Set oEmail = Nothing
End Sub

As always, comments are welcome. For additional help on implementing this in your projects, please post in the forums.

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »