VBA for excel to send multiple emails.

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA for excel to send multiple emails.

  1. #1
    Board Regular
    Join Date
    May 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA for excel to send multiple emails.

     
    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.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA for excel to send multiple emails.

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    May 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for excel to send multiple emails.

    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.

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA for excel to send multiple emails.

    Hi Dominic, glad it was what you needed...

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    May 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA for excel to send multiple emails.

      
    Thanks Michael

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com