[RESOLVED] Help with For Loop

[billboy]

I really struggle with loops error: End if block without if

I tried to create a second loop for Recipient
Sub SendEmail()

Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim cell2 As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String

Set OutlookApp = CreateObject("Outlook.Application")

For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then

For Each cell2 In Columns("B").Cells.SpecialCells(xlCellTypeConstants)

If IsEmpty(cell2.Value) Then
cell2.Value = ""

Subj = UserForm1.TextBox1.Text
Recipient = cell2.Value
EmailAddr = cell.Value

Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = UserForm1.TextBox2.Text


Msg = Msg & "Thanks." & vbCrLf & vbCrLf
Msg = Msg & "Chris"

Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display
SendKeys ("%{s}")

End With

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End If
Next

End Sub

[vbfbryce]

You are missing a "next." I would suggesting indenting to make it easier to see, for example:

Sub loops()
'
    For i = 1 To 10
    
        For j = 1 To 5
    
        Next j
    
    Next i
End Sub

[billboy]

Thanks that is the problem, I cant get my head around the nested for loop and the correct place to place the next

This got me a bit closer but I just get the same recipient name for each email, so my next is not correct

For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "*@*" Then

For Each cell2 In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
     If IsEmpty(cell2.Value) Then
       Recipient = "TEST"
     Else
Recipient = cell2.Value

End If
Next cell2
Subj = UserForm1.TextBox1.Text
EmailAddr = cell.Value

Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & UserForm1.TextBox2.Text

'Msg = Msg & "Thanks." & vbCrLf & vbCrLf
'Msg = Msg & "Chris"

Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem

.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display
SendKeys ("%{s}")

End With

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End If
Next cell

End Sub

[billboy]

I solved my issue by using cell.offset

Thanks

Still would interested to know how to do it using the loop for my own knowledge so I will leave the post for a day or two

Thanks

[DataMiser]

If I am understanding what you are trying to do then the Next Cell2 line needs to be much lower in the code.

For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "*@*" Then

                For Each cell2 In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
                    If IsEmpty(cell2.Value) Then
                        Recipient = "TEST"
                    Else
                        Recipient = cell2.Value

                    End If
                    'Next cell2
                    Subj = UserForm1.TextBox1.Text
                    EmailAddr = cell.Value

                    Msg = "Dear " & Recipient & vbCrLf & vbCrLf
                    Msg = Msg & UserForm1.TextBox2.Text

                    'Msg = Msg & "Thanks." & vbCrLf & vbCrLf
                    'Msg = Msg & "Chris"

                    MItem = OutlookApp.CreateItem(olMailItem)
                    With MItem

                        .To = EmailAddr
                        .Subject = Subj
                        .Body = Msg
                        .Display()
                        SendKeys("%{s}")

                    End With

                    newHour = Hour(Now())
                    newMinute = Minute(Now())
                    newSecond = Second(Now()) + 5
                    waitTime = TimeSerial(newHour, newMinute, newSecond)
                    Application.Wait(waitTime)
                Next cell2
            End If
        Next cell

From Green location to red location

[billboy]

That sending each email twice, I have 2 in a test workbook

This loop is driving me loopy

[DataMiser]

It seemed like the logical spot to put the next given the code there but I am not sure what you are doing there really. I do not code in Excel, and when given a grid of data I would loop through the rows in a single loop rather than nesting a loop to loop through all cells in a column. Not sure what the logic is there.

[billboy]

I need to loop through one column A to get email address and then next column B to get name of recipient to place in the message of the email

loop might not be the best or only way to accomplish this

Cell.Offset works great for gretriving the Value of Column B, except I would need to know before hand were that column is in relation to Column A

In other words it might not alway be column and Column B

So I was trying to code so the user could place what column the appropriate Value is in

So in plain english


Loop through column ? if it has an email address then get the name from column ? and place that in the recipient message and send email

[vbfbryce]

If you're asking how to let the user provide the input (which columns contain the data), do something like this:

Sub cols()
    Dim emailCol As Integer
    Dim offsetCol As Integer
    Dim myOffset As Integer
    
    emailCol = InputBox("Enter the column number containing e mail addresses...(ie. C = 3)")
    offsetCol = InputBox("Enter the other column number...")
    myOffset = offsetCol - emailCol
End Sub

Then use one or more of those variables in your loop (ONE loop, not two).

[DataMiser]

Loop through column ? if it has an email address then get the name from column ? and place that in the recipient message and send email

And to use a loop you would

Loop through rows
Check Column1 of the current row for email address
If that column has an email address then grab the value from column 2 of that same row
do your stuff
continue the loop

By using a nested loop on columns you would be doing a lot more looping than needed and getting the same things several times.

Say if you had 100 rows in the sheet A loop on rows executes 100 times and checks all the data in the sheet
A nested loop on columns Executes the outter loop 100 times and the inner loop 100 times * 100 times

[billboy]

If you're asking how to let the user provide the input (which columns contain the data), do something like this:

Sub cols()
    Dim emailCol As Integer
    Dim offsetCol As Integer
    Dim myOffset As Integer
    
    emailCol = InputBox("Enter the column number containing e mail addresses...(ie. C = 3)")
    offsetCol = InputBox("Enter the other column number...")
    myOffset = offsetCol - emailCol
End Sub

Then use one or more of those variables in your loop (ONE loop, not two).

Thanks for that. I was acually thinking of a way to translate Column Alphabet to Column Numbers to use offset more variably

Since most users dont think in terms of Column 1 2 etc.. they think of rows that way.

If I know my email address column is set to Column B for example and the Name is set to column F , is there a way to calculate the number of columns between B and F so i can use that number in the offset function

That would be better then two loops as you suggested

[vbfbryce]

To get column letter input, and convert to number:

Sub colNames()
    Dim strCol As String    'column letter
    Dim intCol As Integer   'column number
    strCol = InputBox("Enter column (ie. 'A')")     'user enters column letter
    intCol = Columns(strCol).Column         'convert to number
    MsgBox intCol
End Sub

If you get input for two different columns and convert each to number, then it would be easy to figure out how far apart they are. For example, they tell you the first column is B, you convert to 2. They tell you the second is F, you convert to 6. You subtract the 2 from the 6 to get your offset.

[billboy]

To get column letter input, and convert to number:

Sub colNames()
    Dim strCol As String    'column letter
    Dim intCol As Integer   'column number
    strCol = InputBox("Enter column (ie. 'A')")     'user enters column letter
    intCol = Columns(strCol).Column         'convert to number
    MsgBox intCol
End Sub

If you get input for two different columns and convert each to number, then it would be easy to figure out how far apart they are. For example, they tell you the first column is B, you convert to 2. They tell you the second is F, you convert to 6. You subtract the 2 from the 6 to get your offset.

Thanks thts what I want to do, but not sure how to convert A to 1 B to 2 etc...

[vbfbryce]

Copy the sub that I showed you and step through it.

It will ask for a letter, and convert to a number, and show you in a msg box.

[billboy]

Copy the sub that I showed you and step through it.

It will ask for a letter, and convert to a number, and show you in a msg box.

Oh duh my bad, sorry I see the line that does that.

Thanks so much that is great

Do you know how to keep the Font Size and style of the text in my textbox to my message, I allow the user to change the font size, name styles etc..
But they dont carry over into the message

Msg = Msg & UserForm1.TextBox2.Text

'Msg = Msg & "Thanks." & vbCrLf & vbCrLf
'Msg = Msg & "Chris"

MItem = OutlookApp.CreateItem(olMailItem)
With MItem

.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display()
SendKeys("%{s}")