how to send each row by email

[BARIS]

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

[Admin]

Hi Baris,

Welcome to ExcelFox !!!

Try this

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

[BARIS]

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

[Admin]

Hi,

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

[BARIS]

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

[BARIS]

sorry for questions. Is this ubound formula correct?

For i = 1 To UBound(eMailIDs, 1)

Debug error pops out.

[Admin]

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.

[BARIS]

Hi,
It's done..

Thanks
Baris

[Admin]

Hi,

Your StartRow should be 1.

[BARIS]

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