Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mass Email (Outlook 2000)

    Is there a way, does someone have sample code, to use a field in an Excel spread sheet containing email addresses as the "TO" for a form letter sent from Outlook?

    I would like to create the mail message in Outlook and then have the "TO" field populated based on the email filed in a spreadsheet.


    Thanks for any help.
    Richard

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

    Re: Mass Email (Outlook 2000)

    You could use the Excel workbook as data source for a merge to e-mail from Microsoft Word.

    I don't think there is a way of doing this directly from Outlook, except by using complicated VBA code.

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

    Re: Mass Email (Outlook 2000)

    <P ID="edit" class=small>(Edited by JohnBF on 07-Jul-05 15:40. )</P>It was very of sneaky of Hans to answer that way! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Here's some code that creates a new message and populates it using the email names in a range named "to_list" in the first worksheet of a workbook called "emailtest.xls" in a folder with this path: "C:documents & settingsmy documentsexcel". It should get you started.

    In the VBE, you must Set a Reference to 'Microsoft Excel nn.n Object library'.

    Sub MessageAddressesFromExcel()
    Dim xlApp As Excel.Application
    Dim wkb As Workbook
    Dim rng As Range
    Dim msg As MailItem
    Dim intC As Integer

    Set xlApp = CreateObject("Excel.Application")
    Set msg = Outlook.CreateItem(olMailItem)
    Set wkb = xlApp.Workbooks.Open(_
    FileName:="C:documents & settingsmy documentsexcelemailtest.xls", _
    addtomru:=False)
    Set rng = wkb.Worksheets(1).Range("to_list")
    For intC = 1 To rng.Cells.Count
    msg.Recipients.Add rng.Cells(intC).Value
    Next intC
    Set rng = Nothing
    wkb.Close Savechanges:=False
    Set wkb = Nothing
    Set xlApp = Nothing
    With msg
    .Recipients.ResolveAll
    .Save
    .Display
    End With
    Set msg = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Email (Outlook 2000)

    Thanks, that is exactly what I was looking for.
    Richard

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Email (Outlook 2000)

    The code you gave me works very well.

    In Access I can get the path to the current database with the Application.CurrentDB.Name command.
    Is there a way in Outlook to do this. I am looking for a way to make the path to the spreadsheet felxable.
    For testing I have hard coded the location to the file on my machine, but it will vary for other users.

    Or is there a way to create a user form where they would be able to browse for the file, then click a command button to kick off the macro?
    I know how to create this type thing in Access, but have not used forms of this type in any of the otehr Office applcations.



    Thanks for all lyour help.
    Richard

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

    Re: Mass Email (Outlook 2000)

    Outlook file open dialogs are restricted to Outlook files, and there's no Methods corresponding to the Dialog().Show or GetOpenFilename and related Methods in other MS Apps. Therefore I use a hacked API call, in this case the GetFileNameFromDialog Function in the attachment. (The GetOneFile Sub is just a demo for using it.)

    If someone has a better way, I'm also interested!
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mass Email (Outlook 2000)

    You are a genius. That worked like a dream.


    THANK YOU
    Richard

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

    Re: Mass Email (Outlook 2000)

    I'm not a genius, I'm more of a plagiarist. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Here is the source of the code I posted, except this MVP page shows the multiselect version (for selecting multiple files), and so is excessive if you are requiring that the user select only one file, but perfect if you ever need a function for selecting multiple files.
    -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
  •