Batch high-speed email sending using Excel

You can use Excel to send emails at a high speed in two steps:

1. Prepare the data to be sent:

A.) Activate exceland create a new book1.xlsx.

B.) Fill in the following content,

First column: recipient, second column: Mail title, third column: body, fourth column: attachment path

Note: The attachment path isCan have Chinese Characters,Space is not allowed

Here you can write a lot of other content, each line is sent as an email.

Note: EmailThe text is black and white, and does not support bold or font color.. (If you need to support color emails, the solution will be provided later)

2. Write a macro mail

A) alt + F11 open the macro editor and Choose Insert> module from the menu.

B) paste the following code into the module Code Editor:

'Code list-1

Public declare function settimer lib "USER32" _ (byval hwnd as long, byval nidevent as long, byval uelapse as long, byval lptimerfunc as long) as longpublic declare function killtimer lib "USER32" _ (byval hwnd as long, byval nidevent as long) as longprivate declare sub sleep lib "Kernel32" (byval dwmilliseconds as long) function winproca (byval hwnd as long, byval umsg as long, byval idevent as long, byval receivime as long) As long killtimer 0, idevent doevents sleep 100 'Use Alt + S to send an email, this is the key of this article. It does not require a security prompt to allow you to send emails by yourself. sendkeys "% s" end function 'sub Sendmail (byval to_who as string, byval subject as string, byval body as string, byval attachement as string) dim objol as object dim itmnewmail as object 'References Microsoft Outlook object set objol = Createobject ("Outlook. application ") set itmnewmail = objol. createitem (olmailitem) with itmnewmail. subject = subject 'subject. body = body 'body this article. to = to_who 'recipient. attachments. add attachement 'attachment. If you do not need to send an attachment, you can delete this sentence. Leave the fourth column in Excel blank and you cannot delete it. display 'start outlook sending form settimer 0, 0, 0, addressof winproca end with set objol = nothing set itmnewmail = nothingend sub' Batch Sending mail sub batchsendmail () dim rowcount, endrowno = cells (1, 1 ). currentregion. rows. count 'send mail row by row for rowcount = 1 to endrowno Sendmail cells (rowcount, 1), cells (rowcount, 2), cells (rowcount, 3), cells (rowcount, 4) nextend sub

Finally, the effect in the code editor is as follows:

I

To run the code correctly, you must

Select Tools> microseft outlook x.0 Object Library in reference. (x.0 is a version and may vary with machines)

C .) paste the code, check the above items, and then send the email. Click the green triangle button displayed in the red circle. The displayed dialog box is displayed. Click execute, the batch mail is sent.

D .) suppose you want to confirm whether your emails have been sent out, you can go to the "sent emails" Directory of outlook to check whether there are emails you want to send. If so, congratulations, close the ticket ~~

---------------------------------------------------------------------

Below

1. How to send color emails

2. How to replace some content in the body, for example, each email may have different names and different numbers reported to the recipient.

3. How to send multiple attachments

---------------------------------------------------------------------

1.WhatSendColor email

Two steps are required to send a color email,

Step 1:The above code needs to be modified.(Bold text in red and htmlbody ):

'Code list-2

'Subprogram Sendmail for sending a single mail (byval to_who as string, byval subject as string, byval body as string, byval attachement as string) dim objol as object dim itmnewmail as object 'References Microsoft Outlook object set objol = Createobject ("Outlook. application ") set itmnewmail = objol. createitem (olmailitem) with itmnewmail. subject = subject 'Theme '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~ . Htmlbody = body. This line is different from the previous one. The rest are the same ~ '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~ . To = to_who 'recipient. attachments. add attachement 'attachment. display 'start outlook sending form settimer 0, 0, 0, addressof winproca end with set objol = nothing set itmnewmail = nothingend sub

Step 2: modify the content of the third column (column C) of Excel. You need to know a little bit about the HTML language.

For example, if you want to red or bold the word "Tax Return" in the email, change the content in the third column:

Hello, the following are the <font color = "red"> <B> tax returns </B> </font> ,...

Final Effect

Go to the sender to check the effect:

Note: editing the body in an Excel file does not take effect if you perform bold or colored operations. You must use HTML. Sorry, no HTML friends can use Sina Weibo follow. I help: @ researcher raywill

2. What Replace part of the body

In two steps:

1. Change the Excel content

2. Change code

1. Change the Excel content:

