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.
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?
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
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)
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
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?
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.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!