Sending email to list of recipient using vba in excel

[vinayt]

Hello All,

I use following code to send an email from an excel file

Sub Button1_Click()
 
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim Recipient As String
Dim Subject1 As String
Dim ccRecipient As String

Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
Else: Maildb.OpenMail
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Recipient = worksheets("note").range("A3").value
MailDoc.SendTo = Recipient
'MailDoc.BlindCopyTo = Whomever
Subject1 = "New scorecards"
MailDoc.Subject = Subject1
MailDoc.Body = worksheets("note").range("A8").value & vbNewLine & "THESE ARE THE NEW SCORES"

MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo Audi
Call MailDoc.Send(False)
Set Maildb = Nothing:
Set MailDoc = Nothing:
Set Session = Nothing
Exit Sub
Audi:
Set Maildb = Nothing:
Set MailDoc = Nothing:
Set Session = Nothing
End Sub

Now,
I have another sheet which have records of Name, Company and emailID in tabular form
I want a macro which will send an email to everybody in the tabular list with different message.

I thought of using lookup and for loop but it didn't work.

any help would be highly appreciated.

Vinayt