Sending an email in excel

[csweet]

Overview of what i am trying to achieve - I have a list of data that could be 1000 rows long and i want to send an email to everyone manager on the list as a reminder to do something but if a manager appears on the list more than once this means they have multiple employees with the same issue so they need to be told in the same email not seperate.

Here are my column headers

EMP_NO

SURNAME

FORENAMES

ORG_UNIT

FIXED_TERM_END_DATE

REPORTS_TO_EMP_NO

MANAGER_SURNAME

MANAGER_FORENAME

MANAGER_EMAIL

<tbody>
</tbody>

here is the query i have mangled together looking at other peoples advice on this site as it stands it worked up to the point where i tried to add the IF statement to get more than one employee on the list. the amendments i made to stop it working are hilighted

Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim xMailBody1 As String
Dim xMailBody2 As String
Dim txt As String
If Range("F2") <> Range("F3") Then txt = xMailBody
ElseIf Range("F2") <> Range("F4") Then txt = xMailBody1
ElseIf Range("F2") <> Range("F5") Then txt = xMailBody2
End If


On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)


xMailBody = "Dear " & Range("H2") & "," & vbNewLine & vbNewLine & _
"We have noticed that you have the below temporary employee's active in your store with a fixed term end date in the past." & vbNewLine & vbNewLine & _
Range("C2") & " " & Range("C2") & " - " & Range("A2") & " - Fixed Term End Date - " & Range("E2") & vbNewLine & vbNewLine & _
"This currently means that the above employee's will not have a correct holiday balance and we must ensure they take their statutory minimum this year, please can you take one of the below actions." & vbNewLine & vbNewLine & _
"· Extend the employee's contract" & vbNewLine & vbNewLine & _
"· Make the employee's permanent" & vbNewLine & vbNewLine & _
"· Make the employee a leaver if they are not being kept on" & vbNewLine & vbNewLine & _
"Please see the attached guide on how to extend a fix term end date or make an employee Permanent in deb-hr. If you have any queries please contact HR." & vbNewLine & vbNewLine & _
"Kind Regards,"



On Error Resume Next


xMailBody1 = "Dear " & Range("H2") & "," & vbNewLine & vbNewLine & _
"We have noticed that you have the below temporary employee's active in your store with a fixed term end date in the past." & vbNewLine & vbNewLine & _
Range("C2") & " " & Range("C2") & " - " & Range("A2") & " - Fixed Term End Date - " & Range("E2") & vbNewLine & vbNewLine & _
Range("C3") & " " & Range("C3") & " - " & Range("A3") & " - Fixed Term End Date - " & Range("E3") & vbNewLine & vbNewLine & _
"This currently means that the above employee's will not have a correct holiday balance and we must ensure they take their statutory minimum this year, please can you take one of the below actions." & vbNewLine & vbNewLine & _
"· Extend the employee's contract" & vbNewLine & vbNewLine & _
"· Make the employee's permanent" & vbNewLine & vbNewLine & _
"· Make the employee a leaver if they are not being kept on" & vbNewLine & vbNewLine & _
"Please see the attached guide on how to extend a fix term end date or make an employee Permanent in deb-hr. If you have any queries please contact HR." & vbNewLine & vbNewLine & _
"Kind Regards,"


On Error Resume Next

xMailBody2 = "Dear " & Range("H2") & "," & vbNewLine & vbNewLine & _
"We have noticed that you have the below temporary employee's active in your store with a fixed term end date in the past." & vbNewLine & vbNewLine & _
Range("C2") & " " & Range("C2") & " - " & Range("A2") & " - Fixed Term End Date - " & Range("E2") & vbNewLine & vbNewLine & _
Range("C3") & " " & Range("C3") & " - " & Range("A3") & " - Fixed Term End Date - " & Range("E3") & vbNewLine & vbNewLine & _
Range("C4") & " " & Range("C4") & " - " & Range("A4") & " - Fixed Term End Date - " & Range("E4") & vbNewLine & vbNewLine & _
"This currently means that the above employee's will not have a correct holiday balance and we must ensure they take their statutory minimum this year, please can you take one of the below actions." & vbNewLine & vbNewLine & _
"· Extend the employee's contract" & vbNewLine & vbNewLine & _
"· Make the employee's permanent" & vbNewLine & vbNewLine & _
"· Make the employee a leaver if they are not being kept on" & vbNewLine & vbNewLine & _
"Please see the attached guide on how to extend a fix term end date or make an employee Permanent in deb-hr. If you have any queries please contact." & vbNewLine & vbNewLine & _
"Kind Regards,"




With xOutMail
.To = Range("I2")
.CC = ""
.BCC = ""
.Subject = "Fixed term end dates in the past"
.body = txt


.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

What i would like to achieve is to send the email to every line manager automatically including all the information for all of their employees (the data will be sorted by reports_to_emp_no)

any questions let me know