I found this code while do a search on the site. It does exactly what I am looking for. I know that it is a little old, but I think it will still work. My problem is that I am no able to define the mail recpient, everything I have tried so fair has not worked. Can someone tell me how to define s.
Code:
Sub test()
Dim myFile As String, s As String
Dim c
myFile = Range("B5").Value
ThisWorkbook.ActiveSheet.Copy
'Need to assign name via saveas
ActiveWorkbook.SaveAs "C:\Windows\temp\" & myFile
ThisWorkbook.Activate
If Not IsNull(Application.MailSession) Then Application.MailLogon
With ThisWorkbook.Worksheets("Sheet6").Cells
For Each c In .Range("D2:D12").Cells
s = c.Value
If s = "" Then GoTo Skip
Workbooks(myFile & ".xls").SendMail Recipients:=s, _
Subject:="Here is a NEW Problem Report", _
ReturnReceipt:=False
Skip:
Next c
End With
Workbooks(myFile & ".xls").Close False
'Now cleanup
Kill "C:\Windows\temp\" & myFile & ".xls"
End Sub