Code to send Specific Row of data as an Email to the address mentioned in each Cell.


Hi there,

I have been trying to look for methods where in i can use a VBA code to send mails to my customers.

Here is a breif on how i need the code,

i have a sheet where in there are details of Outstanding Amounts that needs to be collected.

I want to use the code to send the mail of each customer in each line with a constant Body line from A1 to I4.

the Subject line should have the following Line "Pending C Forms - followed by the customer name in each line (Cell E4 and corresponding cells)".

If i could find some help from you guys it would be of a great help and it owuld make my life easlier.

File enclosed for reference.




Try this macro:

Sub SendOutlookMessages()

'This macro will send an email through Outlook to a list of
'recipients whose emails are in excel.

 Set OL = CreateObject("Outlook.Application")
 Set MailSendItem = OL.CreateItem(olMailItem)

 For Each xcell In Sheets("sheet1").Range("J4:J10000").Cells
    If Len(xcell.Value) > 0 Then

    recipient = xcell.Value

    'Get all of the cells for the body text into one variable
    'Currently each cell is separated by a single space
    For i = 1 To 9
      body_text = body_text & " " & Cells(xcell.Row, i).Value
    Next i

    With MailSendItem
         .Subject = "Pending C Forms - " & Cells(xcells.Row, 5).Value
         .Body = body_text
         .To = recipient
     End With

    End If
 Next xcell

Set OL = Nothing

End Sub

Try it on sample data first!!!

Also, the cell data for the message body is separated by only a single space right now but you can change that to whatever you want by changing " " in this line: body_text = body_text & " " & Cells(xcell.Row, i).Value


Answer the Question

You must create an account to use the forum. Create an Account or Login