Export/Add all the email addresses to a distribution list from a table.

M

Maz

Hi

I am new to VB/VBA. I am just wondering if there is any way to add
multiple addresses to a distribution list via code or other means.

I have a table in MS Access (or I can get that in Excel). The column
name is EmailAddresses in the table.
I am looking for a macro or something that can export all the email
addresses to a distribution list.

Please Help

Thanks

MS
 
L

loadhigh

Hi! Maz
I have programmed a excel file with the macro can export the email
address in the outlook 's contact.
Here is the link to the file( zipped by rar)
http://club.excelhome.net/viewFile.asp?Boardid=22&ID=15731

The excel file is Chinese Verson,but the code below is the macro in the
excel file( please reference the "microsoft outlook application .." before
run it):

Sub Output_Contact()

Dim myolapp As New Outlook.Application

Set myNameSpace = myolapp.GetNamespace("MAPI")
Set myAddressList = myNameSpace.AddressLists("???") 'Here is in Chinese
verson 's code, In english verson,it maybe "Set myAddressList =
myNameSpace.AddressLists("Personal Address Book") " or " Set myAddressList
= myNameSpace.AddressLists("Global Address List")"

Set myaddressentries = myAddressList.AddressEntries

For i = 1 To myaddressentries.Count

With Sheet1

.Cells(i, 1) = myaddressentries(i).Name
.Cells(i, 2) = myaddressentries(i).Address

End With

Next


End Sub
 
M

Maz

Thanks very much, that code is very useful. I will try this and try to
work out. I was just wondering if there is an english version of the
link that you sent. It also asks for username/password.

Regards
MS
 
M

Maz

Hi

I tried the following code below. It works perfectly OK with less
email addresses (5 or 10) but when the list gets large (eg 50 plus
addresses) it does not work. It creates an empty distribution list.
Am I missing here something or is there a limit for exporting more
number of email addresses?

Pre reqs: Access 2000, Outlook 2000
Create a table named "tblEmailaddresses" with only one column named
"email" in Access
Create a form named "frmAddEmails" with a command button "cmdAdd"
Populate "tblEmailaddresses" with say 80 email addresses eg [email protected],
[email protected] etc.

Put this code behind the command button's click event.

Option Compare Database

Private Sub cmdAdd_Click()

On Error GoTo ErrHandler

Dim txtDistName As String
txtDistName = InputBox("Enter the name of New Distribution List",
"Enter distribution List Name", "DistributionList1")

If txtDistName = "" Then Exit Sub

Dim ola As Outlook.Application
Dim dli As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Dim tblEmailTable As Object

Set ola = CreateObject("Outlook.Application")
Set dli = ola.CreateItem(olDistributionListItem)
Set myTempItem = ola.CreateItem(olMailItem)
Set myRecipients = myTempItem.Recipients
Set tblEmailTable = CurrentDb.OpenRecordset("tblEmailAddresses")
If tblEmailTable.BOF Then GoTo exit_createDL
tblEmailTable.MoveFirst

Do
myRecipients.Add tblEmailTable!email
tblEmailTable.MoveNext
Loop Until tblEmailTable.EOF
dli.AddMembers myRecipients
dli.DLName = txtDistName

dli.Close olSave
myTempItem.Close olDiscard

exit_createDL:
Set myTempItem = Nothing
Set myRecipients = Nothing
Set dli = Nothing
Set ola = Nothing
tblEmailTable.Close
Set tblEmailTable = Nothing

Exit Sub

ErrHandler:

MsgBox Err.Description, vbOKOnly, "There was an error!!"


End Sub

Please can you also advise/suggestions on coding style, error trapping
etc

many thanks

MS
 
Top