Today's harvest - automatic transmission of Excel pay

xiaoxiao2021-03-06  77

Each company has a monthly salary to give each employee a pay. The main purpose of the payment order is to let employees know the details of the salary composition in one month and confirm that the financial settlement is incorrect. The general processing method is to give each of the employees before the total number of employees, which prints out, one-time, and other employees. This way is OK to the small company, but when the company reaches several tens of hundred or thousands of employees, the finance staff will spend a lot of time every month, but also spend a lot of paper.

Our solution is to extract each person's head and salary record to regenerate an XLS file while starting Outlook to send it as an attachment to the appropriate individual. This monthly, the financial staff of each month will be completed after the full wage statement is cleared. This saves both financial personnel to reduce their work intensity to make them more energetic, and also saving paper. ---- Don't see small paper, how many of the consumption of many company printing paper should have a number, this is a small overhead.

This article is implemented in the above idea. The specific code is as follows:

Private Sub CommandButton1_Click () Dim outFile Dim Email Dim cn As New ADODB.Connection Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim oOutlookApp As New Outlook.Application Dim oItemMail As Outlook.MailItem Set oItemMail = oOutlookApp.CreateItem (olMailItem) cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source = C: / Documents and Settings / gw / My Documents / Book3.xls;" & _ "Extended Properties = "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ) .Row Email = Cells (i, 11) MsgBox "Creat ready." & Email, vbInformation Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.ActiveSheet MsgBox "Creat is beginning." & i, vbInformation xlSheet.Range ( "A1: K1"). Value = Range ("A1: K1"). Value Xlsheet.Range ("A2: K2"). Value = Range ("A" & I & I & ": K" & I) .Value outfile = "C: / Documents and Settings /" & Cells (i, 4) & " .xls "MsgBox" Creat File Name. "& outfile, vbinformation xlbook.saveas" C: / Documents and Settings / "& Cells (i, 4) &" .xls "MSGBOX" Creat File "& Outfile &" Is Closing " , vbinformation xlbook.close false msgbox "Creat File" & Outfile & "

", vbinformation with OITEMMAIL .SUBJECT =" Payroll ".Body =" Please confirm your payroll, if you have any questions before this month, I've confirmed ".attachments.add (outfile) .importance = OlimportanceHigh. Recipients.Add (Email) .Sensitivity = olPersonal .Send End With outFile = "" Email = "" Set oItemMail = oOutlookApp.CreateItem (olMailItem) MsgBox "Mail" & (i - 1) & "is Created" Next cn.Close Set cn = nothing msgbox "all finish", Vbinformationnd Sub can use the salary table to add a Button, then this code can be used to modify this.

转载请注明原文地址:https://www.9cbs.com/read-120742.html

New Post(0)