[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-05
Medium Priority
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
ID: 20020297
See http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_22703450.html


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
ID: 20020305
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

ID: 20027343
>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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

richardhubbard earned 0 total points
ID: 20029448
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
ID: 20029545
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

ID: 20029554
Yeah, thanks for the tip Richard
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20786507

Expert Comment

ID: 20898531
Closed, 250 points refunded.
Community Support Moderator

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

873 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