Results 1 to 6 of 6

Skip row in For...Next loop to automate e-mails

Threaded View

  1. #1
    Registered User
    Join Date
    03-01-2012
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2010
    Posts
    64

    Skip row in For...Next loop to automate e-mails

    Hello folks

    I'm hoping this is a quick fix but I cannot get this to work and it's driving me mad. I'm adapting some code I found to automate sending e-mails from an Excel file. So far that works fine. I've then added a column to say 'Yes' once an e-mail has been sent so that the macro doesn't keep sending e-mails (FYI once this code is fully working I'll use Task Scheduler to run the procedure every day, hence the need to check off rows that have had an e-mail previously). This works fine also.

    What I want to do now though is tell this macro to skip a row if an e-mail has been sent and move to the next row, and I cannot get this to work. What piece of code do I need to do this? Any help is very much appreciated, I've attached my Excel file and the code I'm using is below:

    Sub Check_Tasks()
         
        Dim lastRow As Long, r As Long
         
        With Sheets("todo")
            lastRow = Cells(Rows.Count, "B").End(xlUp).Row
            For r = 5 To lastRow
                If Cells(r, "I").Value = "Yes" Then 'How do I skip and go to next line that doesn't contain a 'Yes' in column I?
                             
                If Cells(r, "E").Value = Date Then Send_Outlook_Email "Task Reminder", .Cells(r, "H").Value, .Cells(r, "G").Value
               
                Cells(r, "I").Value = "Yes"
                
            Next r
        End With
         
    End Sub
     
     
    Private Sub Send_Outlook_Email(subject As String, body As String, ToEmail As String)
         
        Dim OutlookApp As Object 'Outlook.Application
        Dim objMail As Object 'Outlook.MailItem
         
        On Error Resume Next
        Set OutlookApp = GetObject(, "Outlook.Application")
        On Error GoTo 0
        If OutlookApp Is Nothing Then Set OutlookApp = CreateObject("Outlook.Application")
         
        Set objMail = OutlookApp.CreateItem(0) 'olMailItem
        objMail.subject = subject
        objMail.body = body
        objMail.To = ToEmail
        objMail.Send
         
    End Sub
    Thanks
    Kenny
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Skip blank cells in a loop
    By k64 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2013, 02:29 PM
  2. Use a loop but skip a row
    By amartino44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 12:26 PM
  3. Using Excel to automate e-mails sent from Outlook?
    By Shelter417 in forum Excel General
    Replies: 3
    Last Post: 02-26-2012, 03:25 PM
  4. How to skip special cases in a For Each loop
    By smohyee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2011, 02:20 PM
  5. [SOLVED] Loop through email address list to send e-mails
    By Paul. in forum Excel General
    Replies: 1
    Last Post: 04-12-2005, 08:06 AM

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