Create PDF and Mail Excel file with excel VBA (Fully Automatic)

 VBA Code for Create PDF and Mail Excel File

Download file for VBA Code - Click here

'************* Code for Single Pay Slip **********

Sub sendReminder()

ChDir "C:\Users\Golu\Desktop"

Sheets("Pay").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Golu\Desktop\PrintPdf\" & Range("d7").Value & " - " & Range("d9").Value & ".pdf"

Dim OutLookApp As Object

Dim OutLookMailItem As Object

Dim myAttachments As Object

Set OutLookApp = CreateObject("OutLook.application")

Set OutLookMailItem = OutLookApp.CreateItem(0)

Set myAttachements = OutLookMailItem.Attachments

With OutLookMailItem

.to = Sheets("Pay").Range("d12")

.Subject = "Pay Slip"

.Body = "Please find enclosed file of Pay slip in Attachment"

myAttachements.Add "C:\Users\Golu\Desktop\PrintPdf\" & Range("d7").Value & " - " & Range("d9").Value & ".pdf"

.Send

End With

'Set OutlookMailitem = Nothing

'Set OutLookApp = Nothing

MsgBox "PDF file generateed successfully"

End Sub

'************* Code for Bulk Pay Slip **********

Sub sendReminderBulk()

Dim OutLookApp As Object

Dim OutLookMailItem As Object

Dim myAttachments As Object

For n = 3 To 43

Sheets("Pay").Range("d7").Value = Sheets("Jan").Cells(n, "B")

Sheets("Pay").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Golu\Desktop\PrintPdf\" & Range("d7").Value & " - " & Range("d9").Value & ".pdf"

Set OutLookApp = CreateObject("OutLook.application")

Set OutLookMailItem = OutLookApp.CreateItem(0)

Set myAttachements = OutLookMailItem.Attachments

With OutLookMailItem

.to = Sheets("Pay").Range("d12")

.Subject = "Pay Slip"

.Body = "Please find enclosed file of Pay slip in Attachment"

myAttachements.Add "C:\Users\Golu\Desktop\PrintPdf\" & Range("d7").Value & " - " & Range("d9").Value & ".pdf"

.Send

End With

Next

'Set OutlookMailitem = Nothing

'Set OutLookApp = Nothing

MsgBox "PDF file generateed successfully"

End Sub

Download file for VBA Code - Click here