Results 1 to 5 of 5

Thread: VBA Code To Email Multiple Recipients From Excel

  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0

    VBA Code To Email Multiple Recipients From Excel

    I am writing a code to send an email from excel using the name in column AR. I have the following code but it will only pull the 1st name it comes to. I need it to pull all of the names. There will be some names that are repeated so I would like for it to pull each name only once if that is possible. Thanks

    Code:
    Dim OutApp As Object
        Dim OutMail As Object
        Dim Recipients As String, c As Range
        Set r = Range("AR2")
        For Each c In r
    
                Recipients = ";" & c.Value
        Next c
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
    Last edited by Excel Fox; 06-10-2013 at 11:08 PM. Reason: Code Tags

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by cdurfey View Post
    I am writing a code to send an email from excel using the name in column AR. I have the following code but it will only pull the 1st name it comes to. I need it to pull all of the names. There will be some names that are repeated so I would like for it to pull each name only once if that is possible. Thanks

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Recipients As String, c As Range
    Set r = Range("AR2")
    For Each c In r

    Recipients = ";" & c.Value
    Next c
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    Try replacing the line of code I highlighted in red above with this...
    Code:
    If InStr(Recipients & ";", ";" & c.Value & ";") = 0 Then Recipients = Recipients & ";" & c.Value
    Note: After the loop has finished, the Recipients variable will start with a semi-colon... you can remove it by placing this line of code after the Next statement...
    Code:
    Recipients = Mid(Recipients, 2)

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    I am still getting just the 1st person in the list.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by cdurfey View Post
    I am still getting just the 1st person in the list.
    Duh, well of course you are getting just the first name... I just re-looked at your code...
    Code:
    Dim OutApp As Object
        Dim OutMail As Object
        Dim Recipients As String, c As Range
        Set r = Range("AR2")
        For Each c In r
    
                Recipients = ";" & c.Value
        Next c
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
    I highlighted the problem in red above... your For..Each loop is iterating through all the cell is range 'r'... there is only one cell in range 'r'. You need to expand the range to include all the cells you want processed. Assuming the your names are in column AR, your code for that Set line should probably look like this...
    Code:
    Set r = Range("AR2:AR" & Cells(Rows.Count, "AR").End(xlUp).Row)
    Note: You will still have to make the change to the line of code inside the For..Next block that I posted earlier in order to get all the names in the list.
    Last edited by Rick Rothstein; 06-10-2013 at 11:30 PM.

  5. #5
    Junior Member
    Join Date
    May 2013
    Posts
    21
    Rep Power
    0
    I must be sleeping. Thanks for the help.

Similar Threads

  1. Replies: 12
    Last Post: 12-10-2019, 09:56 PM
  2. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  3. VBA Code to email using Globals Address Book
    By cdurfey in forum Excel Help
    Replies: 5
    Last Post: 05-28-2013, 10:25 PM
  4. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  5. Replies: 17
    Last Post: 05-22-2013, 11:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •