1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Emailing multiple recipients from Excel Based off Cell Value

Discussion in 'Business Applications' started by Pillowtalk, Nov 21, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Pillowtalk

    Pillowtalk Thread Starter

    Joined:
    Nov 21, 2011
    Messages:
    5
    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. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "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)
     
  3. Pillowtalk

    Pillowtalk Thread Starter

    Joined:
    Nov 21, 2011
    Messages:
    5
    first of all, thanks so much for replying.

    yes, i followed and tested your code.

    could u kindly guide me the next step?
     
  4. Pillowtalk

    Pillowtalk Thread Starter

    Joined:
    Nov 21, 2011
    Messages:
    5
    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.
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    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
     
  7. Pillowtalk

    Pillowtalk Thread Starter

    Joined:
    Nov 21, 2011
    Messages:
    5
    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?
     
  8. Pillowtalk

    Pillowtalk Thread Starter

    Joined:
    Nov 21, 2011
    Messages:
    5
    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!
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027847

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice