[smitty343]

I am looking for a way to make a email distribution list in excel. The information in the sheets is a follows. Name, Group, and up to 10 email addresses for each person. what I need to do is have 1 button to click on and have an email composed with all the email addresses on that sheet put into an email. I have found a post on the forums that does almost this but I don't know how to change it to what I need. I have attached it so you can look at it.

[Zack Barresse]

Hi there, and welcome to the board!

Wow, this looks sooo familiar. Can you post a link to the thread which looked like it might help? What do you want to fire this routine? Just a simple button? How will you know which row of data to look at? Shall there be a list to choose from when you click this button? And if so, will it be by employee name (col G)? In your post, you say up to 10 email addresses, but your worksheet only shows three columns of email addresses. Where is the difference here? Is this your actual data, or a representation? The closer we get to your actual data the better off you are, and if you can upload your actual file, that's the best case scenario for us to help you.

Edit: Also, what OS are you running? Version of Excel? Email client and version? And this is only for your machine, yes?

[smitty343]

http://forums.techguy.org/business-a...tion-list.html

I need just a button to fire this routine.
That work sheet is yours for the post above. I just attached that to show you that I need it to work something like that one.

here's what I'm looking to do.
col A will be the persons Name
col B-col k will be the email addresses
I need a button to click on that will take all the email addresses from Col B thru col K and put them into an email.
I don't need the persons name in the email just there addresses.
so if there were 20 people on the sheet with say 5 email addresses each I need 1 button that will take all 100 email addresses on that sheet and put it into an email.
OS system is windows XP. Excel 2003, Outlook 2003 and only for my machine

[Zack Barresse]

I knew that spreadsheet looked familiar!!! LOL!

Do you have this spreadsheet made yet? So there is no limit to the number of emails? Not a limit of 10? How will 20 people with 5 email addresses for each be 100 email addresses in one email? By what you said before that would seem to be 20 emails with 5 email addresses sending to... I'm lost.

[smitty343]

I'm sure that I'm not explaining it properly but here is a basic spreadsheet that I will use. If you look at it there are 20 people listed. Each has some email addresses filled in. I need the button to take all of the email addresses that are filled in from col B thru col K and put it into an email. so if the 20 people had 5 email addresses each that would be a total of 100 email addresses filled into the outgoing email. 5x20=100 addresses

[smitty343]

I think I figured out what I needed to do to the one I sent you.

[Zack Barresse]

100 addresses? Or 100 emails? From your file it looks like 20 people, 20 emails, 100 recipients.

[smitty343]

I have this working but need some help still. Attached is the file I'm working with. Sheet 1 works just fine but if I change the name of the sheet it stops working. Also I need all the other sheets to work just like sheet 1 but they don't. also I need to be able to change the sheet names and have it still work.

[Zack Barresse]

You see this line...

    With ThisWorkbook.Sheets("Sheet1")

It would need to be changed to this...

    With ThisWorkbook.ActiveSheet

Also, you can shorten up your code a bit by taking out this entire loop section...

        For Each rCell In .Range("I3", .Cells(.Rows.Count, "I").End(xlUp)).SpecialCells(xlCellTypeVisible)
            If rCell.Value <> "" Then sAddys = sAddys & rCell.Value & "; "
            If rCell.Offset(0, 1).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 1).Value & "; "
            If rCell.Offset(0, 2).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 2).Value & "; "
            If rCell.Offset(0, 3).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 3).Value & "; "
            If rCell.Offset(0, 4).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 4).Value & "; "
            If rCell.Offset(0, 5).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 5).Value & "; "
            If rCell.Offset(0, 6).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 6).Value & "; "
            If rCell.Offset(0, 7).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 7).Value & "; "
            If rCell.Offset(0, 8).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 8).Value & "; "
            If rCell.Offset(0, 9).Value <> "" Then sAddys = sAddys & rCell.Offset(0, 9).Value & "; "
        Next rCell

...and replace it with these two lines...

        Set rCell = .Range("I3", IIf(.Range("R3").Value <> "", .Range("R3"), .Range("R3").End(xlToLeft)))
        sAddys = VBA.Join(Application.Transpose(Application.Transpose(rCell)), "; ")

Should look a little more managable at that point I think.

HTH

[Zack Barresse]

Oh, also, at the end of your code where you look at quitting Outlook if you opened it (for cleaning up), make sure you change it from "quite" to "quit".