Automation - Sample code to send email (via Outlook) from MS Access 2007 versions

Posted on 2007-10-05
Last Modified: 2008-05-01
For many years I have used code that sends email from MS Access via Outlook.
That same code does not work with the 2007 versions of Access and Outlook. I can not find any sample VBA code search Microsofts' site or internet in general.
Does anyone have working code that enable emails to be sent using MS Access and Outlook 2007.
(Plse note I have code with works for 2003 version, but it does not work for 2007)
Question by:richardhubbard
    LVL 59

    Expert Comment

    by:Chris Bottomley


    Dim mail_item As mailitem

        Set mail_item = CreateItem(0)
        With mail_item
            .To = my_toaddress_parameter
            .Subject = my_mail_Subject_or_constant_string
            .Body = some_kind_of_info_string
    '        .Send
        End With

        Set mail_item = Nothing

    LVL 59

    Expert Comment

    by:Chris Bottomley
    I am unaware of anything that may have changed in 2007, you should perhaps check to ensure the outlook reference is included as well?

    Alt + F11 to open VBE
    Tools : References

    Lok for a tick in the Microsoft Outlook Object library

    LVL 27

    Expert Comment

    >you should perhaps check to ensure the outlook reference is included as well?
    why you have your code like this, then you need to put the Refence to Outlook:
    Dim oApp As Outlook.Application

    However, if your code is like this, then you don't need a Reference:
    Dim oApp As object

    LVL 1

    Accepted Solution

    Many thanks for your comments.

    The sample code and link to vb solution was a problem cos the code did not work in vba without being edited. But comment that nothing should have changed was helpful as it meant I needed to look for the issue outside of MS Access and the vba code I had.

    As it turned out, while testing it came with an error daying Outlook did not have a "Data File", can not recal the exact words. But it pointed me to the Mail icon in Control Panel and I had to reset the defaut Datafile.

    Once done everything worked fine.

    However one line of code did need to change to make it work in 2007 from my previous version which worked in 2003 and below

     Set objOL = CreateObject("Outlook.Application") was not picked up with debug, but would not run
    until it was changed to  Set objOL = Outlook.Application when used in 2007

    Hope these added comments are helpful to others working through the same issues
    LVL 59

    Expert Comment

    by:Chris Bottomley
    Makes sense in the light of something i've just been working on where I pointed out that the VBA interface in outlook 2003 was inferior in regard to checking for existing instances of outlook as well as switching to them.  I commented therein that the 2007 interface may be improved, (cannot afford to upgrade so cannot say myself for sure) and your observation implies that Outlook 2007 VBA is now improved towards the capabilities of the other office apps ... which is good.

    I do need to look again at the reference material though as I didn't notice that change hence my initial comment, (since it is the same as other apps I suspect I missed the connection rather than it wasn't presented).

    Glad the comment helped a bit though and thanks for the info.

    LVL 27

    Expert Comment

    Yeah, thanks for the tip Richard
    LVL 17

    Expert Comment

    by:Shanmuga Sundaram
    LVL 1

    Expert Comment

    Closed, 250 points refunded.
    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    C# primary key 9 46
    Order by 8 19
    update query variable with apostrope 7 16
    Access 2013 combo box not working 3 10
    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now