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 = "
'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 = et")
' 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