Use Excel to send e-mail in bulk

Use Excel to send e-mail in bulk at high speed in two steps:

1. Prepare the data to be sent:

A.) Open Excel, New book1.xlsx

B.) Fill in the following content,

First column: Receiver, second column: message header, third column: Body, fourth column: Attachment path

Note: You can have Chinese in the attachment path, but there can be no spaces

Here you can write a lot of other things, each line as an e-mail sent.

Note: The message body is black and white text content, does not support bold, font color and so on . (Assuming that you need to support color messages, you will be given a workaround later)

2. Write a macro to send a message

A.) Alt + F11 Open Macro Editor, menu select: Insert-and-module

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 nide Vent as Long) as Longprivate Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds as Long) Function Winproca (ByVal hwnd A s long, ByVal umsg as Long, ByVal idevent as Long, ByVal SysTime as Long) as Long KillTimer 0, idevent DoEvents S Leep 100 ' use Alt+s to send mail, this is the key point of this article, to avoid the security prompt to send their own unsolicited mail all rely on it Application.sendkeys "%s" End Function ' send a single message sub-program sub SendMail (Byva    L 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 ' keynote. Body = Body ' text. to = To_who ' recipient. AttaChments. ADD attachement ' attachment, if you do not need to send an attachment, you can delete this sentence, Excel in the fourth column is left blank, can not be deleted oh.  Display ' Start Outlook send form SetTimer 0, 0, 0, AddressOf Winproca End with Set objOL = Nothing Set itmnewmail = NothingEnd Sub ' bulk Send mail sub Batchsendmail () Dim rowCount, Endrowno endrowno = Cells (1, 1). CurrentRegion.Rows.Count ' progressive mail for rowCount = 1 to Endrowno SendMail cells (rowCount, 1), Cells (RowCount, 2) , Cells (RowCount, 3), Cells (RowCount, 4) NextEnd Sub

Finally, the effects in the Code Editor are as follows:

I

In order to run the code correctly, you also need to

Menu: Tools-microseft Outlook x.0 Object Library tick on (x.0 is version, different machines may not be the same)

C.) Paste the code, tick the above east to send the message, click a red circle to see the green triangle button, will pop up the dialog box, click to execute, began to send bulk mail.

D.) Let's say you want to make sure your email is sent out and be able to go to Outlook's "Sent Items" directory to see if there are any messages you wish to send, fake, congratulations, and a break.

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

The following commentary

1. How to send color mail

2. How to replace some of the contents of the text, for example, each email may be the most beginning of the name of different, to the other side of the number of the report is not equal

3. How to send multiple attachments

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

1. how to send color mail

Sending a color message requires two steps

The first step: the above code needs to be changed (red bold text, body changed to HTMLBody):

' Code list-2

' Sub-program to send a single message sub SendMail (ByVal to_who As String, ByVal subject As String, ByVal body As String, ByVal attachement as Strin g) Dim objOL As Object Dim itmnewmail As Object ' reference to Microsoft Outlook Object Set objOL = CreateObject ("Outlook.appl Ication ") Set Itmnewmail = Objol.createitem (olMailItem) with itmnewmail. Subject = Subject ' Keynote ' ~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~         . HTMLbody = Body ' text This article, only this line is different from the previous, the rest are the same oh ~ ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~. to = To_who ' recipient. Attachments.Add attachement ' accessories.  Display ' Start Outlook send form SetTimer 0, 0, 0, AddressOf Winproca End with Set objOL = Nothing Set itmnewmail = NothingEnd Sub

Step Two: Change the contents of the third column (column C) of Excel, which requires you to understand a little HTML language

For example, if you want to make the "tax return" three characters red and bold in the mail, change the contents of the third column to:

Hello, the following is this week's <font color= "red" ><b> tax return </b></font>

Finally effect

Go to the Outbox to see the effect:

Note: In Excel, the text is edited and the bold, color-adding operation does not take effect. Must use HTML himself, sorry oh no HTML friends can Sina Weibo follow I help: @ Researcher Raywill

2. how Replace body part content

In two steps:

1. Change Excel Content

2. Change the Code

1. Change the Excel content:

Replace the part of the change with [==xxxx==] in this form. Note: There are no spaces in the middle.

For example, the number [==1==] will be replaced by the contents of the e column, [==2==] will be replaced by the contents of the F column, and so on, if there are many other, add many other columns, [==3==], [==4==] and so on.

2. Change the code to replace the "bulk email" program with the following code:

