• 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 Documents\Report.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 Documents\Special Report.xls"
            .AttachFile = "D:\My Documents\Another 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.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 3 Comments
    1. stulesley's Avatar
      stulesley -
      Ken,
      This exactly what I have been looking for and it works great!! If you ever decide to work on this some more there are a couple of things that would be helpful:

      Get addresses, etc. from specific tab in the workbook
      Open up the address lookup so we could navigate to the contact list

      Do you know of any customer who's ever satisfied? Doesn't it always go like "That is great but ..." so I completely understand if you accept the kudos and ignore the request.

      Thanks again,

      Stu
    1. Ken Puls's Avatar
      Ken Puls -
      Hey Stu,

      Honestly, the purpose of this code was to keep the coding task very simple.

      I did just create a solution for a poster based on this though, where we pulled the emails out of a table (you can find that here if you're interested.)
    1. dcris's Avatar
      dcris -
      Ken,
      Maybe it's just me but I couldn't get an HTML signature without an image to get included at the bottom of the email. I modified your code in the CreateMessage procedure as follows and then it worked:

      Code:
              'Add body
              If Len(sSignatureTextPath) > 0 Then
                  'Body and plain text signature
                  .Body = sBody & vbNewLine & vbNewLine & SignatureText(sSignatureTextPath)
      
              ElseIf Len(sSignatureHTMLPath) > 0 Then
                  'Convert body to HTML and append signature
                  '.HTMLBody = ConvertTextToHTML(sBody) & "<br><br>" & sTemp
                  .HTMLBody = ConvertTextToHTML(sBody) & "<br><br>" & SignatureText(sSignatureHTMLPath)
              Else
                  'Body with no signature
                  .Body = sBody
              End If
      I did try one signature that had an image and got the "no images allowed" error message. I then changed the signature to my "reply" signature which has all the same text and formatting, but no company logo. When it wouldn't insert into the email (and there was no error) I started debugging the process and determined sTemp had no value. After modifying it to use SignatureText, it worked.

      Thanks for the code! I've got a couple of users who are loving the functionality to automate sending tens (if not hundreds) of emails each week. I've got a loop that reads info from rows of data in the spreadsheet and sends an email with a specific attachment for each row with data. The process used to be manual and take 4-8 hrs per week. Now it only takes an hour at most. My last modification will be to let the user decide which signature to use based on the outgoing emails -- some are sent under different program group names so the signature needs to be changed.

      Kudos!

      Diana
  • MVP Logo
  • Recent Forum Posts

    needexcelhelp

    Convert large dataset into 2 columns into multiple columns

    Hi. I have a large dataset from multiple experiments that are all in two columns, when in reality they should be split into 60 or so columns from 30 separate...

    needexcelhelp Yesterday, 11:20 PM Go to last post
    Hercules1946

    week num

    Excels weeknum function can result in a week 54 in some years! This is because:
    1. 1st January is always Week 1.
    2. But .... there is also...

    Hercules1946 Yesterday, 11:16 PM Go to last post
    myk2thestar23

    Highlighting Cells based on Date

    I need help trying to accomplish this to save me some manual work.

    I have an excel spreadsheet where I need to Highlight cells in Column...

    myk2thestar23 Yesterday, 11:10 PM Go to last post
    jonespandrew

    WEEKnUM AND PIVOT TABLES

    Hi Thanks

    However the forumula used in previous post is showing 31/12/17 as week 53 when it should be 52 how do i alter the formula to correct...

    jonespandrew Yesterday, 07:00 PM Go to last post
    jonespandrew

    week num

    Hi thanks for this, the only issue now is that it is shows 31/12/17 as week 53 when infact it is week 52, what do i need to do to alter this....

    jonespandrew Yesterday, 06:59 PM Go to last post