We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Create Mailing List from Excel Worksheet

seamus99
seamus99 asked
on
Medium Priority
328 Views
Last Modified: 2012-05-11
I have a excel Worksheet, that contains a column with email addresses.  I would like to create a repeatable process (as the worksheet will contain to have new rows added with email) where the email addresses can be automatically exported to a list that can be used in Outlook or any other email client.

Comment
Watch Question

i tried different ways, but of no use!!! most probably u cant..

but anyways, if you want, you can just copy and paste all of them in the address box!! you would know this.. ;-)

-Bala
Most Valuable Expert 2012
Top Expert 2012

Commented:
Is this what you're looking for?

This is for Outlook distribution lists, adapted from:  http://www.excelbanter.com/showthread.php?t=160915

 
'Adapted from source:  http://www.excelbanter.com/showthread.php?t=160915

Sub AddNewMembers(strDLName As String)
Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myDistList As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Dim myInputAddr As Range

    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set myDistList = myOlApp.CreateItem(olDistributionListItem)
    Set myTempItem = myOlApp.CreateItem(olMailItem)
    Set myRecipients = myTempItem.Recipients
    
    'myDistList.DLName = InputBox("Enter the name of the new distribution list")
    myDistList.DLName = strDLName
    
    For Each myInputAddr In Range("A2", Range("A" & Rows.Count).End(xlUp))
        myRecipients.Add myInputAddr.Value
        myRecipients.ResolveAll
    Next myInputAddr
    
    myDistList.AddMembers myRecipients
    
    myDistList.Save
    myDistList.Display
    
End Sub

Open in new window


You need to go to 'Tools > References' and set a reference to the Outlook library for the version of Office you have.

See attached and advise.

Enjoy!

Dave

CreateDistList-r1.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's an additional solution that you can run "on demand" - and sends mail message based on distribution list in spreadsheet.  It "appears" to have capability to work with any email system, but doesn't store the distribution list, rather it uses that list for an immediate SendMail command.

http://windowssecrets.com/forums/showthread.php/95630-Create-mailing-list-address-array-(Excel-2k-VBA)

Please let me know if this helps!

Dave
two things:
while calling you should provide the DL name, so set a cell for DL's name and provide the cell's value in the argument.

I doubt if a mail id is already available in the DL, it might give some error. and according to this requirement, in the excel sheet, the new mail ids will be added at the end. so, starting to add from A2 everytime might cause an error. hence, clear the existing DL and then start adding from the first.

hope this helps!!
Most Valuable Expert 2012
Top Expert 2012

Commented:
With the same spreadsheet, you could use this routine (adapted from above link):

note:  SendMail has these parameters (see Excel Help):  expression.SendMail(Recipients, Subject, ReturnReceipt)


 
'Adapted from source:  http://windowssecrets.com/forums/showthread.php/95630-Create-mailing-list-address-array-(Excel-2k-VBA)

Sub sendMailWDistrList()
Dim myInputAddr As Range
Dim distArray As Variant

    Set distlist = Range("A2", Range("A" & Rows.Count).End(xlUp))
    
    For Each Address In distlist
        distArray = distArray & Chr(34) & "," & Chr(34) & Address
    Next Address
    
    distArray = Right(distArray, Len(distArray) - 3)
    ActiveWorkbook.SendMail Recipients = distArray
    
End Sub

Open in new window


The attached worksheet has an additional button for this feature, which you'll need to then enhance to your needs, or advise for assistance...

Note: the example email addresses are bogus, and you'd need to enhance the SendMail to include Subject, etc., as appropriate

Dave
CreateDistList-r2.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
@bala - good point.  One should delete existing distribution list, before adding it back again...

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here's another good helper on Outlook Mailing Lists: http://www.codeforexcelandoutlook.com/blog/2010/06/automatically-update-outlook-distribution-lists-from-excel/

Using some hints from that, I modified my spreadsheet to delete the contact list, before re-creating it as with this example.

Enjoy!

Dave
CreateDistList-r3.xlsm

Author

Commented:
Here is the spreadsheet I want to use, with your suggested VBA Code, I can not seem to get it to run correctly.

CreateDistList-r3.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
>>I have a excel Worksheet, that contains a column with email addresses

Ok - well, this doesn't quite look like a worksheet with a column of email addresses...

What, exactly, would you like to do?

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok - I think I see what you want to do.  I assume you don't care about items in the list that don't have a "valid" email address in column J.  Is that correct?

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok - using the button - Add Distribution List, you'll be prompted for a distribution list name.  The app will go through column J and build a distribution list based on the email addressed contained therein.  If there is nothing, nothing will be created for that record.

At the end, you'll be shown the distribution list.  You can hit "Save/Close" and then can proceed to use that distribution list with Outlook, until you've modified it again (e.g., by adding new records and hitting "Add Distribution List" again.

---------------------------------------
For the second button, "Send Mail via Any email System", the approach is the same, except it fires the SendMail routine.  I've modified the code so you can use this, modify the subject line, and send to that distribution list.

Here's a very good link with send mail tips, if you want to go down that route:  http://www.rondebruin.nl/sendmail.htm
---------------------------------------

Please let me know how the attached is working for you, now.

If you want to use the second approach (e.g., Outlook independent) then I believe you need to be prepared to actually send a message - the routine creates the array of emails to email, then use the SendMail command to execute it.  I can work with you on this, if you can specify a bit more what you're trying to accomplish...

Dave
CreateDistList-r3a.xlsm
Azeem PatelSystem Administartor

Commented:
I think you want to send email to users mentioned in Excel Column

http://mcobit.business.nd.edu/kb/kb.cfm?Action=NEWQuestion&gid=1316

Author

Commented:
Dave
'It creates the distribution list perfectly, however when I click the send email button I eventually get a Erorr 1004  with the following ;

"unknown receiptant name in receipant list'

Commented:
Hi Seamus,

You can just copy the column with the email addresses and paste it in the TO field.

Cheers,
Roberto.
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.