Thread: Macro to copy and paste variable ranges according to values in a column

Hi Everyone,

I am new to macros and I normally only record them. I am trying to copy and select a variable range according to IDs in first column.
My basic objective is to copy the range associated to a person and then paste it somewhereelse. every ID has a variable range of information associated with them.
Can someone suggest a macro code to go through more than thousands of ID and copy the range associated with it and be able to paste it to any one particular area of a new sheet.
here is an example of what I would like to achieve.
A B C
1 1234 xxxx yyyy aaa@gmail.com
2 1234 xxxx yyyy
3 1234 xxxx yyyy
4 1234 xxxx yyyy
5 1234 xxxx yyyy
6 5678 zzzz aaaa bbb@gmail.com
7 5678 zzzz aaaa
8 5678 zzzz aaaa
9 5678 zzzz aaaa
10 5678 zzzz aaaa
11 5678 zzzz aaaa
12 5678 zzzz aaaa
13 5678 zzzz aaaa
14 5678 zzzz aaaa


I would like the macro to be able to copy cells b1:c5 and then paste it as a body of a message to the first email address and send it then copy and paste range b6:c14 as a body of a message to the second email address and send it..
so far I have been able to find codes that help me send info in every cell to their associated email address, I just need to be able to select and paste a variable range to the message of the body to complete the code:

Sub Create_Mail_From_List()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
       
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = Cells(cell.Row, "A").Value                                
                .Send  'Or use Display.
            End With
            On Error GoTo 0
            Set OutMail = Nothing
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Hi guys,

I still am struggling with the code.. can someone please help.

Thanks.