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(v arBody)), 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

[Admin]

Hi

try this one. Also put this code in a standard module, not in class module.


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 = 1 '<<< adjust to suit


If Not Application.Intersect(Range("I:I"), ActiveSheet.UsedRange) Is Nothing Then

LastRow = Range("I" & Rows.Count).End(xlUp).Row

eMailIDs = Range("I" & StartRow).Resize(LastRow - StartRow + 1)

For i = 1 To UBound(eMailIDs, 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

varBody = Range("a" & StartRow + i - 1).Resize(, 7).Value
strBody = Join(Application.Transpose(Application.Transpose(v arBody)), vbTab)
On Error Resume Next
With OutMail
.To = eMailIDs(i, 1) 'email from corresponding row goes here
.CC = ""
.BCC = ""
.Subject = "Expired account notification" '<< adjust subject line
.Body = strBody
'You can add a file like this
'.Attachments.Add ("C:\")
' .Display
.Send
Application.Wait Now + TimeSerial(0, 0, 3)
Set OutMail = Nothing
Set OutApp = Nothing
End With
On Error GoTo 0
Next
End If

End Sub

[BARIS]

It works flawless!
Thank you so much!

Baris

[Admin]

Hi

Thanks for the feedback. :cheers:

[BARIS]

Hi Again,
How can I add cell C(i) to subject line into each email?
What I need to print to subject field for the first line is, for example, "customer11, expired account" etc..
Here is the script and the excel file I run in my pc:



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 = 1 '<<< adjust to suit


If Not Application.Intersect(Range("I:I"), ActiveSheet.UsedRange) Is Nothing Then

LastRow = Range("I" & Rows.Count).End(xlUp).Row

eMailIDs = Range("I" & StartRow).Resize(LastRow - StartRow + 1)

For i = 1 To UBound(eMailIDs, 1)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

varBody = Range("a" & StartRow + i - 1).Resize(, 7).Value
strBody = Join(Application.Transpose(Application.Transpose(v arBody)), vbTab)
On Error Resume Next
With OutMail
.To = eMailIDs(i, 1) 'email from corresponding row goes here
.CC = ""
.BCC = ""
.Subject = "Expired account notification" '<< adjust subject line
.Body = strBody
'You can add a file like this
'.Attachments.Add ("C:\")
' .Display
.Send
Application.Wait Now + TimeSerial(0, 0, 3)
Set OutMail = Nothing
Set OutApp = Nothing
End With
On Error GoTo 0
Next
End If

End Sub

[Excel Fox]

.Subject = varBody(1, 3) & ", Expired account notification"

[Admin]

HI

like


.Subject = Range("C" & i).Value & ", Expired account notification"

[BARIS]

Thanks to both of you Gentlemen.
I have checked the second one only and works smoothly.



.Subject = varBody(1, 3) & ", Expired account notification"


HI

like


.Subject = Range("C" & i).Value & ", Expired account notification"

Thanks
Baris

[RockPoint]

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!

[Excel Fox]

So in your attachment, you can check column N like this


If Range("N" & lngRow).Value < 5 Then
'your code to send the message
'in the To part, use Range("C" & lngRow).Value
Else
'Do Nothing
End if

You'll have to make the adjustments..... if it's not clear, post back with a sample of the excel saved attachments

[BARIS]

Dear Excel fox and Admin,
Last evening I sent 120 email messages with just one click.
Thanks for your help

Now, I have another question. If I should open a new thread, let me know.
I would like to reply my email messages automatically.
When I receive an email which includes "information" phrase in subject line shall be replied with the same excel file which I uploaded earlier to this board. In addition to replying every single email, it will print receipent's email address into excel file to right side cell of related line.

Thanks in advance
Baris

[RockPoint]

Thanks for the quick response. I've attached my file. Now what I need to do is use the name in column C in the "import" sheet to send an email to the corresponding email in column I in "sheet1". I only want to send an email to the people with column N of "import" < 5. The email should contain a message that says, "You have <value of column N> shares outstanding>.

I really appreciate the help.

[BARIS]

Thanks for the quick response. I've attached my file. Now what I need to do is use the name in column C in the "import" sheet to send an email to the corresponding email in column I in "sheet1". I only want to send an email to the people with column N of "import" < 5. The email should contain a message that says, "You have <value of column N> shares outstanding>.

I really appreciate the help.

Hi Rockpoint,
My file is similar to yours. Different sheets talk with others and then I organize them with Vlookup function. At last I put the code which posted by admin in below link. Then it works.
My advise is to edit your sheet structure in a different sheet and automatically move them to sheet 1, then run VBA. Mine works good with that way. In my understanding, we should learn the menthality of VBA code, then we can customize the code...

Regards
Baris

http://www.excelfox.com/forum/f2/how-send-each-row-email-530/index2.html#post2190

[RockPoint]

Thanks for the help. How can I format the body of the email so that it can use specific cell values in the message?

For example, I want it to say

"Good Morning " (Cell B1)","
SKip a space <<<(Also, how do I code this?)
"You have a positions of "(cell N1)" shares."
Skip a Space
"Thank you"