今天的收获---实现excel工资单的自动发送

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

每个公司每月发工资的同时要给每个员工一张工资单。而发工资单的主要目的是让员工知道自己一个月来工资组成的详细情况,确认财务结算是否有误。一般的处理方式是直接在原来全员工资表的基础上给每个员工记录前插入表头等整张打印出来后一条一条的剪裁出来发给各个员工。这种方式对小公司来说还可以,但当公司达到好几十上百或上千的员工时,财务人员光每个月剪裁着工资单就要花不少时间,而且还要花费很多纸张

我们的解决方式是将每个人的表头及工资记录提取出来重新生成一个xls文件,同时启动outlook将它作为附件发送给相应的个人。这样每个月公司的财务人员做好了全员工资报表后只要点击一下一个按钮就可完成。这样既节省了财务人员的时间降低了他们的工作强度使他们有更多精力去做专业的事,同时也为公司节约了纸张。----别看小小纸张,在好多公司打印纸的消耗量有多大大家心里应该都有数,这可是一笔不小的开销啊。

该文就以上面的思想为主线来实现。具体代码如下:

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=""Excel 8.0;"""

cn.Open

Set xlApp = New Excel.Application

MsgBox "Creat ready." & [a1].End(xlDown).Row, vbInformation

For i = 2 To [a1].End(xlDown).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 & ":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 & "close", vbInformation

With oItemMail

.Subject = "工资单"

.Body = "请确认你的工资单,若有问题在本月10号之前到财务处确认"

.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", vbInformation

End Sub

各位只要在工资表上加一个BUTTON,再把这段代码略加修改就可使用了

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航