Using VBA in Access to Create an Outlook Distribution List (Office 2007)

[Don]

We have an Access 2007 front end to a MS SQL Server database with a bunch of
employee data to include names and e-mails. The goal is to add a VBA
function to the Access front end to generate distribution lists based on
data in the database. I actually had some code that did this which worked
under Access 2003/Outlook 2003. Under the 2007 versions, no luck. Based on
working through examples on MSDN and reviewing information there, here, and
elsewhere, I am beginning to suspect it is a security setting problem with
the Recipient and Recipients objects.



Here is some hack Access 2007 VBA code I have been using to work this
problem:



Public Sub ProblemDemo()

Dim blResult As Boolean

Dim olApp As Outlook.Application

Dim objDist As Outlook.DistListItem



Dim objMail As Outlook.MailItem

Dim colRecips As Outlook.Recipients

Dim objRecip As Outlook.Recipient

Dim objContact As Outlook.ContactItem

Dim objRecipients As Outlook.Recipients

Dim objTempItem As Outlook.MailItem



'

'

' From Office Development Center

' DistListItem Obect
(http://msdn.microsoft.com/en-us/library/bb219943.aspx)

'

'Set myNamespace = Application.GetNamespace("MAPI")

' doesn't work! Using:

Set myNamespace = Outlook.Application.GetNamespace("MAPI")



Set myFolder = myNamespace.GetDefaultFolder(olFolderContacts)

'myFolder.Display

' Opened a new Outlook window containing my contacts folder



Set myItem = myFolder.Items("VBATest")

'myItem.Display

' Opened yet another window displaying the contents of the VBSTest
distribution list.

' Other than the multiple windows, things to this point look okay.



'

' Since we can see what's there and switch to an item, let's try creating a
new

' distribution list

Set olApp = Outlook.Application



Set objDist = olApp.CreateItem(olDistributionListItem)

objDist.DLName = "VBATest_2"

objDist.Save

'objDist.Display

' And this successfully created a new distribution list





Set objContact = olApp.CreateItem(olContactItem)

objContact.Email1Address = "(e-mail address removed)"

'objContact.Display

objContact.Save

' new contact successfully created



Set objTempItem = olApp.CreateItem(olMailItem)

objTempItem.Subject = "test VBA"

objTempItem.Display



Set objRecipients = objTempItem.Recipients

' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

' Run-time error '287':

' Application-defined or object-defined error

' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!





objRecipients.Add myNamespace.CurrentUser.Name

objRecipients.ResolveAll

objDist.AddMembers myRecipients

objDist.Save

objDist.Display



'

' Now create a new recipient

Set objMail = olApp.CreateItem(olMailItem)

Set objRecip = olApp.Session.CreateRecipient("(e-mail address removed)")

' Run-time error '287':

' Application-defined or object-defined error

' objRecip.Resolve

objDist.AddMember objRecip

'Add note to list and display

objDist.Body = "Regional Sales Manager - NorthWest"

objDist.Save

objDist.Display

End Sub





I can create mail items, contacts, and distribution lists. However, when
any attempt is made to use touch Receipients of a mail object, wham:
Run-time error '287':

Application-defined or object-defined error .



The local variables viewer shows the value of objTempItem.Recipients as "<>"
which I am assuming is not good.



So, a couple of questions:

1) Am I correct in my assumption that this is a security setting issue? If
it is, the problem is that our corporate IT folks will likely never change
their policies or make exceptions.



2) If the "Recipients" problem is not security related, what am I missing?
I don't think I have missed anything after looking at all the examples.



3) Is there any other way to create a distribution list entry besides using
a receipient? Since I can create a distribution list in my contacts folder,
it would seem I should be able to populate it somehow. Again, the database
has name and e-mail information, all we are looking to put in the
distribution list entry is a display name and the e-mail address.



4) Is there a way to create a distribution list from contacts? I suppose I
could create a temporary contact to take that route, but the only method I
have seen for creating a distribution list entry involves recipients.



Any asisitance will be greatly appreciated!



Thanks!



Don

 

