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
Medium Priority
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
CHI_John_Doe                           John_Doe@domain.com
ATL_Jane_Smith                        Jane_Smith@domain.com
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 John_Doe@domain.com with CHI_John_Doe.xls as the attachement; another email to   Jane_Smith@domain.com, 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
  • 4
  • 4
LVL 23

Expert Comment

by:Danny Child
ID: 12599413
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
ID: 12621598
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

ID: 12625211
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!

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 76

Accepted Solution

David Lee earned 1200 total points
ID: 12630150

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.  

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

ID: 12658215
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("name@domain.com") ' add a recipient
        Set ToContact = .Recipients.Add("name@company.com") ' add a recipient
        ToContact.Type = olCC ' set latest recipient as CC
        Set ToContact = .Recipients.Add("name@org.net") ' 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, "dd.mm.yyyy 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
ID: 12661314
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

ID: 12778924
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

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

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

807 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