• Excel Email Addresses into Outlook Addressee List (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Outlook and email programs » Excel Email Addresses into Outlook Addressee List (2000)

    Author
    Topic
    #414999

    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?

    Viewing 1 reply thread
    Author
    Replies
    • #924409

      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”

    • #924416

      There are some examples in the Excel Forum, including those in the thread which includes Andrew’s post 38432 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 grin); this can be overcome by using ClickYes, or other, more complex, methods.

    Viewing 1 reply thread
    Reply To: Excel Email Addresses into Outlook Addressee List (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: