Free Microsoft Excel 2013 Quick Reference

Send Mass Email with Excel

I have a contact list in excel. In Range D2:D500 I have email addresses for all of my contacts. Is there an automated way to send an email to each of these contacts? Possibly with the help of VBA? Thank you for any guidance.

Post your answer or comment

comments powered by Disqus
Help!! I need to send an email from excel and have it pull the subject from a single cell in the worksheet. I also need it to pull a email address that I can set and will always be the same. Please help.

The code below is to send an email from Excel sheet to Outlook with a touch of button and it works great!!!! , Kudo to Ron De Bruin. However, I could not change the code to make Excel to open newsgroup instead of email and send messages to a
newsgroup. I have 20 students that I would like to send messages to their
Individule Newsgroup but I could not do it. This is the code that I have and
modified a little to fit my needs. But I can not have the macro to open a
newsgroup instead of email. Please help.

Thank you all in advance.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As
String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double

For r = 2 To 2 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = Cells(r, 1)

' Compose the message
Msg = Cells(r, 3)

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf,

' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString,

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:01"))
' Application.SendKeys "%s"
Next r
End Sub


Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show _

This is want I am working with to send the email. I just would like to be able to have the subject refernce to a cell. Please help.

Hi all,

If anyone can help me with sending an email through excel that will be of great help. I have a following question:

Actually, I have a excel with list of email contacts of 500 companies, with column A having company name and column B having email id's...I want to attach my resume to all the mails and write a common body but with part of the body replaced with company name. I would like to send the emails to all the contacts with a click of a button that will save lot of time.

Thanks and Regards,


Hi everyone,

Basically I have a data sheet with a list individuals in one column and individual messages in the next and their associated email addresses in another. I have an excel VBA code which sends the whole worksheet as an email attachment to a specific address. Is there a way to modify the code to send individual emails with individual messages via outlook?

I would basically like the macro to run through each ID and select and copy the data range associated to it as the body of the message and then send it to the corresponding email address. The datarange varies for each ID and I have more than a 1000 of them. I have attached an example sheet with this.

The code I have currently which sends the whole worksheet as an attachment is as follows:
Sub email()
Dim myOutlook As Object
Dim myMailItem As Object
Dim FName As String
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
FName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name
With otlNewMail
.To = ""
.CC = ""
.Subject = "Test"
.Body = "Test"
.Attachments.Add FName
.DeferredDeliveryTime = Range("A1")
End With

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub

Thanks guys.

Hi folks,

Happy Father's Day to all you fathers out there...

Not being a programmer...LOL, bet you never heard that b4.

I have a macro that combines a couple of sheets from a workbook into a third sheet, then makes a new workbook with only the single, combined, sheet. All this works well but now I have a manager who wants me to send an email with the "report" sheet as an attachment. Is there a way for Excel to open an email, with the email particulars (recipient(s), subject, etc.) in a couple of cells and attach the closed "report" file to it?

I'm doing it the usual way now but would like to automate the process if possible.

I just started using 2007 version today and am trying to also become familiar with it. All my files are in the 2003 version.


Hello. I have a VBA routine that successfully sends an email message. I'd like to do two things now that are causing trouble, and sending me into the "I don't know how to do this" realm.

The first is increasing the size of the message body. I'm coding in a mixture of strings and variable references that works perfectly until I try to increase the message body beyond a certain size (around 537 characters, including the "Chr(13)" entries). Not long after I get beginning adding items to the ".Body" of the message, I begin to experience message delivery failures which I'm assuming are size related. Is this about the limit to how large a message can get? If this is due to a method choice (.Body) is there another way to create content for a message that would be better?

The other problem is one of formatting. I'm being asked to include colored text, italicized text, and some center-justified lines to this message body. I'm uncertain how to insert formatting commands at this point (in the ".Body" line of code). This part of the problem isn't as important to me as the apparent "maximum length" problem...many of the recipients may get that formatting stripped away anyway, I'd guess.

How do i send via email my excel sheet. Outlook express always says ERROR or
wront SMTP, which I dont understand.

I am using the following code to send an email but I still have to click the
Send button. How do I automate this?

'Send by email the txt work file
Workbooks.OpenText Filename:=PCODir & PCOFil, Origin:=xlMSDOS,
StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1)),
Application.CutCopyMode = False
Application.Dialogs(xlDialogSendMail).Show Emailto, "Pick-confirm
file attached"

Does anyone know how can I force Excel to show my default file location after
sending a worksheet via a email attachment?

After sending a email from excel I have to close excel and reopen to see my
files in the default file location set in the excel options.

