For Loop Help?

[andrewb90]

Hey there,

I have been trying to do a for loop to change my currently working but long code.
Basically I have 30 rows and each row sends a unique message. And currently I am using this code:

Sub FOH1()  Dim r As Range, c As Range
  Dim sTo As String, ppdf As String, pdf As String, p As String
  Set r = Worksheets("Employees").Range("L6:M6")
  For Each c In r
    With c
      If InStr(.Value2, "@") <> 0 Then sTo = sTo & "," & .Value2
    End With
  Next c
  
  If sTo = "" Then
    MsgBox sTo, vbCritical, "You have no emails to send this to!"
    Exit Sub
  End If


  sTo = Right(sTo, Len(sTo) - 1)
        Gmail "email@gmail.com", "password", "" _
        , "o> " _
        & vbNewLine & Sheets("Print").Range("D1") _
        & vbNewLine & Sheets("Employees").Range("C6") & " " & Sheets("Employees").Range("D6") & " (" & Sheets("Employees").Range("E6") & ") - " & Sheets("Employees").Range("F6") _
        & vbNewLine _
        & vbNewLine & Sheets("Print").Range("G3") & "  " & Sheets("Print").Range("G4") & "  " & Format(Sheets("Print").Range("G6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("H6") & "  " & Format(Sheets("Print").Range("I6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("J3") & "  " & Sheets("Print").Range("J4") & "  " & Format(Sheets("Print").Range("J6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("K6") & "  " & Format(Sheets("Print").Range("L6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("M3") & "  " & Sheets("Print").Range("M4") & "  " & Format(Sheets("Print").Range("M6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("N6") & "  " & Format(Sheets("Print").Range("O6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("P3") & "  " & Sheets("Print").Range("P4") & "  " & Format(Sheets("Print").Range("P6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("Q6") & "  " & Format(Sheets("Print").Range("R6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("S3") & "  " & Sheets("Print").Range("S4") & "  " & Format(Sheets("Print").Range("S6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("T6") & "  " & Format(Sheets("Print").Range("U6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("V3") & "  " & Sheets("Print").Range("V4") & "  " & Format(Sheets("Print").Range("V6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("W6") & "  " & Format(Sheets("Print").Range("X6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("Y3") & "  " & Sheets("Print").Range("Y4") & "  " & Format(Sheets("Print").Range("Y6").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("Z6") & "  " & Format(Sheets("Print").Range("AA6").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("AB6") & " Hours" _
        & vbNewLine & vbNewLine & "The first message has the front of house schedule attached." _
        & vbNewLine & vbNewLine & "Happy Clucking," _
        & vbNewLine & "Schedule Master Zax" _
        & vbNewLine & "0>" _
        , sTo _
        , "1@2.3" _
        , pdf
End Sub

This code runs, then this code (and several others consistently like it run)

Sub FOH2()  Dim r As Range, c As Range
  Dim sTo As String, ppdf As String, pdf As String, p As String
  Set r = Worksheets("Employees").Range("L7:M7")
  For Each c In r
    With c
      If InStr(.Value2, "@") <> 0 Then sTo = sTo & "," & .Value2
    End With
  Next c
  
  If sTo = "" Then
    MsgBox sTo, vbCritical, "You have no emails to send this to!"
    Exit Sub
  End If


  sTo = Right(sTo, Len(sTo) - 1)
        Gmail "email@gmail.com", "password", "" _
        , "o> " _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"D1"[/COLOR]) _
        & vbNewLine & Sheets("Employees").Range("C7") & " " & Sheets("Employees").Range("D7") & " (" & Sheets("Employees").Range("E7") & ") - " & Sheets("Employees").Range("F7") _
        & vbNewLine _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"G3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"G4"[/COLOR]) & "  " & Format(Sheets("Print").Range("G7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("H7") & "  " & Format(Sheets("Print").Range("I7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"J3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"J4"[/COLOR]) & "  " & Format(Sheets("Print").Range("J7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("K7") & "  " & Format(Sheets("Print").Range("L7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"M3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"M4"[/COLOR]) & "  " & Format(Sheets("Print").Range("M7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("N7") & "  " & Format(Sheets("Print").Range("O7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"P3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"P4"[/COLOR]) & "  " & Format(Sheets("Print").Range("P7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("Q7") & "  " & Format(Sheets("Print").Range("R7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"S3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"S4"[/COLOR]) & "  " & Format(Sheets("Print").Range("S7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("T7") & "  " & Format(Sheets("Print").Range("U7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"V3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"V4"[/COLOR]) & "  " & Format(Sheets("Print").Range("V7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("W7") & "  " & Format(Sheets("Print").Range("X7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range([COLOR=#ff0000]"Y3"[/COLOR]) & "  " & Sheets("Print").Range([COLOR=#ff0000]"Y4"[/COLOR]) & "  " & Format(Sheets("Print").Range("Y7").Value, "HH:nn AM/PM") & "  " & Sheets("Print").Range("Z7") & "  " & Format(Sheets("Print").Range("AA7").Value, "HH:nn AM/PM") _
        & vbNewLine & Sheets("Print").Range("AB7") & " Hours" _
        & vbNewLine & vbNewLine & "The first message has the front of house schedule attached." _
        & vbNewLine & vbNewLine & "Happy Clucking," _
        & vbNewLine & "Schedule Master Zax" _
        & vbNewLine & "0>" _
        , sTo _
        , "1@2.3" _
        , pdf
End Sub

I marked in red the values that would always be the same. The rest of the values go down one row starting at row 6 and going to row 23.
If the Employees!L:M range is empty in a particular row, the code should just skip over as there are no emails in either cell, and therefore no schedule to send.

I'm hoping I've explained what I am trying to accomplish fairly well. Everything works now, but its so much code and if I ever need to change something, I have to change it nearly 20 times and it seems like there should be a way to consolidate this.

Thanks!

 

[andrewb90]

bump^^

 

[My Aswer Is This]

The only explanation of what your attempting to do is:
"If the Employees!L:M range is empty in a particular row, the code should just skip over as there are no emails in either cell, and therefore no schedule to send."

With that little bit of explanation I'm surely not able to help you.
Maybe you could explain more about what this lengthy about of code is attempting to do.

 

[andrewb90]

Well, I suppose I should rephrase since my code really already does that. (The vbCritical message that says there are no emails)

I just need some sort of loop that sends out out the worksheet info. starting with row 6 all the info will get sent out, then the code move to row 7 and all the info goes down by one row expect the cells marked in red (they will stay the same regardless of what line the code is on.) This will continue onto row 23.

I am just looking for a solution that only needs this typed out once instead of having 17 different subs to call upon.

I hope that helped a bit

 

[andrewb90]

Bump

 

[andrewb90]

Well, I suppose I should rephrase since my code really already does that. (The vbCritical message that says there are no emails)

I just need some sort of loop that sends out out the worksheet info. starting with row 6 all the info will get sent out, then the code move to row 7 and all the info goes down by one row expect the cells marked in red (they will stay the same regardless of what line the code is on.) This will continue onto row 23.

I am just looking for a solution that only needs this typed out once instead of having 17 different subs to call upon.

I hope that helped a bit

was i able to help?