"Sendmail" macro to multilpe addresses

S

Stephen C

I have found some code on Ron de Bruin's web site which enables me to send
the active sheet as an attachment to an email address contained in cell A1.

This works fine but I now require it to be sent to more than one person, if
add a second email address to cell A1 the macro will not work.

Could someone please advise me how to change the following code to enable me
to send to multiple recipients contained in one cell.

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail (Range("a1")), _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

I am using excel 2000 & Outlook Express.
 
J

Joel

What does the email address look like in A1. Did you seperate the E-Mail
addresses with a semicolon and a space? the email address should look like
the e-mail address in Outlook.
 
S

Stephen C

Cell A1 contains "(e-mail address removed)", this works fine.

I have tried adding the second one with a semicolon and space like this
"(e-mail address removed); (e-mail address removed)" but it will not work.

I am using Outlook Express NOT Outlook.
 
S

Stefi

Separate addresses in A1 by, say commas,
insert this line

addressees = Split(Range("A1"), ",")

before With wb line

and use

..SendMail addressees, "This is the Subject line"

Regards,
Stefi


„Stephen C†ezt írta:
 
J

Joel

You need to include Recipents and subject in the line. Excel was probably
putting your 2nd email address into the subject.

.SendMail Recipients:=(Range("a1")), _
Subject:="This is the Subject line"
 
S

Stephen C

Thank you Stefi.

Stefi said:
Separate addresses in A1 by, say commas,
insert this line

addressees = Split(Range("A1"), ",")

before With wb line

and use

.SendMail addressees, "This is the Subject line"

Regards,
Stefi


„Stephen C†ezt írta:
 
S

Stefi

You are welcome! Thanks for the feedback!
Stefi

„Stephen C†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top