Thanks in advance for any and all suggestions


Hello Forum,
I have been learning more about Excel and VB over the last year and have been trying to automate more tasks related to my job. Currently I have a problem I could not find anything on the boards for, so here is my question:
I have a spreadsheet with about 2000 rows that have a name, email addresses and other information in it. How can I set up a mass email sending that would look something like this:

for i = 1 to 2000
from: myemail
to: [Cells(column A, row I).value]
Dear [cells(column B, row I).value],
You have not submitted information for your event named [cells(column C row I).value] in the [cells(column D, row I).value] term...

I could probably write some mediocre code to retrieve the information, but I have no idea how to do the output part of this task.

Thanks so much for any help.

I have a script (shown below) which allows me to send a mass mailing from Excel, but I am required to click yes in Outlook stating that I am indeed trying to send a message. The help section indicates that this is due to the use of the Item.Send function. Is there a way to turn this off or automate the Yes button? Funny thing is the button will not allow you to press Yes for 5 seconds after the msg appears. I have 200+ emails to send, so that's like 15+ minutes of pushing yes...

Thanks for any ideas.


Sub SendEmail()

Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String

Set OutlookApp = New Outlook.Application

For Each cell In Columns("G").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
Subj = "Monthly Market Survey"
Recipient = cell.Offset(0, -1).Value
EmailAddr = cell.Value

Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "You have received this automated message because your Market Survey has not yet been logged in. Please submit to me at If you have any questions or believe you received this message in error, please contact me at (123) 456-7890." & vbCrLf & vbCrLf
Msg = Msg & "Thanks." & vbCrLf & vbCrLf
Msg = Msg & "Chris"

Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
End With
End If
End Sub

I'm currently trying to send out a mass email with a pdf. attachment to a list of names and associated email addresses I have in excel. I have VBA code which will allow me to send the email out but I'm having trouble finding the proper code for adding a pdf. attachment to the email.

I'm also currently just using plain text for my signature (removed the actual signature before posting here) but would like to be able to pull my outlook signature I have saved on my comp and insert that.

If anyone can help me out I would really appreciate it.

Here's the code I'm currently using.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

Dim r As Integer, x As Double
For r = 1 To 100 'data in rows 1 - 100
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = "Rev1 Power Services"

' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "I am with (My Company). We Specialize in hard to find, highly qualified contract personnel, such as turbine technical advisors, controls engineers, project managers, schedulers, etc., as well as the more common disciplines. We also maintain a large pool of navy nuclear trained entry level personnel." & vbCrLf & vbCrLf
Msg = Msg & "(My Company) operates with a minimal overhead, so we can provied someo of the most competitive rates in the industry, while maintaining a tight focus on quality of service on all projects served." & vbCrLf & vbCrLf
Msg = Msg & "Current clients include American Electric Power, Dominion Power, Florida Power and Light, Consolidated Edison, Silicon Valley Power, Worley Parsons, URS Group, Washington Gropu Iinternational, Shaw Group, Tampa Electric Company, Santee Cooper, CH2M Hill, and many others." & vbCrLf & vbCrLf
Msg = Msg & "Additional information can be found via our website at (company website) and in the brochure I have attached to this email. I look forward to hearing back from you and hopefully my company can be of service to you in the timely and successful completion of your current and upcoming projects." & vbCrLf & vbCrLf & vbCrLf

Msg = Msg & "Sincerely" & vbCrLf & vbCrLf
Msg = Msg & ("Signature") & vbCrLf

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub

Hello everybody! I am very new to excel programming. I don't usually program using excel or vba but I have to for a project.

I have to find a way to send macro emails to members with a custom message with to their emails in this excel spreadsheet with their corresponding passwords in the next column. I want to use vba and I have Windows Mail.

Attached is a sample form of the members.

Loop to email one row of data to an email address
I am trying to create a macro that will send the header row (a1:p1) with a second row of data for a different email addresses. Mail being sent thru lotus. MSOffice 2007.

Email address for recipient is located in column Q. I want to create a loop (currently a counter) the will send and email to the address located in cell Qs, with the data contained on the same row A thru P.

My first obstacle was to get an email generated with data from excel and send. I found the code in this forum. I have been trying to figure out how to loop thru each row. I am attempting to use a counter. This may not be the best because I really just like to start sending data from row 2, 3 .... until no data exist. right now it is coded to send the data from rows 2 thru 5.

But. currently I have some type of code issue in red below; (run-time error '1004'application defined or object defined error). I have never coded in vba and am pretty sure it is a syntax issue.

