VBA for excel to send multiple emails.

whiteevo4

Board Regular
Joined
May 12, 2010
Messages
77
Hello all,

I was wondering if you could help me. I have a list of email address in my workbook under column "A" and I wanted to use a VBA code to send an email to each individual email address using MS outlook with the subject in cell "B1" and the body message in cell "B2".


Any help would be great.

Thanks.</SPAN>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
This will send an E-Mail to every address in Column "A" starting at row 2 down to the last row
At the moment it is set to display before sending.
If you don't want to see each E-Mail first, disable the display line and uncomment the Send line.
Code:
Sub SendEm()
Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
        With Mail_Object.CreateItem(o)
            .Subject = Range("B1").Value
            .To = Range("A" & i).Value
            .Body = Range("B2").Value
            '.Send
            .display 'disable display and enable send to send automatically
    End With
Next i
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub
 
Upvote 0
Thanks Michael, this is exactly what I needed..Your the best...do you know of any free sites that I can learn VBA code?

Regards,

Dominic.
 
Upvote 0
Hi Dominic, glad it was what you needed...(y)

Learn VBA ??
Well, for a start, keep hanging around here, look at the questions, copy the nasers to your own workbook and try them out, make some adjustments and see what happens.....the only and best way to learn is hands on.
But if you want more, have a look through this list of books and sites, kindly compiled by Hiker95

Code:
Getting Started with VBA. 
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try 
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

Here's a good primer on the scope of variables.
Scope Of Variables And Procedures

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

What is a Visual Basic Module?
http://www.emagenit.com/VBA%20Folder...vba_module.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
http://www.cpearson.com/excel/createaddin.aspx

How do I create a PERSONAL.XLS(B) or Add-in
http://www.rondebruin.nl/personal.htm

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Y...Excel/631.html

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Learn to debug: 
http://www.cpearson.com/excel/debug.htm

How To: Assign a Macro to a Button or Shape
http://peltiertech.com/WordPress/how...tton-or-shape/

User Form Creation
http://www.contextures.com/xlUserForm01.html

When To Use a UserForm & What to Use a UserForm For
http://www.ozgrid.com/Excel/free-tra...ba2lesson2.htm

http://www.contextures.com/excel-dat...ation-add.html

Your Quick Reference to Microsoft Excel Solutions
http://www.xl-central.com/index.html

New! Excel Recorded Webinars
http://www.datapigtechnologies.com/ExcelMain.htm

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Email from XL - VBA
http://www.rondebruin.nl/sendmail.htm

Outlook VBA
http://www.outlookcode.com/article.aspx?ID=40

How to create Excel Dashboards
http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/management-dashboards-excel/
http://www.exceldashboardwidgets.com/
http://www.andypope.info/charts/gauge.htm 

Progress Meters
http://www.andypope.info/vba/pmeter.htm
http://www.xcelfiles.com/ProgressBar.html

And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com
http://www.excelforum.com
http://www.ozgrid.com
http://www.vbaexpress.com
http://www.excelfox.com
 
Upvote 0
Hi Michael Hope you are well.Can you please help to add an extra portion to the VBA code that you have done for Dominic, please? I need the same process but just to add a few lines of message in the body of the email to be sent out. Please see if you can help.Thanks Willem
 
Upvote 0
I have tried this code to send multiple mails from gmail but error is popping up please help?
Code:
Sub Send_Email_With_Gmail()

    Dim lrs As Long
    Dim newMail As CDO.Message
    Dim mailConfiguration As CDO.Configuration
    Dim fields As Variant
    Dim msConfigURL As String
    Dim subject, from, dest, cc, attchmnt, txtbdy, usrname, pass As String
    lrs = Cells(Rows.Count, "B").End(xlUp).Row
    subject = Sheet1.Range("D2").Value
    from = Sheet1.Range("A2").Value
    
    cc = Sheet1.Range("c2").Value
    attchmnt = ""
    txtbdy = Sheet1.Range("E2").Value
    usrname = Sheet1.Range("f2").Value
    pass = Sheet1.Range("G2").Value

    
    
    On Error GoTo errHandle
    
    Set newMail = New CDO.Message
    Set mailConfiguration = New CDO.Configuration
    
    
    mailConfiguration.Load -1
    
    For i = 2 To lrs
    dest = Sheet1.Range("B" & i).Value
    Set fields = mailConfiguration.fields
    
    With newMail
        .subject = subject
        .from = from
        .To = dest
        .cc = ""
        .BCC = ""
        ' To set email body as HTML, use .HTMLBody
        ' To send a complete webpage, use .CreateMHTMLBody
        .TextBody = txtbdy
        .AddAttachment "C:\Users\s.abhijeet\Desktop\Self-Assesment\Vba\Projects\project.xlsx"
    End With
    
    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
    
    With fields
        .Item(msConfigURL & "/smtpusessl") = True
        .Item(msConfigURL & "/smtpauthenticate") = 1
        
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2
        
        .Item(msConfigURL & "/sendusername") = usrname
        .Item(msConfigURL & "/sendpassword") = pass
        
        .Update
    
    End With
    
    newMail.Configuration = mailConfiguration
    newMail.Send
    Next i
    
    
    MsgBox "E-Mail has been sent", vbInformation
    
exit_line:
    '// Release object memory
    Set newMail = Nothing
    Set mailConfiguration = Nothing
    
    Exit Sub
    
errHandle:
    
    MsgBox "Error: " & Err.Description, vbInformation
    
    GoTo exit_line
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top