Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Create mailing list address array (Excel 2k VBA)

    I want to create an easily-manageable mailing list on an Excel sheet that can be read into an array variable and passed to a sendmail command: ActiveWorkbook.SendMail Recipients:=Array(address1,address2,etc.). The addresses would come from a simple table:

    <table border=1><td></td><td>A</td><td>1</td><td>DistList</td><td>2</td><td>rasleyd@schwebels.com</td><td>3</td><td>rasleyd@schwebels.com</td></table>

    I plan to use a little macro to refresh the range, so as addresses are added or removed the range will auto-correct:

    <img src=/w3timages/blueline.gif width=33% height=2>
    Sub macSetAddressRange()
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
    Range("A1").Select
    End Sub
    <img src=/w3timages/blueline.gif width=33% height=2>

    The next part just doesn't work. The code reads the range and can string together the addresses, but the sendmail command refuses to accept the variable for its recipients parameter.

    <img src=/w3timages/blueline.gif width=33% height=2>
    Sub macSendMail()
    Module1.macSetAddressRange
    Set Distlist = Sheets("Sheet1").Range("DistList")
    For Each Address In Distlist
    DistArray = DistArray & Chr(34) & "," & Chr(34) & Address
    Next
    DistArray = Right(DistArray, Len(DistArray) - 3)
    ActiveWorkbook.SendMail Recipients:=Array(DistArray)
    End Sub
    <img src=/w3timages/blueline.gif width=33% height=2>

    Can this concept work?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Create mailing list address array (Excel 2k VBA)

    Try this:

    <pre> Dim varRecipients
    varRecipients = Sheets("Sheet1").Range("DistList")
    ActiveWorkbook.SendMail Recipients:=varRecipients
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create mailing list address array (Excel 2k VBA)

    You can use a dynamic named range instead of using code to update a fixed named range. See (for example) Named Ranges on Chip Pearson's site.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create mailing list address array (Excel 2k VBA)

    Such a simple solution! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Posting Permissions

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