Email files automatically to a list of people, by looking for the email address and attachement name in an Excel list

Posted on 2004-11-16
Last Modified: 2010-04-08
Hi Experts!

Don't know if this is the right forum to ask this question. I am running Outlook 2002 and I am looking for a solution to automatically email a series of files to a group of people. There are about 100 files I need to send out each week and it would be great if I could do this automatically with a script.

I have an Excel file, called Master.xls. The file contains the following data, starting on row 2

A                        B                      C
Column A contains the names of the files stored in C:\Data; i.e CHI_John_Doe.xls, ATL_Jane_Smith.xls....
They are all Excel files.

What I'd like to be able to do is run a script that automatically sends an email to with CHI_John_Doe.xls as the attachement; another email to, containing ATL_Jane_Smith.xls, until all the file names in column A were processed.

Is this possible? I appreciate your help and ideas! I am an enthusiastic user of experts-exchange, although I haven't used the Outlook forum before. I don't have much experience with Outlook scripts and I don't know if what I am looking for is even possible.

Thanks so much!
Question by:amoyes
    LVL 23

    Expert Comment

    Does the data have to be in Excel?  We could probably sort it if it was in a CSV format (which Excel can read), using a command line emailer like mapisend from the res kit, blat, or bmail

    might need to be a little careful with the email, as some mail environments prevent open relay type sending.  Let us know if alternative file formats are ok, and describe your email environment a bit....
    LVL 76

    Expert Comment

    by:David Lee
    Hi, Andreea.

    Yes, it's possible to do what you described.  Frankly, it'd be a lot easier if the list of files and recipients was in a text file instead of an Excel spreadsheet, but it's doable either way.  The email portion would be the same, it's merely a matter of whether the details come from a spreadsheet or text file.  Let me know which way you'd prefer to go and I'll whip up a sample.

    Author Comment

    Hi Dan and BlueDevilFan,

    I apologize for not responding sooner, I was out of town and out of reach.
    I can definitely save the Excel file as text or CSV in order to make this run. It would have to be a dynamic file, there are changes to the list every month.

    I'm not very savvy with Outlook scripts so please, if you could, let me know step by step what I need to do. Like where to put the script, where should the Master list file reside, etc. etc.

    Thank you very much!

    LVL 76

    Accepted Solution


    Here is one possible solution, this one written in VBScript.  It uses Outlook so you'd have to have it on the sending computer.  Since this solution uses the Outlook Object Model it will generate a popup dialog box warning that a program is sending a message on your behalf.  You'll have to click a button to release each message.  If there are quite a few messages to send or if you'd envisioned this running unattended, then that's a problem.  The dialog boxes are generated by Outlook itself as a security precaution to help prevent the spread of viruses by scripts.  There's a utility you can get that'll click the dialog box button for you, but I've only read about it so I don't know how well it works.  If you'd like this to run completely unattended, then Outlook isn't a good solution.  I'd recommend using straight VB instead of VBScript and employing SMTP instead.  To do that though you'll need access to an email server that'll accept an SMTP connection.  

    In this solution I've used a plain text file to hold the names of the recipients and the file to send each one.  Each recipient/item pair goes on a seperate line.  The format of each line is:

    Recipient Address TAB File to Send

    The file to send needs to be a complete file path, for example: C:\MyFile.txt
    The TAB is an actual tab character, not the word TAB.

    To use the code you'll need to copy it and save it into a file.  The filename is up to you, so long as it ends with .vbs.  

    ----- BEGIN CODE
    Dim objOutlook, _
        objNS, _
        objMsg, _
        objFSO, _
        objTS, _
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNS = objOutlook.GetNamespace("MAPI")
    'Change "Outlook" to the name of your Outlook profile
    objNS.Logon "Outlook"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'change ".\DistList.txt" to the path/filename of the file that'll hold the distribution information
    Set objTS = objFSO.OpenTextFile(".\DistList.Txt")
    While Not objTS.AtEndOfStream
        arrItem = Split(objTS.ReadLine, vbTab)
        Set objMsg = objOutlook.CreateItem(olMailItem)
        With objMsg
            .To = arrItem(0)
            .Attachments.Add arrItem(1)
            'Change the line below to the text you want to appear in the body of each message
            .Body = "Please see the attached file."
            'Change the line below to the text you want to appear as the subject of each message
            .Subject = "File Distribution Facility"
        End With
    Set objTS = Nothing
    Set objFSO = Nothing
    Set objMsg = Nothing
    Set objNS = Nothing
    Set objOutlook = Nothing
    ----- END CODE

    Author Comment

    Hi BlueDevil

    I tried running the code and it keeps stopping at line 1 without executing. I'm not sure if I didn't change things right or what the problem may be. I pasted the code in a text file, made the two changes and saved with vbs extension.

    Here are the lines I changed in the code you provided:
    objNS.Logon "napcontrols" (do I need the domain name here as well?)
    Set objTS = objFSO.OpenTextFile("C:\\Documents and Settings\moyea0\My Documents\Andreea\Sarat\Document.Txt")

    Any ideas what I may be doing wrong? This is my first VB project using Outlook, I am definitely very "green".

    I pasted below some code I found on Microsoft's site. This code works, it sends emails with attachements, but the loop is not what I wanted, I have to put the addresses and the file names in the code which wouldn't work because there are so many. I thought maybe we could combine the two if you you can help me to set up that loop.

    Thank you very much,

    The two example macros below demonstrates how you can send information to Outlook
    (e.g. sending an e-mail message) and how you can retrieve information from Outlook
    (e.g. retrieving a list av all messages in the Inbox).

    Note! Read and edit the example code before you try to execute it in your own project!

    ' requires a reference to the Microsoft Outlook 8.0 Object Library
    Sub SendAnEmailWithOutlook()
    ' creates and sends a new e-mail message with Outlook
    Dim OLF As Outlook.MAPIFolder, olMailItem As Outlook.MailItem
    Dim ToContact As Outlook.Recipient
        Set OLF = GetObject("", _
        Set olMailItem = OLF.Items.Add ' creates a new e-mail message
        With olMailItem
            .Subject = "Subject for the new e-mail message" ' message subject
            Set ToContact = .Recipients.Add("") ' add a recipient
            Set ToContact = .Recipients.Add("") ' add a recipient
            ToContact.Type = olCC ' set latest recipient as CC
            Set ToContact = .Recipients.Add("") ' add a recipient
            ToContact.Type = olBCC ' set latest recipient as BCC
            .Body = "This is the message text" & Chr(13)
            ' the message text with a line break
            .Attachments.Add "C:\FolderName\Filename.txt", olByValue, , _
                "Attachment" ' insert attachment
    '        .Attachments.Add "C:\FolderName\Filename.txt", olByReference, , _
                 "Shortcut to Attachment" ' insert shortcut
    '        .Attachments.Add "C:\FolderName\Filename.txt", olEmbeddedItem, , _
                 "Embedded Attachment" ' embedded attachment
    '        .Attachments.Add "C:\FolderName\Filename.txt", olOLE, , _
                 "OLE Attachment" ' OLE attachment
            .OriginatorDeliveryReportRequested = True ' delivery confirmation
            .ReadReceiptRequested = True ' read confirmation
            '.Save ' saves the message for later editing
            .Send ' sends the e-mail message (puts it in the Outbox)
        End With
        Set ToContact = Nothing
        Set olMailItem = Nothing
        Set OLF = Nothing
    End Sub

    Sub ListAllItemsInInbox()
    Dim OLF As Outlook.MAPIFolder, CurrUser As String
    Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
        Application.ScreenUpdating = False
        Workbooks.Add ' create a new workbook
        ' add headings
        Cells(1, 1).Formula = "Subject"
        Cells(1, 2).Formula = "Recieved"
        Cells(1, 3).Formula = "Attachments"
        Cells(1, 4).Formula = "Read"
        With Range("A1:D1").Font
            .Bold = True
            .Size = 14
        End With
        Application.Calculation = xlCalculationManual
        Set OLF = GetObject("", _
        EmailItemCount = OLF.Items.Count
        i = 0: EmailCount = 0
        ' read e-mail information
        While i < EmailItemCount
            i = i + 1
            If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & _
                Format(i / EmailItemCount, "0%") & "..."
            With OLF.Items(i)
                EmailCount = EmailCount + 1
                Cells(EmailCount + 1, 1).Formula = .Subject
                Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, " hh:mm")
                Cells(EmailCount + 1, 3).Formula = .Attachments.Count
                Cells(EmailCount + 1, 4).Formula = Not .UnRead
            End With
        Application.Calculation = xlCalculationAutomatic
        Set OLF = Nothing
        ActiveWindow.FreezePanes = True
        ActiveWorkbook.Saved = True
        Application.StatusBar = False
    End Sub       

    LVL 76

    Expert Comment

    by:David Lee
    Hi, Andreea.

    I tested the script before posting it so I know it works.  When you copied the script and pasted it into a file did you perchance include the ----- BEGIN CODE and/or ----- END CODE lines?  If so, then that'd be a problem.  I included those lines in my post merely to set the code off from the rest of my post.  They should not be in the script file.  If it's not that, then what is on line 1 of your file?  What's the exact wording of the error message, assuming there is one?  Also, how are you running the script?  The correct way is to either double-click the .vbs file from Windows Explorer or to drop to a command prompt and type the command

        Cscript Filename.vbs


        Wscript Filename.vbs

    The code you found at Microsoft's site is straight VB not VBScript.  I don't know if you have VB or not.  If you do, then we'd require fewer changes to make it work than if we need to change it to VBScript.  We can modify it to VBScript if needed though.  But the final result wouldn't be any different functionally from the code I already posted.  Before we jump to making changes to something else I'd prefer to figure out why the code I posted isn't working for you.  Otherwise, we could spend time changing the Microsoft code only to find that it doesn't work either.

    Concerning the two lines that you made changes to:

        objNS.Logon "napcontrols" (do I need the domain name here as well?)

    No, you don't need the domain name.  Is napcontrols a username or the name of the Outlook profile you use?  If it's a username, then that's not what we need.  We need the name of your Outlook profile.  If you don't know what it is, then let me know and I'll describe how to find that out.

        Set objTS = objFSO.OpenTextFile("C:\\Documents and Settings\moyea0\My Documents\Andreea\Sarat\Document.Txt")

    I doubt that it's a big problem, but you don't need the double \\ after C:.  A single \ is all you need.

    -- BlueDevilFan

    Author Comment

    Blue Devil,

    Thanks for your help and I apologize I took so long to respond. It took some testing and digging out for this project.


    Author Comment

    oops, I forgot to say that we've got it working!!! Many thanks.
    LVL 76

    Expert Comment

    by:David Lee
    No problem, Andreea.  Glad you got it working.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    My experience with Windows 10 over a one year period and suggestions for smooth operation
    Outlook Free & Paid Tools
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now