Create salary slip in pdf and email it to email id

[khelgadi]

Dear Excel Experts,
Hello!

In this thread I have attached employee details file which contains Employee Name, attendance, salary amount and email id. There's a separate sheet of Salary Slip which calculates salary as per the selected Month.
I need your help in creating VB macro which would prepare a separate pdf file of each employee ( e .g. EmpNameJan19.pdf) from the salary slip sheet , store it to a folder and email it the employee as per records.

I hope you will help to guide me through this process.
Waiting for the responses.

Thanks
Pushkar

 

[Kenneth Hobson]

I would suggest that you make your template sheet more like this one. Note the use of the formulas.

Once done, I can show you how to make the pdfs as this file does and then attach those to the emails.

 

[khelgadi]

Hi Kenneth!
Thanks for the reply...
I am able to do that part. The current slip which I have shown is just as a sample.
Need help in pdf and mail
Thanks again for reply.

 

[D Coker]

Try this for the PDF

Sub PDF_Email()
'

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Dim sFile As String
Dim fName As String
Dim tdate As String
Dim NewName As String



fName = "(name your file)"

End If
tdate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm-dd-yyyy")
NewName = fName & " " & tdate


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
'strName = Replace(wsA.Name, " ", "")
'strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = NewName & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
  (InitialFileName:=strPathFile, _
  FileFilter:="PDF Files (*.pdf), *.pdf", _
  Title:="Select Folder to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
  wbA.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=myFile, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=True
  'confirmation message with file info
  MsgBox "PDF file has been created: " _
  & vbCrLf _
  & myFile
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not create PDF file"
  Resume exitHandler
End Sub

I think this will get you started. I will follow up with email if you use Outlook.

 

[D Coker]

Try this for the email. You may be able to attach the PDF from the file path assigned. Not sure. I use this for emailing a worksheet, not PDF.

Sub SendEmail()
'Working in Excel 2000-2016

  Dim Sourcewb As Workbook
  Dim Destwb As Workbook

  Dim OutApp As Object
  Dim OutMail As Object
  Dim substart As String
  Dim ToEmails, CCEmails, Order, sig, body As String
 
  
  
  With Application
  .ScreenUpdating = False
  .EnableEvents = False
  End With

  Dim etime
  Dim greet As String
  etime = hour(Now())
  If etime > 12 And etime < 16 Then
  greet = "Good Afternoon"
  ElseIf etime > 16 And etime < 23 Then
  greet = "Good Evening"
  Else: greet = "Good Morning"
  End If

  Set Sourcewb = ActiveWorkbook
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  With Destwb
  
  On Error Resume Next
  With OutMail
  .display
  .To = "Whomever you want"
  .CC = "whoever you want"
  .BCC = ""
  .Subject = "Whatever you want"
  .HTMLBody = body & .HTMLBody
  
  '.Attachments.Add Destwb.FullName
  'You can add other files also like this
  '.Attachments.Add ("C:\test.txt")
  '.Display  'or use .Send
  End With
  On Error GoTo 0
'  .Close savechanges:=False
  End With


  Set OutMail = Nothing
  Set OutApp = Nothing

  With Application
  .ScreenUpdating = True
  .EnableEvents = True
  End With
End Sub

 

[khelgadi]

Thanks D Coker for the input... I was unable to try it for last two days.... I'll use it and get back to you.
Thanks a lot again and sorry for late reply.

 

[khelgadi]

BTW
what is vbCrLf _ ??

 

[Marc L]

VB constant as you can see using F1 key (VBA help, all is here !) within VB environment …​

​