how to send each row by email

hi,

In my excel document from column A to G in every single row, there are private datas of in each person. For example, the first row consists of person A's private data. Second row is only about person B, etc.. In column I , their email addresses exist. What I am trying to do is to send each row as an email to related person. Your VBA code help would be highly appreciated.

Regards
Baris

Hi Baris,

Welcome to ExcelFox !!!

Try this

Code:

---

Sub SendEmailRowByRow()
   
    Dim OutApp      As Object
    Dim OutMail    As Object
    Dim strBody    As String
    Dim LastRow    As Long
    Dim eMailIDs, i As Long
    Dim varBody
   
    Const StartRow  As Long = 2    '<<< adjust to suit
   
   
    If Not Application.Intersect(Range("I:I"), ActiveSheet.UsedRange) Is Nothing Then
       
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
       
        LastRow = Range("I" & Rows.Count).End(xlUp).Row
       
        eMailIDs = Range("I" & StartRow).Resize(LastRow - StartRow + 1)
       
        For i = 1 To UBound(eMailIDs, 1)
            varBody = Range("a" & StartRow + i - 1).Resize(, 7).Value
            strBody = Join(Application.Transpose(Application.Transpose(varBody)), vbTab)
            On Error Resume Next
            With OutMail
                .To = eMailIDs(i, 1) 'email from corresponding row goes here
                .CC = ""
                .BCC = ""
                .Subject = "Subject"    '<< adjust subject line
                .Body = strBody
                'You can add a file like this
                '.Attachments.Add ("C:\")
                .Display
                'or use .Send
'                .Send
            End With
            On Error GoTo 0
        Next
    End If
   
    Set OutMail = Nothing
    Set OutApp = Nothing
   
End Sub

---

Quote:

---

Originally Posted by Admin

Hi Baris,

Welcome to ExcelFox !!!

Try this

---

Hi,
Thanks for the code but it sends email to the last person in the list only. (the last row I mean)
If there are twenty individuals, how can we change it?

Thanks in advance
Baris

Hi,

No, it'll send emails to everyone in Col I mentioned, since we are looping Col I for email address.

Only the last receipent received an email message with that code.

sorry for questions. Is this ubound formula correct?

For i = 1 To UBound(eMailIDs, 1)

Debug error pops out.

The error comes because the there is no data in Col I. Check whether your email ids are in Col I ?

Could you please attach a sample workbook ? Feel free to put dummy data in the attachment.

Hi,
It's done..

Thanks
Baris

Hi,

Your StartRow should be 1.

Quote:

---

Originally Posted by Admin

Hi,

Your StartRow should be 1.

---

Hi,
Outlook seems like as if it is going to send email messages to all individuals but only one email message window comes out. When I click to send button, i see only one message in "sent items" box. VB code sends email to last person only.


fyi
Baris