Send mail to multiple recipients

[Marnie]

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
code:

Sub CCLISTNew()
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")
OutApp.Session.Logon
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
Notification"

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.

Maury

 

[Marnie]

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
method.

 

[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
Next

Subj = "File has been updated by " &
Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc="
& Recipientbcc & "&"

 

[Marnie]

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