Hi folks,
I'm trying to send out individual emails, I got the code working for me, but now need slight alterations, which I gave up trying to implement this weekend...can't figure it out.
My VBA code loops through Column "I" with people's names and creates a list of emails. In email body there's a list of rows for each person to be included in the body of the email, from columns B, C, G, I. Pretty straightforward, however I encounter an issue with the latter. It only takes the first row for each person, i.e. doesn't loop through the list to get all of the rows for one individual recipient.
Here's the data to give you an idea
:
Basically, my code DOES generate individual emails, i.e. one for John Smith and one for Antoine Jones. But for some reason in the email body it only takes the first row for their name and ignores the rest. I.e. it doesn't loop through all of the rows for all of the names. Any idea why? And the second issue, how do I make it start at row 3? It currently thinks Row2 with headings is the start of my data....here's my code:
Appreciate your help!
I'm trying to send out individual emails, I got the code working for me, but now need slight alterations, which I gave up trying to implement this weekend...can't figure it out.
My VBA code loops through Column "I" with people's names and creates a list of emails. In email body there's a list of rows for each person to be included in the body of the email, from columns B, C, G, I. Pretty straightforward, however I encounter an issue with the latter. It only takes the first row for each person, i.e. doesn't loop through the list to get all of the rows for one individual recipient.
Here's the data to give you an idea
Basically, my code DOES generate individual emails, i.e. one for John Smith and one for Antoine Jones. But for some reason in the email body it only takes the first row for their name and ignores the rest. I.e. it doesn't loop through all of the rows for all of the names. Any idea why? And the second issue, how do I make it start at row 3? It currently thinks Row2 with headings is the start of my data....here's my code:
Code:
Sub SendEmail3()
Dim OutlookApp
Dim MItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim PriorRecipients As String
Dim Msg As String
Dim Projects As String
'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")
'Loop through each person and send email if they haven't already received one.
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value <> "" Then
'first build email address
EmailAddr = LCase$(Replace(cell.Value, " ", ".")) & "@company.com"
Projects = vbCrLf & "Document: " & Cells(cell.Row, "B").Value & "; " & Cells(cell.Row, "C").Value & "; " & "Rev " & Cells(cell.Row, "G").Value & "; " & Cells(cell.Row, "I").Value
'then check if it is in Recipient List build, if not, add it, otherwise ignore
'If the recipient has already received an email, skip
If InStr(1, PriorRecipients, EmailAddr) <> 0 Then
GoTo NextRecipient
End If
PriorRecipients = PriorRecipients & ";" & EmailAddr
'Create Mail Item and view before sending
Set MItem = OutlookApp.CreateItem(olMailItem)
Msg = "You have the following outstanding documents to be reviewed." & vbCrLf & "Full list of documents to be reviewed below:" & vbCrLf & vbCrLf & Projects
Subj = "Outstanding Documents to be Reviewed"
With MItem
.To = EmailAddr 'single email address
.Subject = Subj
.Body = Msg
.display
'This will show for EVERY person. Skip this and change to .send to just send without showing the email.
End With
End If
NextRecipient:
Next
End SubAppreciate your help!
= ActiveCell.Value
