Code For Whole Column Instead of One Cell

Hey guys,

How would I edit this line to send an email to each of the email addresses in column "BF instead of just one?"

.To = "[email protected]"

I'm not sure how to write it but maybe a loop would do this?

Yes a loop could do this.

Dim x as Integer

   For x = 1 To Range("MyEmailList").Rows.Count
      'your code for creating emails

       .To = Range("MyEmailList").Cells(x,1)

   Next x

Thank you so much. You have no idea how much this has helped me out heh

Here is my code if anyone ever has the same situation:

Sub Email()
    Dim Sendrng As Range
    Dim x As Integer

   For x = 1 To Range("MyEmailList").Rows.Count

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sendrng = Selection

    With Sendrng

        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            With .Item
                .To = Range("MyEmailList").Cells(x, 1)
                .Subject = "Whatever my Boss wanted to put in here..."
                .Send
            End With

        End With
    End With

Next x

StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub

Richard, Thanks again

Hi,

Glad to help, and thanks for the feedback and sharing the code with others.

Just one other observation you might find useful. When I had something similar in my previous working life, I used to use a few cells on the spreadsheet to hold various messages, naming them Message1, Message2 etc. Then in the VBA code I used

.Subject = Range("Message1") & " " & Range("Message2") '....etc

or even use IF..Then structures with .Subject message ranges depending on the results of such tests.

Doing it this way means standard messages can be edited by a non VBA user just by changing the sheet. If the message cells are empty then they are not printed.

HTH

Thats a great idea, thanks for the suggestions. It would make things a lot easier to code that way.

Anything to help simplify and understand this better is a great help.

Thanks again.