Using VBA to email to a list of recipients of varying length

[willow1985]

I have this sendmail code I have used in the past to email to a list of recipients that can change in length. I have made some modifications to the code however never touched the "To: cad" part of it
It no longer works and I don't know what I did wrong.

The email list will be 1 to 5 people most likely.

Any help with this section of the code would be greatly appreciated.

Sub sendmail()
  Dim OutlookApp As Object, MItem As Object, cad As String
  Dim i As Long, Sh As Worksheet, rng As Range, lr As Long
 
  Set Sh = Sheets("Warranty Email")
  lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
  Set rng = Sh.Range("A1:R" & lr)
'here
For i = 2 To lr
    cad = cad & Sh.Range("V" & i).Value & "; "
  Next
  Set OutlookApp = CreateObject("Outlook.Application")
  Set MItem = OutlookApp.CreateItem(0)
  With MItem
'and here
    .To = cad
    .Subject = Sh.Range("W1").Value
    .htmlBody = Sh.Range("W2").Value & RangetoHTML(rng) & _
      "<br>Thank you<br>"
.Display
    .Send
  End With
End Sub

Customer Concern - Warranty Request Log.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Customer Concern Number	Date	Latest W/O #	Previous W/O #(s)	PO#	Model Type	Customer Name	P/N	S/N	Description	Discrepancy	Total number of Parts	Area Issued to	Verification completed Y/N	Warranty Granted Y/N	Warranty Cost	Work done in Good Faith Y/N	Additional Notes			Inspector List:	carla.fisher@email.com	Pending Warranty Inspections/Required Updates
2	CC3306	25-Oct-23	TBD	N/A	1234	N/A	Customer	201604	N/A	Parts	damage	2	Area	N	TBD								The below listed Warranties require inspection and/or updates. Please complete the items in yellow and email back to the QA Dept.
Warranty Email

 

[Eric W]

Just by looking at your code, I'd suggest moving the "Next" line after the "End With" line.

 

[willow1985]

Just by looking at your code, I'd suggest moving the "Next" line after the "End With" line.

Just tried it but still got the same error.

 

[Eric W]

A closer look at your code indicates that the Next was in the right spot. Sorry!

What error are you getting? I don't have Outlook installed on my PC, so I can't actually test your code. Maybe someone else can jump in.

 

[Domenic]

You're concatenating a list of email recipients from Column V, starting at Row 2. But your sample worksheet shows that there's no value in V2. And maybe you don't have any other emails listed in the rest of the column. So, as the error message says, you need at least one name.

 

[willow1985]

You're concatenating a list of email recipients from Column V, starting at Row 2. But your sample worksheet shows that there's no value in V2. And maybe you don't have any other emails listed in the rest of the column. So, as the error message says, you need at least one name.

That's what I was missing! Thank you so much!

For i = 2 should be For i = 1

Thank you very much for the second set of eyes, or three!

Thanks to both of you