.FieldSetText "EnterSendTo", Sheets("Sheet1").Range(Q, counter).Value

Any thoughts regarding this code? I really appreciate insight because this macro will streamline my job.

thanks in advance. love this forum.

Sub Email_Excel_Cells()

    Dim NSession As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim Email ' add new dim
    Dim counter As Integer
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    For counter = 2 To 5

        Set NDoc = NUIWorkSpace.ComposeDocument("", "", "Memo")
        Set NUIdoc = NUIWorkSpace.CURRENTDOCUMENT

        With NUIdoc
            .FieldSetText "EnterSendTo", Sheets("Sheet1").Range(Q, counter).Value
            .FieldSetText "EnterCopyTo", ""
            .FieldSetText "Subject", "Quarterly Supplier Performance " & Now
            .FieldSetText "Body", "Excel cells are pasted below this text" & vbNewLine &
vbNewLine & _
                "**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & _
                "Excel cells are pasted above this text"
            .GotoField ("Body")
            Sheets("Sheet1").Range("A1:p1").Copy        'the cells to copy and paste
            Application.CutCopyMode = False
              Sheets("Sheet1").Range(Cells(a, counter), Cells(p, counter)).Copy     'the cells to copy and paste
            Application.CutCopyMode = False
        End With

    Next counter
    Set NUIdoc = Nothing
    Set NDoc = Nothing
    Set NUIWorkSpace = Nothing
    Set NSession = Nothing
End Sub


After updating my spreadsheet 2x per month, I would like to be able to send individual emails to between 20-200 individuals each with their own row by following these rules:

1) If 'A' is negative - send email. If positive - nothing.
2) Send to: email address in 'B'
3) cc: email address in 'C'
4) with subject in 'D'
5) message in 'E'

I use Excel & Outlook 2007. I only have a rudimentary knowledge of Macros. In my searches, all I have found are macros that allow an entire workbook or worksheet to be emailed automatically. Any advice would be most appreciated.

Thank you,

I am using Office 2007 with Vista on a new computer. Long tyime user of the
Office products but never experienced this issue before. When I send a file
as an attachment to an email and try to send the email from Excel, the email
will remain open for several mintues instead of simply disappearing as it did
in the past. Some times, I have to close and confirm that it was actually
sent by Outlook.

Not sure if it is a setting in Outlook or Excel (does seem to happen in our
programs that allow you to send email from their menu bar).....

Anyone have any suggestions?

Hello Excel Experts:

