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
Post a Comment