' Bulk Send mail sub Batchsendmail ()    Dim rowCount, Endrowno    Dim newbody    Dim Replacecount, Maxreplacecount    Dim Pattern    Endrowno = Cells (1, 1). CurrentRegion.Rows.Count        ' progressive message for    RowCount = 1 to Endrowno        ' replace the current line template contents        Maxreplacecount = 2   ' There are several places to write, two in the example, 2        newbody = Cells (RowCount, 3) for        Replacecount = 1 to maxreplacecount            pattern = "[= =" & Amp CSTR (replacecount) & "= =]"            newbody = Worksheetfunction.substitute (newbody, pattern, Cells (RowCount, 4 + Replacecount))        Next        ' replaced, send an email!        SendMail Cells (rowCount, 1), Cells (RowCount, 2), newbody, Cells (RowCount, 4)            NextEnd Sub

Note: Above "Maxreplacecount = 2" This line of code, 2 need to change to your own value, replace a few places to write a few (newly added several columns to write a few) on the above added to the E, F two column, is 2, if you add 3 replacement (E, F, g column), write 3.

Just, for the content that needs to be replaced repeatedly, do not need to add new column, for example, "Big Talk Tour" appeared in the Mail two times, can repeatedly use [==2==] to represent.

3. how to send multiple attachments

Multiple attachments may need to be sent in real-world scenarios, in fact very easy to change the SendMail subroutine into the following:

' Sub-program to send a single message sub SendMail (ByVal to_who As String, ByVal subject As String, ByVal body As String, ByVal attachement as Strin g)    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  ' keynote        . HTMLbody = Body   ' text of this article        . to = to_who  ' recipient        . Display  ' Start Outlook send 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 = not Hing    Set itmnewmail = NothingEnd Sub

In Excel's Attachment column (third column), multiple attachments are separated by semi-colon semicolons (";", not ";"), such as:

C:\doc\ Diploma Annex. Jpg;c:\doc\ Certificate. docx

Finally the code is for example the following: Summary of bulk replacement, color mail, multi-attachment features

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 nide Vent as Long) as Longprivate Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds as Long) Function Winproca (ByVal hwnd A s long, ByVal umsg as Long, ByVal idevent as Long, ByVal SysTime as Long) as Long KillTimer 0, idevent DoEvents S Leep 100 ' use Alt+s to send mail, this is the key point of this article, to avoid the security prompt to send their own unsolicited mail all rely on it Application.sendkeys "%s" End Function ' send a single message sub-program sub SendMail (Byva    L 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 ' Keynote. H Tmlbody = Body ' text of this article       . to = To_who ' recipient.                Display ' Start Outlook send 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 ob  JOL = Nothing Set itmnewmail = NothingEnd Sub ' bulk Send mail sub Batchsendmail () Dim rowCount, Endrowno Dim newbody Dim Replacecount, maxreplacecount Dim pattern endrowno = Cells (1, 1). CurrentRegion.Rows.Count ' progressive message for rowCount = 1 to Endrowno ' replace the current line template contents Maxreplacecount = 2 ' There are several places to write, two in the example, 2 Newbody = Cells (RowCount, 3) for replacecount = 1 to Maxreplacecount pattern = "[= =" & CStr (replacecount) & "= =]" Newbody = Worksheetfunction.substitute (newbody, pattern, Cells (row        Count, 4 + replacecount)) Next ' replaced, send an email! SendMail cells (RowCount, 1), Cells (RowCount, 2), NEWBODY, Cells (RowCount, 4) NextEnd Sub 

References:

http://www.officefans.net/cdb/viewthread.php?tid=53888

This article will not pop up the security prompt box during the sending of the message, the sender speed is very fast;)

User feedback:

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

Hello, after testing, this method for a large number of sent mail (more than 100 letters. Dozens of no problem. There are some problems, because the program must be established after all Word send form, will be unified alt+s send, very easy to cause memory shortage, and, the last Alt+s will no longer run, in the actual application, I can only write a button, each send 5, send complete Count + 5, manually re-point; want to consult with you, can there be a better way to improve?

Thank you for the solution provided by angel3814:

Sub batchsendmail ()    Dim rowCount, Endrowno, Csheet as Worksheet, ssheet as Worksheet, I As Integer, j As Integer    E Ndrowno = Cells (1, 1). CurrentRegion.Rows.Count    ' progressive send mail    set csheet = Worksheets ("message contents")    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

Point once, own initiative +5, then point

The reason for using 5, is to test found that more than 10, there is a very large probability that the Alt+s event does not take effect (or perhaps a delay problem?). ）

====

In addition, for the students who want to send mail in bulk, they can not confine their thinking to Outlook. Suppose you know the POP3 address of the company's mail server, it is best to use the command line tool to implement the bulk of the mail itself unsolicited.

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

Use a random tool to prepare a sealed message, save it as a text file, and then use Blat to cycle through the send.

Use Excel to send e-mail in bulk