I currently have an excel sheet with a button, when pressed it saves and
asked to select email addresses from another worksheet, when the user selects
the email addresses it will then email the relevant people to say this PIP is
ready for review.

Ideally, i would like to reference whatever is in E10 to lookup a
distribution group.

For example if the user types CURWEN into cell E10 it will look up Curwen
from a table within Excel and email whoever exists in the range of curwen.
This would be so much easier. Please help.

Thanks.

Column A is for Email Addresses
Column B is for Subjects
Column C is for the body text of the email

This Code will send as many different emails to as many people as you like
by looping through the used range HTH.

Option Explicit

Public Sub MailTableItems()
Dim Outlook As Object
Dim Mail As Object
Dim Body As String
Dim i As Integer
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets(1)
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

If LastRow = 1 Then MsgBox "No recipients were found", vbInformation

For i = 2 To LastRow
Set Outlook = CreateObject("Outlook.Application")
Outlook.Session.Logon
Set Mail = Outlook.CreateItem(0)

Body = ws.Range("C" & i).Value

On Error GoTo Morgue

With Mail
.To = ws.Range("A" & i).Value
.Subject = ws.Range("B" & i).Value
.Body = Body
.send
End With
Next

ErrorOut:
Set Outlook = Nothing
Set Mail = Nothing
Exit Sub

Morgue:
MsgBox Err.Description, vbCritical
Resume ErrorOut
End Sub



"Neil Holden" wrote:

I currently have an excel sheet with a button, when pressed it saves and
asked to select email addresses from another worksheet, when the user selects
the email addresses it will then email the relevant people to say this PIP is
ready for review.

Ideally, i would like to reference whatever is in E10 to lookup a
distribution group.

For example if the user types CURWEN into cell E10 it will look up Curwen
from a table within Excel and email whoever exists in the range of curwen.
This would be so much easier. Please help.

Thanks.