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

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)
Who is Participating?
richardhubbardConnect With a Mentor Author Commented:
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
Chris BottomleySoftware Quality Lead EngineerCommented:


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

Chris BottomleySoftware Quality Lead EngineerCommented:
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

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

>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

Chris BottomleySoftware Quality Lead EngineerCommented:
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.

Yeah, thanks for the tip Richard
Shanmuga SundaramDirector of Software EngineeringCommented:
Closed, 250 points refunded.
Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.