Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Email Addresses into Outlook Addressee List (2000)

    I have a list of email addresss in 1 column in Excel. How do I get all of these out and into the To: field in Outlook such that I can send the email to each of the people listed in Excel?

    Possible?

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

    Re: Excel Email Addresses into Outlook Addressee List (2000)

    You can use the SendMail method of the workbookm and set the Recipients argument to the range of cells containing the e-mail addresses:

    ActiveWorkbook.SendMail Recipients:=Worksheets("Addresses").Range("A1:A10" ), Subject:="Test e-mail"

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Excel Email Addresses into Outlook Addressee List (2000)

    There are some examples in the Excel Forum, including those in the thread which includes Andrew's <post#=38432>post 38432</post#> which should be your starting point. Make sure you set the necessary references in the VBE.

    You will need to write a range loop to gather all the addresses from your Excel range, something like this untested air code:

    Dim itmMessage as MailItem
    Dim strToNames as String
    Dim rngCell as range

    For Each rngCell in ActiveSheet.Range("A1:A40").Cells
    strToNames = strToNames & rngCell.Value & Chr(59)
    next rngCell

    And then use

    With itmMessage
    .To = Chr(34) & strToNames & Chr(34)

    Your code will trigger the Object Model Guard warnings (because Outlook thinks you're a spammer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>); this can be overcome by using ClickYes, or other, more complex, methods.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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