方法
打開要發(fā)送的工資
啟用開發(fā)工具
點(diǎn)擊【插入】
選擇【命令控件】
點(diǎn)擊【右鍵】,選擇【屬性】
雙擊圖中區(qū)域進(jìn)入VBA編程界面
【輸入】Private Sub CommandButton1_Click()
'要能正確發(fā)送并需要對(duì)Microseft Outlook進(jìn)行有效配置
On Error Resume Next
Dim rowCount, endRowNo, endColumnNo, sFile$, sFile1$, A&, B&
Dim objOutlook As Object
Dim objMail As MailItem
'取得當(dāng)前工作表數(shù)據(jù)區(qū)行數(shù)列數(shù)
endRowNo = ActiveSheet.UsedRange.Rows.Count
endColumnNo = ActiveSheet.UsedRange.Columns.Count
'取得當(dāng)前工作表的名稱,用來作為郵件主題進(jìn)行發(fā)送
sFile1 = ActiveSheet.Name
'創(chuàng)建objOutlook為Outlook應(yīng)用程序?qū)ο?br>Set objOutlook = CreateObject("Outlook.Application")
'開始循環(huán)發(fā)送電子郵件
For rowCount = 2 To endRowNo
'創(chuàng)建objMail為一個(gè)郵件對(duì)象
Set objMail = objOutlook.CreateItem(olMailItem)
With objMail
'設(shè)置收件人地址,數(shù)據(jù)源所在列數(shù)
.To = Cells(rowCount, 5)
'設(shè)置抄送人地址(從通訊錄表的'E-mail地址'字段中獲得)
'.CC = Cells(rowCount, 0)
'設(shè)置郵件主題,取值工作表名,
.Subject = sFile1
'設(shè)置郵件內(nèi)容(從通訊錄表的“內(nèi)容”字段中獲得)
'align單元格文本顯示方式 left(向左)、center(居中)、right(向右),默認(rèn)是center, width-寬 height-高border 單元格線粗細(xì),bordercolor返回或設(shè)置對(duì)象的邊框顏色
'colSpan是一種編程語言,其屬性可設(shè)置或返回表元橫跨的列數(shù)
sFile = "<tr>您好!<br> 以下是您" + sFile1 + ",請(qǐng)查收!</tr>"
sFile = sFile + "<table align='left' width='500' height='25' border= 1 bordercolor='#000000'> <tbody> "
sFile = sFile + "<tr><td colspan ='4' align='center'> 工資表</td> </tr> "
B = 1
For A = 1 To endColumnNo
'數(shù)據(jù)表頭中添加“X”后將不發(fā)送此字段
If Application.WorksheetFunction.CountIf(Cells(1, A), "*X*") = 0 Then
If B = 1 Then
sFile = sFile + "<tr><td width='20%' height='25'> " + Cells(1, A).Text + " </td> <tdwidth='30%' height='25'> " + Cells(rowCount, A).Text + "</td>"
B = 0
Else
sFile = sFile + "<td width='20%' height='25'> " + Cells(1, A).Text + " </td> <tdwidth='30%' height='25'> " + Cells(rowCount, A).Text + "</td> </tr>"
B = 1
End If
End If
Next
.HTMLBody = sFile
'設(shè)置附件(從通訊錄表的“附件”字段中獲得)
.Attachments.Add Cells(rowCount, 24).Value
'自動(dòng)發(fā)送郵件
.Send
End With
'銷毀objMail對(duì)象
Set objMail = Nothing
Next
'銷毀objOutlook對(duì)象
Set objOutlook = Nothing
'所有電子郵件發(fā)送完成時(shí)提示
MsgBox rowCount - 2 & "個(gè)員工的工資單發(fā)送成功!"
End Sub
聲明:本網(wǎng)頁內(nèi)容旨在傳播知識(shí),若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系,我們將在第一時(shí)間刪除處理。TEL:0731-84117792 E-MAIL:11247931@qq.com