From: http://blog.csdn.net/chenxiangping/article/details/70821116

1, . set up Outlook email account (not the focus of this article)

2, . set up Outlook Trust Center as follows

If the following operation settings are not made, the prompt is as follows when Excel VBA calls Outlook to automatically send mail

2.1 Outlook - > tools - > Trust Center

2.2 programming access - > select "never send me suspicious activity warning (not recommended)", note: it is recommended to use Excel VBA to automatically send mail to enable this function

3, Enable Excel Macro

3.1 enable macro operations as follows:

Open Excel and click Office - > Excel, as shown below

Select "Trust Center" - > trust center setting on the left side of the "Excel option" form, as shown below:

In the "Trust Center" form - > macro settings, select the following two options

Then close Excel and open again to enable macro and VBA programming development.

4, Excel VBA development

4.1 create module class: clsModel, write the following code:

1. Public Declare Function SetTimer Lib "user32" _  
2.         (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long  
3. Public Declare Function KillTimer Lib "user32" _  
4.         (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long  
5. Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)  
6. Sub AutoMail()  
7.     GB_EMPSALARY.Show  
8. End Sub  
9.   
10. 'Function WinProcA(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idEvent As Long, ByVal SysTime As Long) As Long  
11. '    KillTimer 0, idEvent  
12. '    DoEvents  
13. '    Sleep 100  
14. '    'Use Alt+S Sending email is the key point of this article. It is the only way to automatically send email without security prompt  
15. '    Application.SendKeys "%s"  
16. 'End Function  
17.   
18. ' Subprogram to send a single message  
19. Sub SendMail(ByVal to_who As String, ByVal SubJect As String, ByVal body As String, ByVal cell As String)  
20.     Dim objOL As Object  
21.     Dim itmNewMail As Object  
22.     'Quote Microsoft Outlook object  
23.     Set objOL = CreateObject("Outlook.Application")  
24.     Set itmNewMail = objOL.CreateItem(olMailItem)  
25.     On Error GoTo Err_Handle  
26.       
27.     With itmNewMail  
28.         .SubJect = SubJect  'A purpose  
29.         .htmlBody = body    'Text text  
30.         '.body = body   'Text text  
31.         .To = to_who  'Recipient  
32.         '.Attachments.Add attachement 'Attachments. If you don't need to send attachments, you can delete this sentence, Excel The fourth column in cannot be deleted  
33.         .Display  'start-up Outlook Send window  
34.         'SetTimer 0, 0, 0, AddressOf WinProcA  
35.         .Send  
36.         'Application.Wait (Now + TimeValue("0:00:03"))  
37.         'Application.SendKeys "%s"  
38.     End With  
39.     Worksheets("Sheet1").Range(cell).Value = "Y"  
40.     Set objOL = Nothing  
41.     Set itmNewMail = Nothing  
42. Err_Handle:  
43.     Set objOL = Nothing  
44.     Set itmNewMail = Nothing  
45.     On Error Resume Next  
46. End Sub  

4.2 create an automatic email sending interface, so that users can see which line of Excel table to operate.

The main codes are as follows:

1. Private Sub butSend_Click()  
2.     On Error Resume Next  
3.     Dim i As Integer  
4.       
5.     Dim EmpName, eMail, mailSubJect, mailBody, cell, sendFlag As String  
6.       
7.       
8.     i = CInt(txtStartRow.Text)  
9.     If (i < 3) Then  
10.         i = 3  
11.     End If  
12.     'Mailbox subject  
13.         mailSubJect = "A company" & Worksheets("Sheet1").Range("C1").Value & "Payslip"  
14.     'Employee name  
15.     EmpName = Worksheets("Sheet1").Range("E" & i).Value  
16.     'Employee name is empty exit stop sending mail  
17.     Do While EmpName <> ""  
18.         'Send mail flag  
19.         sendFlag = Worksheets("Sheet1").Range("A" & i).Value  
20.         'e-mail address  
21.         eMail = Worksheets("Sheet1").Range("AH" & i).Value  
22.         'Whether the email is sent and closed, and whether the email address is empty  
23.         If (sendFlag <> "Y" And eMail <> "") Then  
24.            'Mailbox content  
25.            mailBody = SalaryContext(EmpName, i)  
26.            'Send flag cell or not  
27.            cell = "A" & i  
28.            SendMail eMail, mailSubJect, mailBody, cell  
29.         End If  
30.         i = i + 1  
31.         'Get employee name on next line  
32.         EmpName = Worksheets("Sheet1").Range("E" & i).Value  
33.         DoEvents  
34.         Sleep 300  
35.         txtSend.Text = i  
36.     Loop  
37. End Sub  
38. 'Payslip table details  
39. Function SalaryContext(ByVal EmpName As String, ByVal Row As Integer) As String  
40.     Dim htmlBody, tableHeader, tableBody As String  
41.     htmlBody = "<html>" & _  
42.         "<head>" & _  
43.         "<meta http-equiv=""Content-Type"" contentType=""application/vnd.ms-excel;charset=gb2312"">" & _  
44.         "   <STYLE type=text/css>" & _  
45.         "   .sub_title{" & _  
46.         "      FONT-WEIGHT: bold;" & _  
47.         "      FONT-SIZE: 4mm;" & _  
48.         "      VERTICAL-ALIGN: middle;" & _  
49.         "      TEXT-ALIGN: center" & _  
50.         "      background-color: #ffff66//" & _  
51.         "      }"  
52.           
53.     htmlBody = htmlBody & "   .context {" & _  
54.         "      font-size: 12px;" & _  
55.         "      BORDER-TOP-WIDTH: 0.6mm;" & _  
56.         "      PADDING-RIGHT: 1mm;" & _  
57.         "      PADDING-LEFT: 1mm;" & _  
58.         "      BORDER-LEFT-WIDTH: 0.6mm;" & _  
59.         "      BORDER-BOTTOM-WIDTH: 0.6mm;" & _  
60.         "      PADDING-BOTTOM: 0mm;" & _  
61.         "      PADDING-TOP: 0mm;" & _  
62.         "      BORDER-COLLAPSE: collapse;" & _  
63.         "      BORDER-RIGHT-WIDTH: 0.6mm" & _  
64.         "      }"  
65.   
66.     htmlBody = htmlBody & "   .context td{" & _  
67.         "      border:1px solid #009900;" & _  
68.         "      }" & _  
69.         "   .page {" & _  
70.         "      page-break-after: always;" & _  
71.         "      }" & _  
72.         "  </STYLE>" & _  
73.         "</head><body>Dear " & EmpName & Chr(13)  
74.       
75.     htmlBody = htmlBody & "<table class=""context"" borderColor=""#669933"" border=1>"  
76.     'MsgBox htmlBody  
77.     'Header  
78.     tableHeader = "<tr bgcolor=""#FFE66F""><td align=""center"">Fixer<br>Benchmark</td>" & _  
79.         "<td align=""center"">Floating performance<br>Benchmark</td><td align=""center"">Should be diligent<br>Hours</td>" & _  
80.         "<td align=""right"">Actual<br>Attendance</td><td align=""center"">section<br>holiday</td><td align=""center"">Assessment<br>coefficient</td>" & _  
81.         "<td align=""center"">fixed<br>wages</td><td align=""center"">Float<br>Achievements</td><td align=""center"">stay outside over-night<br>subsidy</td>" & _  
82.         "<td align=""right"">food&subsidy</td><td align=""center"">bonus</td><td align=""center"">Royalty</td>" & _  
83.         "<td align=""right"">subsidy</td><td align=""center"">Replacement</td><td align=""center"">Other<br>subsidy</td>" & _  
84.         "<td align=""right"">Should be issued<br>Total</td><td align=""center"">Late</td><td align=""center"">food</td>" & _  
85.         "<td align=""right"">social security</td><td align=""center"">common<br>MPF</td><td align=""center"">rent</td>" & _  
86.         "<td align=""right"">hydropower</td><td align=""center"">personal income tax</td><td align=""center"">Telephone bill</td>" & _  
87.         "<td align=""right"">Withholding tuition</td><td align=""center"">Other</td><td align=""center"">Withhold<br>Total</td>" & _  
88.         "<td align=""right"">Paid wages</td></tr>"  
89.    'MsgBox Worksheets("Sheet1").Range("F" & i).Value  
90.    'Table content  
91.    tableBody = "<tr>" & _  
92.         "<td>" & Worksheets("Sheet1").Range("F" & Row).Value & "</td>" & _  
93.         "<td>" & Worksheets("Sheet1").Range("G" & Row).Value & "</td>" & _  
94.         "<td>" & Worksheets("Sheet1").Range("H" & Row).Value & "</td>" & _  
95.         "<td>" & Worksheets("Sheet1").Range("I" & Row).Value & "</td>" & _  
96.         "<td>" & Worksheets("Sheet1").Range("J" & Row).Value & "</td>" & _  
97.         "<td>" & Worksheets("Sheet1").Range("K" & Row).Value & "</td>" & _  
98.         "<td>" & Worksheets("Sheet1").Range("L" & Row).Value & "</td>" & _  
99.         "<td>" & Worksheets("Sheet1").Range("M" & Row).Value & "</td>" & _  
100.         "<td>" & Worksheets("Sheet1").Range("N" & Row).Value & "</td>" & _  
101.         "<td>" & Worksheets("Sheet1").Range("O" & Row).Value & "</td>" & _  
102.         "<td>" & Worksheets("Sheet1").Range("P" & Row).Value & "</td>" & _  
103.         "<td>" & Worksheets("Sheet1").Range("Q" & Row).Value & "</td>" & _  
104.         "<td>" & Worksheets("Sheet1").Range("R" & Row).Value & "</td>" & _  
105.         "<td>" & Worksheets("Sheet1").Range("S" & Row).Value & "</td>" & _  
106.         "<td>" & Worksheets("Sheet1").Range("T" & Row).Value & "</td>" & _  
107.         "<td>" & Worksheets("Sheet1").Range("U" & Row).Value & "</td>" & _  
108.         "<td>" & Worksheets("Sheet1").Range("V" & Row).Value & "</td>" & _  
109.         "<td>" & Worksheets("Sheet1").Range("W" & Row).Value & "</td>" & _  
110.         "<td>" & Worksheets("Sheet1").Range("X" & Row).Value & "</td>" & _  
111.         "<td>" & Worksheets("Sheet1").Range("Y" & Row).Value & "</td>" & _  
112.         "<td>" & Worksheets("Sheet1").Range("Z" & Row).Value & "</td>" & _  
113.         "<td>" & Worksheets("Sheet1").Range("AA" & Row).Value & "</td>" & _  
114.         "<td>" & Worksheets("Sheet1").Range("AB" & Row).Value & "</td>" & _  
115.         "<td>" & Worksheets("Sheet1").Range("AC" & Row).Value & "</td>"  
116.   
117.    tableBody = tableBody & "<td>" & Worksheets("Sheet1").Range("AD" & Row).Value & "</td>" & _  
118.         "<td>" & Worksheets("Sheet1").Range("AE" & Row).Value & "</td>" & _  
119.         "<td>" & Worksheets("Sheet1").Range("AF" & Row).Value & "</td>" & _  
120.         "<td>" & Worksheets("Sheet1").Range("AG" & Row).Value & "</td>" & _  
121.         "</tr>"  
122.   
123.    'MsgBox tableBody  
124.       
125.    htmlBody = htmlBody & tableHeader & tableBody & "</table></body></html>"  
126.      
127.    SalaryContext = htmlBody  
128. End Function  

The contents of the Excel table are as follows