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:

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

• AttachFile

• 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)

Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "[email protected]"
 
        '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)

Public Sub EmailViaOutlook()
'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "[email protected]"

        'Add a couple of people to CC on the email
        .AddCCRecipient = "[email protected]"
        .AddCCRecipient = "[email protected]"

        '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)

Public Sub EmailViaOutlook()'Create the email object
    Dim oEmail As New clsOutlookEmail
    With oEmail
        'Add a recipient
        .AddToRecipient = "[email protected]"
        .AddToRecipient = "[email protected]"

        'Add a couple of people to CC on the email
        .AddCCRecipient = "[email protected]"

        'Add a blind carbon recipient
        .AddBCCRecipient = "[email protected]"
        .AddBCCRecipient = "[email protected]"

        '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.