Sending an email in excel

csweet

New Member
Joined
Aug 21, 2018
Messages
2
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

[TABLE="class: x_MsoTableGrid"]
<tbody>[TR]
[TD]EMP_NO[/TD]
[TD]SURNAME[/TD]
[TD="width: 84"]FORENAMES[/TD]
[TD="width: 92"]ORG_UNIT[/TD]
[TD="width: 159"]FIXED_TERM_END_DATE[/TD]
[TD="width: 151"]REPORTS_TO_EMP_NO[/TD]
[TD="width: 143"]MANAGER_SURNAME[/TD]
[TD="width: 151"]MANAGER_FORENAME[/TD]
[TD="width: 257"]MANAGER_EMAIL[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top