Urgent help is required from you IT guys. I need to send emails
silently within Excel using VBA, and the following code doesn't work
for me (the code is found from

Sub Mail_Small_Text_CDO()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
sendusing") = 2
smtpauthenticate") = cdoBasic
smtpserver") = ""
sendusername") = "
sendpassword") = "mypassword"
smtpserverport") = 465
smtpusessl") = True
smtpconnectiontimeout") = 60

End With
'flds.Fields("urn:schemas:httpmail:importance") = 2
'flds.Fields("urn:schemas:mailheader:X-Priority") = 1

strbody = "Hi the testing from CDO" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.From = "Steineke"
.Subject = "Important message from Excel CDO"
.TextBody = strbody
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub

The intention of above sub is to compose an email and send it from
Gmail SMTP server through Excel VBA to my yahoo's email account. The
Excel workbook is running in a machine which is connected to internet
via either dial up or intranet connection, but without any email
client program installed (at least we don't care). The fwllowing error
code is displayed when .send is executed:

Run-time error '-2147220973(80040213)':
The transport failed to connect to the server.

I do have a gmail account and a yahoo account. I checked gmail help
about how to set the settings for sending and receiving emails. The
computer is connected to internet occasionally through dial-up
connection, and we want the VBA to send an email with some data back
to the headquarter without much intervention of human beings.

How to get it work? I'm not a good programmer with some knowledge
about VB. Your comments are highly appreciated.


This is easier to click

Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)

"Ron de Bruin" > wrote in message ...
> Hi HervinderSee John Walkenbach his website.
> --
> Regards Ron de Bruin
> (Win XP Pro SP-1 XL2002 SP-2)
> "Hervinder" > wrote in message ...
> > I have a very simple database on excel containing names
> > address, etc. One of the fields are email addresses. I
> > want to write a macro to send a personalised email to
> > each contact.
> >
> > The only part i know i will have problems with is the vb
> > code to send the email. I can get the macro to go through
> > each contact no problem all i need is the code that
> > actually sends the email. I do not want to attach the
> > spreadsheet to the email just a simple email.
> >
> > The email software i use is Outlook 2002.
> >
> > Can anyone help
> >
> > Thanx
> > Hervinder

I have a very basic working knowledge of Excel and am stuck with trying to send a list of email addresses.
I have read some of the other posts on similar topics but cannot understand the answers
(I am not familiar with codes at all).
To explain:

Cell A1 :A200 contains the Name list

Cells B1:B200 contain the relevant email addresses

Cells C1:C200 The relevant Userid

I need to send a email to all this user like this

From -- standard
To -- from work sheet cell ‘B’
subject: Syetem Migration -- standard
Attach : c:test.doc -- standard

Dear Mark Joseph, -- from work sheet cell ‘A’

Due to migration of our SAP Platform ,
Your userid and URL has been changed as follows.

User id : MJOSEPH -- from work sheet cell ‘C’
To access this new system You need to change your password using your userid or email id
using the following URL
Attachment : Guidelines to change the Password

I attcahed sample work sheet

We have just upgraded from Office 97 to 2003.

I have an Excel macro that sends an email with Outlook to inform of client changes. This worked fine in 97, but in 2003 you now get a message box saying:

You have a program trying to access addresses stored in Outlook. Do you want to allow this?

If you choose the Yes button 3 times it works, but choosing No brings up a VB error. I have tried reducing the security level in Outlook, but the message still appears.

Is it possible to hide this box and get it to choose Yes by default?


I am using the following code and it works great the only problem is that when I have more then one email address in the same cell it will not send the email. Even if I seperate it with a semicolon. It work fine if I have just one email address in the email field. How can I get it to send the same info to different email addresses.

Sub Email2()

    Dim rng As Range
    Dim Ash As Worksheet
    Dim Cws As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim NewWB As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long

    On Error GoTo cleanup

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    'Set filter sheet, you can also use Sheets("MySheet")
    Set Ash = ActiveSheet

    'Set filter range and filter column (column with e-mail addresses)
    Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
    FieldNum = 2    'Filter column = B because the filter range start in column A

    'Add a worksheet for the unique list and copy the unique list in A1
    Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Cws.Range("A1"), _
            CriteriaRange:="", Unique:=True

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

            'If the unique value is a mail addres create a mail
            If Cws.Cells(Rnum, 1).Value Like "?*@?*.?*" Then

                'Filter the FilterRange on the FieldNum column
                FilterRange.AutoFilter Field:=FieldNum, _
                                       Criteria1:=Cws.Cells(Rnum, 1).Value
                'Copy the visible data in a new workbook
                With Ash.AutoFilter.Range
                    On Error Resume Next
                    Set rng = .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                End With

                Set NewWB = Workbooks.Add(xlWBATWorksheet)

                With NewWB.Sheets(1)
                    .Cells(1).PasteSpecial Paste:=8
                    .Cells(1).PasteSpecial Paste:=xlPasteValues
                    .Cells(1).PasteSpecial Paste:=xlPasteFormats
                    Application.CutCopyMode = False
                End With

                'Create a file name
                TempFilePath = Environ$("temp") & ""
                TempFileName = "BOD " & Format(Now, "dd-mmm-yy h-mm-ss")

                If Val(Application.Version) < 12 Then
                    'You use Excel 2000-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                    'You use Excel 2007
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If

                'Save, Mail, Close and Delete the file
                With NewWB
                    .SaveAs TempFilePath & TempFileName _
                          & FileExtStr, FileFormat:=FileFormatNum
                    On Error Resume Next
                    .SendMail Cws.Cells(Rnum, 1).Value, _
                              "BOD Appointment Reminder"
                    On Error GoTo 0
                    .Close savechanges:=False
                End With

                Kill TempFilePath & TempFileName & FileExtStr
            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
    End If

    Application.DisplayAlerts = False
    Application.DisplayAlerts = True

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Hi, I would like to ask for your help with a problem of mine. I have a file which has a column that contains dates. what I need is when I open this file, excel will automatically look at the dates in the column and check if the date in the column is within 8 days from today. Let's say today's date is Jan 1 and the date in the column is Jan 5 so that means that the remaining days is 4 days up to Jan 5 and therefore within the 8 days. Once excel finds out that it is within 8 days it will automatically send an email to myself. I am not that good with excel. Thanks.

I know this may seem weird, but I'm trying to write some code that will send
an email from excel 97 with a specified subject, but all i get is
?subject=... added to the TO field of the email. Is there any way around
this or is it just excel 97. The current method works for all versions
after 97, but ideally I want it for all versions.

Many thanks


No luck finding an answer? You could always try Google.