Printable View
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
Hi,Quote:
Originally Posted by Admin
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.
Hi,Quote:
Originally Posted by Admin
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
Hi
try this one. Also put this code in a standard module, not in class module.
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 = 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(varBody)), 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
It works flawless!
Thank you so much!
Baris
Hi
Thanks for the feedback. :cheers:
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:
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 = 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(varBody)), 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
Code:.Subject = varBody(1, 3) & ", Expired account notification"
HI
like
Code:.Subject = Range("C" & i).Value & ", Expired account notification"
Thanks to both of you Gentlemen.
I have checked the second one only and works smoothly.
Quote:
Originally Posted by Excel Fox
ThanksQuote:
Originally Posted by Admin
Baris
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!
So in your attachment, you can check column N like this
You'll have to make the adjustments..... if it's not clear, post back with a sample of the excel saved attachmentsCode: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
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
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,Quote:
Originally Posted by 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....html#post2190
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"