VBA(Mail a row or rows to each person in a range)

[Khazamula7]

Hi Family,

Kindly assist a fellow here:

I have thousands of orders that i need to follow up almost everyday, so i decided that since i can be able to pull out a report in a format as attached, and then i thought of wonders that vba can do, tried my magic but just couldn't come right.

What i wish to have in this worksheet is that with a click of a send button, emails are sent straight to the respective suppliers in the format stipulated in the attachment, cc the 8 sales personnel in each and every email sent. That is basically the gist of my query.

Regards,
Tsakani

 

[RDAngelo]

Possibly...

Sub test()
    Dim v As Variant, cc As Variant
    Dim i As Long, oMail As Object

    With Worksheets("Sheet1")
        v = .Cells(1, 1).CurrentRegion
        cc = Application.Transpose(.Range("I2:I" & .Cells(Rows.Count, 9).End(xlUp).Row))
    End With
   
    With CreateObject("outlook.application")
        For i = 2 To UBound(v)
            Set oMail = .createitem(0)
            With oMail
                .To = v(i, 1)
                .cc = Join(cc, ";")
                .Subject = "Ref: " & v(i, 2) & " - " & v(i, 5)
                .body = "Good day," & vbNewLine & vbNewLine & _
                        "Kindly update us on the ETA (Estimated Time of Arrival) for this order." & vbNewLine & vbNewLine & _
                        v(1, 2) & vbTab & vbTab & v(1, 4) & vbTab & vbTab & v(1, 5) & vbTab & vbTab & v(1, 7) & vbNewLine & _
                        v(i, 2) & vbTab & vbTab & v(i, 4) & vbTab & vbTab & v(i, 5) & vbTab & vbTab & v(i, 7) & vbNewLine & _
                        vbNewLine & vbNewLine & "Kind regards," & vbNewLine & vbNewLine
                .display 'or .send
            End With
            Set oMail = Nothing
        Next i
    End With
End Sub