Excel sends emails using vba

Sub Mail_Text_in_Body ()
Dim msg As String, cell As Range
Dim Recipient As String, Subj As String, HLink As String
Dim Recipientcc As String, Recipientbcc As String
Recipient = "someone@21cn.com"
Recipientcc = ""
Recipientbcc = ""
Subj = "Testbodymail"
Msg = "Dear customer"
HLink = "mailto:" & Recipient &"? "&" Cc = "& Recipientcc &" bcc = "& Recipientbcc &"&"
HLink = HLink & "subject =" & Subj &"&"
HLink = HLink & "body =" & msg
ActiveWorkbook. FollowHyperlink (HLink)
Application. Wait (Now + TimeValue ("0:00:03 "))
Application. SendKeys "% s"
End Sub

 

Send an email in VBA (1. Use the Outlook component)

01. 'Use Outlook to send emails
02. Sub SendEmailByOutlook ()
03.
04. 'to be correctly sent, and you need to configure Microseft Outlook effectively
05. On Error Resume Next
06. Dim rowCount, endRowNo
07. Dim objOutlook As New Outlook. Application
08. Dim objMail As MailItem
09.
10. 'obtain the number of rows in the data area of the current worksheet connected to Cells ().
11. endRowNo = Cells (1, 1). CurrentRegion. Rows. Count
12.
13. 'create objOutlook as the Outlook Application Object
14. Set objOutlook = New Outlook. Application
15.
16. 'start sending emails cyclically. For example, starting from the second line, the first line is the title.
17. For rowCount = 2 To endRowNo
18.
19. 'create objMail as an email object
20. Set objMail = objOutlook. CreateItem (olMailItem)
21. With objMail
22.
23. 'set the recipient address (for example, from the "email address" field in the first column of the Excel table)
24. To = Cells (rowCount, 1). value' "fantasia@sina.com"
25.
26. 'set the mail subject (for example, obtained from the "mail subject" field in the second column of the Excel table)
27. Subject = Cells (rowCount, 2). valu' "Subject"
28.
29. 'set mail content (for example, obtained from the "Mail content" field in the third column of the Excel table)
30. Body = Cells (rowCount, 3). Value '"email content"
31.
32. 'set the attachment (for example, from the "attachment" field in the fourth column of the Excel table)
33. Attachments. Add Cells (rowCount, 4). valu' "c: \ users. ctl"
34.
35 .. Send
36.
37. End
38.
39. 'Destroy the objMail object
40. Set objMail = Nothing
41.
42. Next
43.
44. 'Destroy the objOutlook object
45. Set objOutlook = Nothing
46.
47. End Sub

Paste a button in Excel and click the event to point to this process. Microsoft Outlook 11.0 Object Library must also be referenced in the Visual Basic Editor of Excel. The specific component name may vary depending on the installed Outlook version. You can only use Outlook instead of Outlook Express.

You must Configure Outlook correctly to use the default account to send emails. The code above implements sending emails using the default account of Outlook.

One of the most serious problems with this method is that every time you send an email, a security prompt for Outlook appears,

"Yes" one by one, which will definitely make you get bored. Some say that SendKeys or other software is used to automatically "yes", but it is not very good. It cannot truly achieve the effect of automatic batch mail. However, in Outlook Express and Outlook 2007 security settings, there is an option to warn me (W) when other applications try to send an email in my name, however, one VBA does not know how to use Outlook Express, and the other is not installed with an advanced version like 2007 on my computer. So we have to find another method, that is, the JMail component.

 

You can search w3 JMail Personal V4.3 on the network, or directly go to the official website to download the http://www.dimac.net, w3 Jmail authorization method is free. After JMail is installed by default, the Examples directory jmail.dll1_w3jmail4.pdf and readme.txt Files are displayed in C: \ Program Files \ Dimac \ w3JMail4. You can view the help in w3jmail4.pdf. The file we want is actually the jmail. dll. The installer will automatically help you register jmail. dll. If you have not run the installation program but copied jmail. dll, run regsvr32 jmail. dll to register it.

You can look at the Examples directory and find that it can support ASP, Assembly, Delphi, Visual C ++, and xml. It can also be used in languages like JS, VBS, VB, and VBA. It can be used not only to send emails, but also to receive emails through the POP protocol. The following code is used to receive emails:

01. 'Use the JMail component to send emails
02. Function JmailSend (Subject, HtmlBody, MailTo)
03. Dim JmailMsg As New jmail. Message
04. With JmailMsg
05 .. Encoding = "GBK"
06 .. MailServerUserName = "Unmi" 'smtp authentication Username
07 .. MailServerPassWord = "xxxxxx" 'smtp verification Password
08 .. AddRecipient MailTo 'recipient
09 .. AddRecipientCC "fantasia@sina.com" 'CC
10. AddRecipientBCC "broodwar@2911.net" 'bcc
11 .. From = "unmi@2911.net"
12 .. FromName = "yehuang Ying"
13. Charset = "GBK"
14. ContentType = "text/html"
15. '. Priority = 1' importance level
16. Logging = true' is True. You can use JmailMsg. Log to obtain detailed Log information for interaction with the server.
17 .. Silent = true' is True. If an error occurs, JmailMsg. ErrorMessage is returned.
18. Subject = Subject
19. '. Body = htmlbody' is sent as plain text content. Set the Body attribute.
20. HtmlBody = HtmlBody 'can be used to send Html content
21.
22. AddAttachment "c: \ users. ctl" 'send attachments
23.
24. If. Send ("mail.2911.net") then' specifies the email server
25. JmailSend = "sent successfully"
26. Else
27. JmailSend = JmailMsg. ErrorMessage
28. End If
29. MsgBox JmailMsg. Log
30 .. Close
31. End
32. Set JmailMsg = Nothing
33. End Function

