how to send each row by email

**BARIS** · 08-23-2012, 04:04 PM

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** · 08-23-2012, 04:34 PM

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

**BARIS** · 08-23-2012, 05:05 PM

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

**RockPoint** · 09-12-2012, 10:56 PM

Hi, this is my first time using excelfox and I'm fairly new to VBA

I would like to add a modified version of this code if you can help me out. I have already written code in an outlook macro that scrapes off excel attachments from emails in a specific folder, savesthe attachments to a folder on my computer, opens the saved attachments in excel, and filters the excel spreadsheet.

Now, I need to map the person's name in column C to specific email adresses and send a message to them if column N has a value less than 5.

Your help would be much appreciated!

**Admin** · 08-23-2012, 06:05 PM

Hi,

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

**BARIS** · 08-23-2012, 06:43 PM

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

**BARIS** · 08-24-2012, 04:58 AM

sorry for questions. Is this ubound formula correct?

For i = 1 To UBound(eMailIDs, 1)

Debug error pops out.

**Admin** · 08-24-2012, 08:02 AM

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.