• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Send Email Through Outlook with VBA

November 23, 2015 by Ryan McCormick 1 Comment

I have to say that I have been super hesitant to share this example as it could be used by spammers. That being said, there are plenty of legit uses for sending email through Outlook with VBA.

I have used this function primarily to automate the delivery of scheduled reports where I pull in an HTML template (fancy), replace template “placeholders” and have the report sent to a distribution list defined in an access database table. I won’t go into details about how to get ‘fancy’ but rather let you as an end-user modify to your needs.

Provided are two examples, one with early binding and another with late binding code. Starting off, here is a test function you can reference for calling the sendmail function examples.

Send Email with VBA through Outlook – Test Function

'----------------------------------------------------
' Test Function. Change the 'mail@example.com' string
' to your Email address
'----------------------------------------------------
Sub test()
    Call sendEmail("mail@example.com", "This is a test", "This is a test")
End Sub

Send Email Through Outlook with VBA – Early Binding Example

'----------------------------------------------------
' Send Email through Outlook with VBA (Early Binding)
'----------------------------------------------------
' NOTE: To use this code, you must reference
' The Microsoft Outlook 14.0 (or current version)
' Object Library by clicking menu Tools > References
' Check the box for:
' Microsoft Outlook 14.0 Object Library in Outlook 2010
' Microsoft Outlook 15.0 Object Library in Outlook 2013
' Click OK
'----------------------------------------------------
Sub sendEmail(msgTo, msgSubject, msgBody)
    Dim oApp As Outlook.Application
    Dim oMail As MailItem
        
    ' Initialize Outlook and Set Objects
    '------------------------------------------
    Set oApp = New Outlook.Application
    Set oMail = oApp.CreateItem(olMailItem)
    
    'Set Options
    With oMail
        'Message format set
        'HTML, Plain, RichText or Unspecified
        '--------------------------------------
        .BodyFormat = olFormatHTML
        
        'Set Recipient(s)
        '--------------------------------------
        .To = msgTo
        
        'Set Subject Line
        '--------------------------------------
        .Subject = msgSubject
        
        'Body Text
        '--------------------------------------
        .HTMLBody = msgBody
    End With
    
    'Send the message
    '------------------------------------------
    oMail.Send
        
    ' Quit Outlook Application After Send
    '------------------------------------------
    ' If you want to leave outlook running
    ' in the background after sending your
    ' message, leave 'oApp.Quit' commented out
    '------------------------------------------
    'oApp.Quit
    
    ' Clean Memory
    '------------------------------------------
    Set oApp = Nothing
    Set oMail = Nothing
End Sub

Send Email Through Outlook with VBA – Late Binding Example

'----------------------------------------------------
' Send Email through Outlook with VBA (Late Binding)
'----------------------------------------------------
' NOTE: This is the late binding version of the
' Send Email through Outlook with VBA code. No ref
' to Microsoft Outlook XX.0 Object Library is needed
'----------------------------------------------------
Sub sendEmail(msgTo, msgSubject, msgBody)
    Dim oApp As Object
    Dim oMail As Object
        
    ' Initialize Outlook and Set Objects
    '------------------------------------------
    Set oApp = CreateObject("Outlook.Application")
    
    ' (0) = olMailItem for late binding
    '------------------------------------------
    Set oMail = oApp.CreateItem(0)
    
    'Set Options
    With oMail
        'Message format set (enum)
        'HTML(2), Plain(1), RichText(3) or Unspecified(0)
        '--------------------------------------
        .BodyFormat = 2
        
        'Set Recipient(s)
        '--------------------------------------
        .To = msgTo
        
        'Set Subject Line
        '--------------------------------------
        .Subject = msgSubject
        
        'Body Text
        '--------------------------------------
        .HTMLBody = msgBody
    End With
    
    'Send the message
    '------------------------------------------
    oMail.Send
        
    ' Quit Outlook Application After Send
    '------------------------------------------
    ' If you want to leave outlook running
    ' in the background after sending your
    ' message, leave 'oApp.Quit' commented out
    '------------------------------------------
    'oApp.Quit
    
    ' Clean Memory
    '------------------------------------------
    Set oApp = Nothing
    Set oMail = Nothing
End Sub

As always please comment with questions, improvements, etc…

Related

Filed Under: Microsoft Access, Microsoft Excel, Microsoft Outlook, VBA Tagged With: outlook, send email, vba

Reader Interactions

Comments

  1. Ron says

    August 23, 2016 at 6:15 am

    Thank you for the post. I support nearly 50 Access databases and was looking for a “Late Binding” option. Since the office is currently transitioning from Office 2007 to 2013, I need to replace a lot of “Early Binding” code in order for these different formats to co-exist on our network. This looks like just what I needed.

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in