VBA to email pay slip

[tayomi]

I maintain payroll on Excel. The workbook contains different sheets one of which is named PAYSLIP.
The payslip was populated through data validation list and combination of IF and VLookup formula. Such that if a staff name is selected from the cell that contains data validation list all the fields will be filled automatically. Payslip had been set as Print_Area

VBA code was written to attach Print_Area as PDF, loop through the data validation list and email everyone on the list a custom payslip.
Below was the VBA applied.

Sub EmailPayslipToDataValidationList()
On Error GoTo ErrorHandler

Dim ws As Worksheet
Dim rngDataValidation As Range
Dim cell As Range
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim payslip As String

' Set the worksheet and range containing the data validation list
Set ws = ThisWorkbook.Worksheets("PAYSLIP") ' Replace "Sheet1" with your actual worksheet name
Set rngDataValidation = ws.Range("J5:J54") ' Replace A1:A10 with the range containing your data validation list

' Create Outlook objects
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through each cell in the data validation list
For Each cell In rngDataValidation
' Skip empty cells and header row
If Not IsEmpty(cell) And cell.Row <> 1 Then
' Set the print area based on the cell's row
ws.PageSetup.PrintArea = ws.Range("B2:F16" & cell.Row).CurrentRegion.Address

' Create a temporary PDF file name
payslip = Environ("TEMP") & "\" & "Payslip.pdf"

' Export the print area as PDF
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=payslip, Quality:=xlQualityStandard

' Create a new email
Set OutlookMail = OutlookApp.CreateItem(0)

' Set email properties
With OutlookMail
.Subject = "Payslip"
.Body = "Please find attached your payslip."
.To = cell.Value ' Assumes the email addresses are in the same column as the data validation list
.Attachments.Add payslip
.Send
End With

' Delete the temporary PDF file
Kill payslip

' Delay between emails (2 seconds)
Application.Wait Now + TimeValue("0:00:02")

' Clear the email object for the next iteration
Set OutlookMail = Nothing
End If
Next cell

' Release Outlook object
Set OutlookApp = Nothing

MsgBox "Emails sent successfully!"

Exit Sub

ErrorHandler:
MsgBox "An error occurred while sending emails. Please check your Outlook configuration and try again."
End Sub


Problem
I use outlook to send mail. Everybody received the same payslip instead of payslip that is personal to individuals. That is, the payslip of the person selected at the point of running the macro.
I want individuals to receive what belong to them.

Kindly help me out.

 

[tayomi]

A

A big thanks to my instructor here. The code run and delivered successfully. Thanks, Thanks and Thanks!!!!

In continuation, only recipients with outlook accounts are receiving mail. I want others with Gmail and Yahoo account to receive too.
Which way to go or what do you advise?

Thanks!

 

[davesexcel]

I don't know why people with other emails can't get your email sent from outlook.

 

[tayomi]

Tha

I don't know why people with other emails can't get your email sent from outlook.

Thanks for the response, I am satisfied with the extent of solutions we've been able to reach so far. Thank you.