To reference the registered "JMail 4.0 Library" in the Microsoft Visual Basic Editor of Excel, The AddXxx method in the code can be executed multiple times, such as adding multiple recipients and attachments. Batch automatic sending is realized, and the email server can be dynamically selected in the program.

Using the w3 JMail component is much more convenient than calling Outlook. You do not need to configure Outlook. You only need to bring a jmail. dll dynamic library at the time of release and register it with regsvr32 jmail. dll.

 

XMLHttpRequest separates functions. Excel only submits Http requests. JSP is used to complete actual mail sending. Of course, it can also be implemented in other forms. So there are two sections of implementation code:

JSP code uses the commons-email component of Apache, and activation is also required. jar and mail. jar. For details about how to use commons-email, refer to the previous article: Use apache commons-email to easily send ungarbled emails. For example, if the file is stored as sendmail. jsp, the URL used for access via a browser is http: // 192.168.1.100: 8080/WebUtils/sendmail. jsp:

01. <% @ page contentType = "text/html; charset = UTF-8" pageEncoding = "UTF-8" %>
02. <% @ page import = "org. apache. commons. mail. *, java.net. *" %>
03. <%
04.
05./* the email sending page can only be submitted in post Mode
06. parameter description:
07. to: email recipient
08. subject: Mail title
09. body: email body
10 .*/
11.
12. String method = request. getMethod ();
13.
14. if (method. Handle signorecase ("post") {// only process post requests
15.
16. // set the request character set to iso8859-1, and then call toUTF8 to solve the garbled Problem
17. request. setCharacterEncoding ("iso8859-1 ");
18. String to = toUTF8 (request. getParameter (""));
19. String subject = toUTF8 (request. getParameter ("subject "));
20. String body = toUTF8 (request. getParameter ("body "));
21.
22. // send an email with attachments and HTML content
23. HtmlEmail email = new HtmlEmail ();
24. email. setHostName ("smtp.sina.com ");
25.
26. // The email must be sent to the server for verification. The user name/Password
27. email. setAuthentication ("fantasia", "xxxxxx ");
28. email. addTo ();
29. email. setFrom ("fantasia@sina.com", "Unmi ");
30.
31. // set the subject's character set to UTF-8
32. email. setCharset ("UTF-8 ");
33. email. setSubject (subject );
34. try {
35. email. setHtmlMsg (body );
36. email. attach (new URL ("file: // c | SendMail. java"), "SendMail. java", "SendMail. java ");
37. email. buildMimeMessage ();
38. email. send ();
39. out. print ("sent successfully ");
40.} catch (Exception ex ){
41. out. print (ex. getMessage ());
42 .}
43 .}
44. %>
45. <%!
46. private String toUTF8 (String src) throws UnsupportedEncodingException {
47. String dst = new String (src. getBytes ("ISO8859-1"), "UTF-8 ");
48. return dst;
49 .}
50. %>
01. Function SendEmailByHttp (MailTo, Subject, Body)
02. Dim XmlHttp As New MSXML2.XMLHTTP60 'variable type is slightly different depending on the MSXML version you reference, such As XMLHTTP50
03. Dim PageUrl, data
04. data = "to =" & MailTo & "subject =" & Subject & "& body =" & Body
05. PageUrl = "http: // 192.168.1.100: 8080/WebUtils/sendmail. jsp"
06.
07. 'If your web application has been configured with form Verification, you need to add the last two parameters.
08. XmlHttp. Open "POST", PageUrl, False, "username", "password" 'synchronous Mode
09.
10. 'Post needs to set this request header when submitting the form.
11. XmlHttp. setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
12.
13. 'The character set in the subsequent lines of the Request Header does not seem to be able to solve the garbled problem.
14. 'xmlhttp. setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset = UTF-8"
15. 'xmlhttp. setRequestHeader "Accept-Charset", "UTF-8"
16. 'xmlhttp. setRequestHeader "charset", "UTF-8"
17. 'XmlHttp. setRequestHeader "Content-Type", "charset = UTF-8"
18.
19. XmlHttp. Send data 'send the request
20. If XmlHttp. Status = 200 Then
21.
22. 'Remove the carriage return, line feed, and space on both sides of the jsp output.
23. SendEmailByHttp = Trim (Replace (XmlHttp. responseText, Chr (10), ""), Chr (13 ),""))
24. Else
25. SendEmailByHttp = "sending failed! "'Failed requests returned
26. End If
27. End Function