Send mail to multiple recipients



I need to send an email to a group of people whose names are in several
fields in a spreadsheet. I look up the fields based on a value in a
different worksheet. It is building the list properly but the email is not
working. There is no error, but I do not receive the email. Here is my

Dim OutApp As Object
Dim OutMail As Object
Dim CCList As Variant
Dim tmplist As Variant
Dim MailList As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Set LkRange = Worksheets("Contact List").Range("A2:F25")
If Sheets("Hold Reasons").Range("C28") = "X" Then
tmplist = Application.WorksheetFunction.VLookup(Range("D6"),
LkRange, 2, False)
MailList = MailList + tmplist + ","
End If

If Sheets("Hold Reasons").Range("D28") = "X" Then
tmplist = Application.WorksheetFunction.VLookup(Range("D6"),
LkRange, 3, False)
MailList = MailList + tmplist + ","
End If

If Sheets("Hold Reasons").Range("E28") = "X" Then
tmplist = Application.WorksheetFunction.VLookup(Range("D6"),
LkRange, 4, False)
MailList = MailList + tmplist + ","
End If

If Sheets("Hold Reasons").Range("F28") = "X" Then
tmplist = Application.WorksheetFunction.VLookup(Range("D6"),
LkRange, 5, False)
MailList = MailList + tmplist + ","
End If

On Error Resume Next

CCList = Split(MailList, ",")
ActiveWorkbook.SendMail Recipients:=Array(CCList), Subject:="QC Hold

On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Erase CCList

End Sub

Maury Markowitz

I'm not 100% sure, but I think you want to remove the Array(). I have
examples with an without the Array() in my own code.

Another possibility is that one or more of the names is not resolving
directly, but would normally resolve through a delayed process. If you
use the mailenvelope method you can hand-resolve them, but I'm not
sure you can cause this in SendMail.



Thank you for responding.
I tried it first without the Array and did some investigating, which said
that you need Array if sending to multiple recipients. I got it to work with
only one name but it failed when I added a second name in my test data. I
was able to send to each of my test names individually so I don't think it is
a problem with name resolution. I am not familiar with the mailenvelope

Gary Keramidas

i have used something like this, maybe you can adapt it:

arr = Worksheets("emp").Range("M2:M" & lastrow).Value

For i = 2 to lastrow
Recipient = arr(i, 1) & ";" & Recipient

Subj = "File has been updated by " &
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc="
& Recipientbcc & "&"


Thanks but I neglected to mention that I need to include the Excel
spreadsheet as an attachment on the email.

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