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

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!
Andreea
amoyesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Danny ChildIT ManagerCommented:
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

http://www.beyondlogic.org/solutions/cmdlinemail/cmdlinemail.htm

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....
0
David LeeCommented:
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.
0
amoyesAuthor Commented:
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!

Andreea
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David LeeCommented:
Andreea,

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, _
    arrItem
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"
        .Send
    End With
Wend
objTS.Close
Set objTS = Nothing
Set objFSO = Nothing
Set objMsg = Nothing
objNS.Logoff
Set objNS = Nothing
Set objOutlook = Nothing
----- END CODE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amoyesAuthor Commented:
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,
Andreea

 
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("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    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("", _
        "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    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
    Wend
    Application.Calculation = xlCalculationAutomatic
    Set OLF = Nothing
    Columns("A:D").AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Saved = True
    Application.StatusBar = False
End Sub       

0
David LeeCommented:
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

or

    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
0
amoyesAuthor Commented:
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.

Regards,
Andreea
0
amoyesAuthor Commented:
oops, I forgot to say that we've got it working!!! Many thanks.
0
David LeeCommented:
No problem, Andreea.  Glad you got it working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.