Replace the changed part with the form of [= XXXX =. Note: There is no space in the middle.

For example, the number [= 1 =] is replaced by the content of column E, and [= 2 =] is replaced by the content of column F, if there are many other columns, you can add many other columns, such as [= 3 =], [= 4 =], and so on.

2. Change the code to replace the "batch mail" program with the following code:

'Batch send mail sub batchsendmail () dim rowcount, endrowno dim newbody dim replacecount, maxreplacecount dim pattern endrowno = cells (1, 1 ). currentregion. rows. count 'send mail row by row for rowcount = 1 to endrowno' Replace the content of the current row template maxreplacecount = 2' with a few replicas. In this example, there are two replicas, write 2 newbody = cells (rowcount, 3) for replacecount = 1 to maxreplacecount pattern = "[=" & CSTR (replacecount) & "=]" newbody = worksheetfunction. substitute (n Ewbody, pattern, cells (rowcount, 4 + replacecount) Next 'is replaced, send an email! Sendmail cells (rowcount, 1), cells (rowcount, 2), newbody, cells (rowcount, 4) nextend sub

Note: In the above line of "maxreplacecount = 2" code, 2 needs to be changed to your own value. replace it with a few places and write a few (add a few columns) the above two columns E and F are added, that is, 2. If you add 3 replicas (columns E, F, and g), write 3.

However, you do not need to add new columns to content that needs to be replaced repeatedly. For example, "westward journey" appears twice in the email, [= 2 =] can be used repeatedly.

3.WhatSend multiple attachments

In actual application scenarios, you may need to send multiple attachments. In fact, it is very easy to change the Sendmail subprogram to the following:

'Subprogram Sendmail for sending a single mail (byval to_who as string, byval subject as string, byval body as string, byval attachement as string) dim objol as object dim itmnewmail as object dim attaches dim attach 'reference Microsoft Outlook object set objol = Createobject ("Outlook. application ") set itmnewmail = objol. createitem (olmailitem) with itmnewmail. subject = subject 'subject. htmlbody = body 'body this article. to = to_who 'recipient. display'start outlook to send the form attaches = Split (attachement, ";") for each attach in attaches if (LEN (attach)> 0) then. attachments. add attach end if next settimer 0, 0, 0, addressof winproca end with set objol = nothing set itmnewmail = nothingend sub

In an Excel attachment column (the third column), multiple attachments are separated by semicolons (";", not ";"). For example:

C: \ doc \ graduation certificate attachment .jpg; C: \ doc \ .docx

Finally codeFor example, the following features are summarized: Batch replacement, color mail, and multi-attachment.

Public declare function settimer lib "USER32" _ (byval hwnd as long, byval nidevent as long, byval uelapse as long, byval lptimerfunc as long) as longpublic declare function killtimer lib "USER32" _ (byval hwnd as long, byval nidevent as long) as longprivate declare sub sleep lib "Kernel32" (byval dwmilliseconds as long) function winproca (byval hwnd as long, byval umsg as long, byval idevent as long, Byval receivime as long) As long killtimer 0, idevent doevents sleep 100 'Use Alt + S to send emails, which is the key of this article, security-free prompts allow you to send emails by using the application. sendkeys "% s" end function 'sub Sendmail (byval to_who as string, byval subject as string, byval body as string, byval attachement as string) dim objol as object dim itmnewmail as object dim attaches dim attach 'reference Microsoft Outlook object set objol = Createobject ("outlo OK. application ") set itmnewmail = objol. createitem (olmailitem) with itmnewmail. subject = subject 'subject. htmlbody = body 'body this article. to = to_who 'recipient. display'start outlook to send the form attaches = Split (attachement, ";") for each attach in attaches if (LEN (attach)> 0) then. attachments. add attach end if next settimer 0, 0, 0, addressof winproca end with set objol = nothing set itmnewmail = nothingend sub 'batch send mail sub Batchsendmail () dim rowcount, endrowno dim newbody dim replacecount, maxreplacecount dim pattern endrowno = cells (1, 1 ). currentregion. rows. count 'send mail row by row for rowcount = 1 to endrowno' Replace the content of the current row template maxreplacecount = 2' with a few replicas. In this example, there are two replicas, write 2 newbody = cells (rowcount, 3) for replacecount = 1 to maxreplacecount pattern = "[=" & CSTR (replacecount) & "=]" newbody = worksheetfunction. substitute (newbody, Pa Ttern, cells (rowcount, 4 + replacecount) Next 'is replaced, send an email! Sendmail cells (rowcount, 1), cells (rowcount, 2), newbody, cells (rowcount, 4) nextend sub

Exam documents:

Http://www.officefans.net/cdb/viewthread.php? Tid = 53888

The security prompt box is not displayed during the email sending process, and the sending speed is extremely fast ;)

Feedback from netizens:

• Sender: angel3814
• Time: 2013-01-28 10:35:30

Dear valued customer, this method has been used to send a large number of emails (more than 100 emails. Dozens of emails .) There are some problems, because the program will send Alt + s in a unified manner only after all the word sending forms are created, which is very easy and causes insufficient memory, and, the last Alt + S is no longer running. in actual application, I can only write another button, send five messages each time, count the sent messages + 5, and click it manually. I 'd like to ask you, is there a better way to improve it?

Thanks to the solution provided by angel3814:

Sub batchsendmail () dim rowcount, endrowno, csheet as worksheet, ssheet as worksheet, I as integer, J as integer endrowno = cells (1, 1 ). currentregion. rows. count 'send mail line by line set csheet = worksheets ("Mail content") set ssheet = worksheets ("send") I = ssheet. cells (2, 1 ). value J = ssheet. cells (2, 2 ). value for rowcount = I to J Sendmail csheet. cells (rowcount, 1), csheet. cells (rowcount, 2), csheet. cells (rowcount, 3), csheet. cells (rowcount, 4) Next ssheet. cells (2, 1 ). value = I + 5 ssheet. cells (2, 2 ). value = J + 5end sub

Click it again, take the initiative to + 5, and then click

The reason for using 5 is that the half test found that more than 10, there is a very high probability that the Alt + S event does not take effect (maybe the delay problem ?)

====

In addition, if you want to send emails in batches, you do not have to limit your thinking to outlook. If you know the POP3 address of the company's mail server, you 'd better use the command line tool to send mails in batches by yourself.

For example: Blat: http://www.blat.net/syntax/syntax.html

Use a free tool to prepare a block of emails, save them as text files, and use Blat to send emails one by one.