Results 1 to 12 of 12

VBA to Outlook - Help with Looping

Threaded View

MorganWJ VBA to Outlook - Help with... 08-25-2021, 12:06 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 02:59 PM
MorganWJ Re: VBA to Outlook - Help... 08-25-2021, 03:20 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 04:45 PM
MorganWJ Re: VBA to Outlook - Help... 08-25-2021, 04:57 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 05:05 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 05:11 PM
MorganWJ Re: VBA to Outlook - Help... 08-25-2021, 05:23 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 05:47 PM
cubangt Re: VBA to Outlook - Help... 08-25-2021, 05:59 PM
MorganWJ Re: VBA to Outlook - Help... 08-26-2021, 07:13 AM
Hitulseo Re: VBA to Outlook - Help... 05-12-2023, 08:19 AM
  1. #1
    Registered User
    Join Date
    11-13-2020
    Location
    Austin, TX
    MS-Off Ver
    2016
    Posts
    6

    VBA to Outlook - Help with Looping

    Hi, I am a relatively new user and am needing help in looping a macro which will send customized emails to a list of recipients using data from Excel.

    I am using the code below, which works perfectly for the first row. However when Outlook opens, it generates identical emails on an endless loop. The intention is to send emails to all the recipients in Column P using the data from other columns until it reaches an empty row or cell.

    Any suggestions would be greatly appreciated! Thanks.


    Sub SendEmails_Click()
    
    Dim i As Integer
    Dim email, body, subject, copy, EEfirst, EElast, GDIbldgname, GDIbldgnumb, HRCbldgname, HRCbldgnumb As String
    Dim OutApp As Object
    Dim OutMail As Object
    
    body = ActiveSheet.TextBoxes("TextBox 1").Text
    
    i = 2
    
    Do While Cells("P12").Value <> ""
    
        email = Range("P12").Value
        subject = Range("AB12").Value
        copy = Range("H12").Value
        EEfirst = Range("U12").Value
        EElast = Range("W12").Value
        GDIbldgname = Range("X12").Value
        GDIbldgnumb = Range("D12").Value
        HRCbldgname = Range("Y12").Value
        HRCbldgnumb = Range("C12").Value
    
        body = Replace(body, "U12", EEfirst)
        body = Replace(body, "W12", EElast)
        body = Replace(body, "X12", GDIbldgname)
        body = Replace(body, "D12", GDIbldgnumb)
        body = Replace(body, "Y12", HRCbldgname)
        body = Replace(body, "C12", HRCbldgnumb)
    
    Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.createitem(0)
        With OutMail
             .To = email
             .cc = copy
             .subject = subject
             .body = body
             .display
             '.Send
        End With
        
            body = ActiveSheet.TextBoxes("TextBox 1").Text
            i = i + 1
        Loop
        
        Set OutMail = Nothing
        Set OutApp = Nothing
        
        MsgBox "Email(s) Sent!"
    
    End Sub
    
    Last edited by MorganWJ; 08-25-2021 at 01:11 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Looping doesn't work, putting appointments in Outlook via Excel
    By Fean in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2020, 06:19 AM
  2. Replies: 1
    Last Post: 08-21-2018, 01:48 PM
  3. Outlook looping
    By Spirit ICT in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-04-2017, 08:51 AM
  4. Looping through Filter and then copying the data so I can paste or put into outlook body
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-28-2016, 12:12 AM
  5. [SOLVED] Using Exell VBA to launch Outlook Email while separating lines in the body of Outlook.
    By Tazyote in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-27-2013, 01:18 PM
  6. [SOLVED] VBA Macro to print to Pdf format and place as attachment in Outlook (Excel & Outlook 2007)
    By Webman1012 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2013, 01:25 PM
  7. looping through range of cells & sending multiple emails with outlook
    By splosczynski in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2008, 01:49 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1