How to send an email to multiple recipients

[Phil1980]

Hello,

First, here is the situation. I am part of a group of non profit organization, who's members change quite often because presidents, for example, have 2 years term. At any given time, a member can send an email to the rest of the group, but presidents are no longer there and it becomes a hassle to manage and keep up to dates the email adresses of every one. Therefore, I thought we could write the email adresses in excel, share the document via Dropbox so that people, when there is a new president, for example, can update the adresses themselves in the document. So far, that is easy enough. I thought it would be a good idea though to go a step further and have a mailto function where people can click and it would open their default email client with all the email adresses contained in column C, for example.

I have used the following function =HYPERLINK("mailto:"&C2&", "&C3&", "&C4&""), which works for about 15 email adresses, but no more, which is not helpfull as we may have close to 100 email addresses. I do not mind using VB, but I am a rookie and need detailed steps in order to do it.

If somebody can help, it would be greatly appreciated.

 

[ranman256]

enter vbe, (alt-f11)
add new module: insert module
paste in the code below,
then put all emails in a sheet, all in col A
change the code below to YOUR sheet name
Save
then collect all the emails from the list via:
dim sTo as string
sTO = CollectEmailList()
'send email here

Public Function CollectEmailList()
Dim vTo
Sheets("Emails").Activate
Range("A2").Select
While ActiveCell.Value <> ""
    vTo = vTo & ActiveCell.Value & ";"
     ActiveCell.Offset(1, 0).Select 'next row
Wend
CollectEmailList = vTo
End Function

 

[Phil1980]

Hello,

Thank you for your reply.

I have followed your suggestions, but I do not know what you mean by:

then collect all the emails from the list via:
dim sTo as string
sTO = CollectEmailList()
'send email here

If a list should have been created, it has not. Can you elaborate please.

Thanks again!