Using Loop To Create Multiple Recipients of Email (Excel) 1

[OzzieTheOwl]

Hi

I am trying to send an email to multiple recipients, the number of recipients could vary each time the macro is run.
I have created a loop to get the names, but Outlook doesn't seem to like the multiple names, can anyone help me out?

Current Code Below

        For EmailLoop = 3 To EmailEnd
            If EmailLoop = 3 Then EmailName = Sheets("Contacts").Cells(EmailLoop, ProjectLoop + 1) & "@name.co.uk"
            If EmailLoop > 3 Then EmailName = EmailName & ";" & Sheets("Contacts").Cells(EmailLoop, ProjectLoop + 1) & "@name.co.uk"
        Next EmailLoop
    'Copy To New Book
    Sheets(ProjectName).Copy
    ActiveWorkbook.SendMail Recipients:=EmailName, Subject:="PAT Testing Report"

Cheers

Barney

 

[PHV]

You have to use an array, I guess:
Dim arrName()
ReDim arrName(EmailEnd - 2)
For EmailLoop = 3 To EmailEnd
arrName(EmailLoop - 3) = Sheets("Contacts").Cells(EmailLoop, ProjectLoop + 1) & "@name.co.uk"
Next
Sheets(ProjectName).Copy
ActiveWorkbook.SendMail Recipients:=arrName, Subject:="PAT Testing Report"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OzzieTheOwl]

PHV

Thank you very much, worked perfectly.

Have a star on me.

Cheers

Barney