Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create Mailing List from Excel Worksheet

Posted on 2011-04-23
15
Medium Priority
?
267 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.

0
Comment
Question by:seamus99
  • 9
  • 2
  • 2
  • +2
15 Comments
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35452762
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453810
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453833
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
0
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!

 
LVL 2

Expert Comment

by:balatheexpert
ID: 35453843
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!!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453880
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35453931
@bala - good point.  One should delete existing distribution list, before adding it back again...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35454102
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
0
 

Author Comment

by:seamus99
ID: 35454936
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35454944
>>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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35454959
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35454994
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
0
 
LVL 4

Expert Comment

by:Azeem Patel
ID: 35455720
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
0
 

Author Comment

by:seamus99
ID: 35456017
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'
0
 
LVL 5

Expert Comment

by:Pabilio
ID: 35456238
Hi Seamus,

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

Cheers,
Roberto.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35456368
Seamus - I believe that's because one of your email addresses is invalid.  Please check your email addresses

Dave
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 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