Emailing multiple recipients from Excel Based off Cell Value

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,
Leon

PS.
Using Excel/Outlook 2010

 

"those with the "notification" field marked as yes": which field is that?

First things first. Assuming you have headers in row 1, then these dummy entries in B2:B5:

[email protected]
[email protected]
[email protected]
[email protected]

followed by running this code:

Sub test()
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B2:B" & LastRow)
If WorksheetFunction.CountIf(Range("B2:B" & Cell.Row), Cell) = 1 Then
MsgBox Cell, , "Address from B" & Cell.Row
End If
Next Cell
End Sub

will give you "prompts" except for the last "address" (in B5) because it already exists further up the list. Do you follow so far?

"I ... have no experience with Excel vba coding."

Do you know how to test the above code sample for yourself?

(welcome to the board)

 

first of all, thanks so much for replying.

yes, i followed and tested your code.

could u kindly guide me the next step?

 

For example,

Column A is the Name field, which stores the name of the client.
A2: Alex
A3: Peter
A4: Serene
A5: Alex

Column B is the Email field, which stores the respective email of the client.
B2: [email protected]
B3: [email protected]
B4: [email protected]
B5: [email protected]

Column C and D onwards will store the Item price and purchase.
C2 and D2 will store the Item price and purchase for Client B2, etc.

Column E is the notification field, which stores yes or no. If yes, it will send an email to the respective client of that row with his/her Item price and purchase. If no, it will just ignore that row.

 

SO, moving on, adding the extra "Yes/No" condition, if I make E2:E5:

Yes
Yes
No
Yes

and then expand the code to:

Sub test()
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B2:B" & LastRow)
If WorksheetFunction.CountIf(Range("B2:B" & Cell.Row), Cell) = 1 Then
If Cells(Cell.Row, 5) = "Yes" Then
MsgBox Cell, , "Address from B" & Cell.Row
End If
End If
Next Cell
End Sub

I only get prompted for "records" 1 and 2, yes?

 

Once you've got that, you just need to incorporate and tailor Ron's "Mail a small message" code. Something like:

Sub test()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

LastRow = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B2:B" & LastRow)
If WorksheetFunction.CountIf(Range("B2:B" & Cell.Row), Cell) = 1 Then
If Cells(Cell.Row, 5) = "Yes" Then

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

strbody = "Hi there" & vbNewLine & vbNewLine & _
Cells(Cell.Row, 3) & vbNewLine & _
Cells(Cell.Row, 4)

On Error Resume Next
With OutMail
.To = Cells(Cell.Row, 2)
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End If
End If
Next Cell

End Sub

 

I tested the code but i'm having a problem.

For example:
E2: Yes
E3: Yes
E4: No
E5: Yes

since E2 and E5 are both Yes and they are for the same person, can the code be modify so that it's send the records in both Row 2 and 5 within a same email?

 

using this same example, is it possible to create a workbook/worksheet for each individual person with only his data in his worksheet? and email the individual workbook/worksheet to the respective person?

example: Alex will have his own workbook/worksheet created from the main excel file with all his purchase "records" (notification "yes" records) emailed to him as an attachment?

Thanks!