visual basic + excel - sending emails to mulitple address stored in cells

hey

once again more problems sending emails using visual basic (though a different program this time)

the guitarist in my band wants an easy to use mailing list. now there are plently of eaier options for doing this.. but i decided to try writing one using VB and Excel, just to see if i could really...

... i cant! or at least, im having trouble, hense the reason im here!

basically email addresses are stored in the individual cells of a column (range("a1") downwards)

at the moment, im using this code, kindly suggest by Bruintje in one of my previous questions to send the emails:

Dim iOutlook As Object
Dim myApp As Object
Set iOutlook = CreateObject("Outlook.Application")
Set myApp = iOutlook.CreateItem(0)
myApp.To = (blank)
myApp.Subject = "My Subject"
'myApp.Attachments.Add "C:\file.txt" '<-uncomment to attach a file
myApp.Body = "ello"
'myApp.send
Set myApp = Nothing
Set iOutlook = Nothing
End Sub

however, i want to know if it is possible to have the email sent to all the addresses stored in the column. i can do the address in the first cell of the column:

yApp.To = range("a1").value

but i havnt got a clue how to go about getting other email address into the "yApp.To" section.

is it possible? can the experts understand my crude explanation of my problem? will toast ever fall butter side up? only time will tell :)

thanks,

dave xXxx
paddockAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fantasy1001Commented:
if you know exactly how many email in the column then

yApp.To = range("a1").value & ";" & range("a2").value & ";" & range("a3") & ... continue here
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Dim x as string
int i as integer

i = 0
while Cells(i, "A").Value <> Empty
 x = x + Cells(i,"A").Value +"; "
Wend

myApp.To = x

note: It runs until there's a blank, but I would figure you had the list sorted.  I would also recommend doing the myApp.BCC to eliminate publishing everyone's e-mail addresses to each other.  Good luck!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
paddockAuthor Commented:
thankee most kindly sir!

doesnt quite work, but i think thats my own inadequacies showing through lol

il keep working on it, it does look like exactly what i need!

as for your suggestion fantasy1001, although that would have worked, there are two problems:

1) the number of people on the list are always changing (people subscribe, unsubscribe) and seeing how the recipicant of the program knows less VB than myself, he wouldnt know how to adjust the code accordingly

2) there is absolutly no way that im going to sit there and type out all the cell refrences! i really am that lazy:)

but thank you for your advice

dave xXxx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.