Tech Support Guy banner
Status
Not open for further replies.

Emailing multiple recipients from Excel Based off Cell Value

17K views 7 replies 2 participants last post by  Pillowtalk 
#1 ·
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
 
#2 ·
"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:

123@abc.com
xyz@abc.com
xyz@xyz.com
123@abc.com

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)
 
#4 ·
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: Alex@abc.com
B3: Peter@xyz.com
B4: Serene@xyz.com
B5: Alex@abc.com

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.
 
#5 ·
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?
 
#6 ·
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
 
#8 ·
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: Alex@abc.com
B3: Peter@xyz.com
B4: Serene@xyz.com
B5: Alex@abc.com

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.
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!
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top