Put Excel list of addresses into Outlook?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
I'm stumped. I have email addresses in an Excel file that I need to send an email with Outlook or Outlook Express. When I try to import the file, I get the error message that the Excel file has no named ranges. I have gone into the Excel file and highlighted the addresses, but to no avail. What am I doing wrong?
 
Joined
Jul 1, 2005
Messages
8,546
Highlight the addresses again. Press CTRL+F3. Type a suitable name in the "Names in workbook" box (e.g. Addies), then press Enter. Then try your import again.

Rgds,
Andy
 
Joined
Jul 25, 2004
Messages
5,458
FtWrth, is this an exported list of addresses from outlook? If so, can you re-export it to a file to try again? Which version of office are you running?

If this is not the case, can you post a sample file of what your data looks like? If we can't import it into Outlook, we can do so programmatically from Excel. But it's always best to use the native functions if we can.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
FtWrth, why not send the email va Outlook from within Excel using the Address list in Excel?
 

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
Thanks guys. I tried Bomb 1's solution and got the same error message. To firefytr: the list is an original excel. Another error message I have gotten while trying to do this is that the file can't be read by the translator and may be the wrong type or corrupt. I have no reason to believe it is corrupted. I'm using Office 2000 with Win XP.
 
Joined
Jul 25, 2004
Messages
5,458
My question is: how did you get this file? Did Outlook create it for you, or did somebody create it?

If somebody created it, you'll need to post much more detailed information on your file and it's data structure, but it would probably be easier to post/upload a sample file.
 

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
Thanks again. Ok, here's the story. My community relations manager has an Excel file of email addresses that need to be sent an email notification of an in-store event that they will take part in. Her store computer hard drive fried, so I volunteered to do the emails at home. I saved the copy of addresses to my computer and have been trying to import it to my Outlook so I could send the email. My system (as stated) includes Office 2000. I'm not sure, but I believe the CRM's computer has the same. There are over 600 teachers on this list, so I sure don't want to have to do them one at a time!
 
Joined
Jul 25, 2004
Messages
5,458
Can you post a sample file? Or at least explain to us where the addresses are stored?

Here is an example of sending an email from Excel via Outlook's Object Model.
 

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
New information... I opened a new Excel file and typed in a dummy address. It entered as an URL (blue, underlined). I then copy/pasted addresses from the original file to that one and they are NOT imported as URLs. Does that mean they have to be retyped?
 

OBP

Joined
Mar 8, 2005
Messages
19,896
firefytr, How about a piece of VBA to loop through the Excel list that creates the email and sends it for each record? I have done this in Access but not in Excel.
 

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
Whew! I looked at the Outlook's Object Model. That's Greek to me. I don't even know what OBP is asking for: "What is the timescale?" I'm WAY over my head with this project. Again....thanks a ton, but I'll save a lot of time by just typing them in one at a time. You guys are great, though.
 
Joined
Jul 25, 2004
Messages
5,458
OBP, that is what I intended to do, but alas, there is no data to work with, no data structure. I'd be flying blind. I've done this quite a few times before, and it works well.

Oh heck with it, I'll attach a sample file.

Here is the code ..

Code:
Option Explicit

Sub EmailList()

    Dim OL As Object, OLMsg As Object
    Dim rngLoop As Range, c As Range
    
    If MsgBox("Are you sure you want to send these emails?", _
        vbYesNo, "Send Email to All?") = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets("Sheet1")
        Set rngLoop = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    Set OL = CreateObject("Outlook.Application")
    
    For Each c In rngLoop
        Set OLMsg = OL.CreateItem(0)
        With OLMsg
            .To = c.Value
            .Cc = c.Offset(0, 1).Value
            .Bcc = c.Offset(0, 2).Value
            .Subject = c.Offset(0, 3).Value
            .Body = c.Offset(0, 4).Value
            .Send 'change to .Display to see them all
        End With
    Next c
    
    Application.ScreenUpdating = True
    
    Set OL = Nothing
    Set OLMsg = Nothing
    
End Sub
Note that it has ".Send" in the code instead of ".Display". This means you will get a message box that comes up once (from Outlook) saying something is trying to access outlook and needs permission. Give it permission, one minute should suffice, but 10 would be best.
 

Attachments

FtWrth

Thread Starter
Joined
Mar 1, 2005
Messages
129
My ignorance is overwhelming..... I took the Excel file originally typed with Excel 97 and copied the column of email addresses to a new Excel 2000 SP-3 spreadsheet. There is only one column of information containing the email addresses. I then attempted to use Outlook 2000 SP-3 to import the addresses to send the emails. It will not let me do so. I noted that the addresses are not encoded as URL's. When I type a new address (any), it is encoded as an URL. If the code you gave me applies, how do I use it? Where to I install the code? Sorry I didn't give sufficient information, but if you think you're flying blind! Wow! I am flying like Helen Keller.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top