[Ken Slovak - [MVP - Outlook]]

Outlook 2007 is actually far less restrictive than Outlook 2003 when it
comes to code, especially with code run outside of the Outlook COM process
(COM addins and Outlook VBA code). Unless your admins have locked down
Outlook security the default is that if you have an up-to-date A-V program
that your code is permitted to run without the security prompts you'd see in
Outlook 2003.

You have to use Recipient objects to add members to a DL.

I think the first thing to try would be to set up some simple test code in
the Outlook VBA project. There you can use Application as the
Outlook.Application object, something like this:

Public Sub ProblemDemo()
Dim oNS As Outlook.NameSpace
Dim objRecip As Outlook.Recipient
Set oNS = Application.GetNameSpace("MAPI")
Set objRecip = oNS.CreateRecipient("(e-mail address removed)")
End Sub

See if that produces any errors. Also look at the Trust Center's settings
for addins and macros and security and see if your A-V is listed as
up-to-date and what limitations are listed, if any.

 

[Don]

Ken,

I tried your suggestion of simplifying. While I no longer get the 287
error, objRecip does not contain anything after the assignment. Some
observations:

1) Set oNS = Application.GetNamespace("MAPI") throws a "Method or Data
Member not Found" on GetNamespace when I try to compile or execute. Instead
I use:
Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")
Could this be the cause of the problem with objRecip?

2) Trust Center in Outlook appears to be set to allow programmatic
execution from an application outside of Outlook. The radio button is set
to warn if the anti-virus is out of date or invalid. (The selection is
greyed out since as a regular user I am not allowed to change it.) And it
says the AV is valid (Symantec Corporate edition)

3) objRecip.Application.IsTrusted is set to FALSE -- this is troubling.
The way I read the manual
(http://msdn.microsoft.com/en-us/library/bb207708.aspx) Outlook will not
trust the object and presumably therefore not allow it to be set.

Suggestions?

Thanks!

Don




Here is the code sample:

Public Sub ProblemDemo2()
Dim oNS As Outlook.NameSpace
Dim objRecip As Outlook.Recipient
Dim objDist As Outlook.DistListItem

Dim RecipTrust As Boolean

'Set oNS = Application.GetNamespace("MAPI")
' GetNamespace --> Error: "Method or Data Member not Found"
' When trying to compile or execute.

Set olApp = New Outlook.Application
Set oNS = olApp.GetNamespace("MAPI")

Set objRecip = oNS.CreateRecipient("(e-mail address removed)")
' If I look at objRecip in Locals window, it appears to be
' showing default values. Address is <> as is AddressEntry
' objRecip.Application.IsTrusted is set to FALSE and is a
' READONLY property.
'
RecipTrust = objRecip.Application.IsTrusted

Set objDist = olApp.CreateItem(olDistributionListItem)
objDist.DLName = "VBATest_2"
objDist.Body = "Programmtic List Build Test"

objDist.AddMember objRecip


objDist.Save
objDist.Display

' Trust Center in Outlook
' Anti-virus: Valid
' Set to warn when AV is out of date or invalid
'
' Appears to be set to allow programmtic access from other applications.

End Sub

 

[Ken Slovak - [MVP - Outlook]]

Application.GetNameSpace() only has meaning in an Outlook VBA project. In a
Word VBA project "Application" is Word, in an Excel VBA project it's Excel,
etc. So if not in Outlook then you must use an explicit Outlook.Application
object and instantiate it.

If objRecip.Application.IsTrusted == false then that definitely explains why
you aren't getting a Recipient object, Outlook doesn't trust your code. Why
that is I have no idea.

See if any of the Outlook 2007 specific information at
http://www.outlookcode.com/article.aspx?id=52 gives you any clues as to
what's going on. At least you now know it's a trust issue and not a coding
issue.

 

[Don]

Ken,

You are right regarding narrowing down the problem! Getting info on Outlook
security is a bit of a pain. I think it is obfuscated on purpose to confuse
malcontents!

Regarding the "Application", that is sort of what I thought, but wanted to
be sure.

Thanks for all the help!

Don