Hi all,

Can someone point me in the right direction?

I have a list of xls files in a folder which I need to email. Each
file has a cover page that has named ranges which has the details of
who I want to send it to and the subject. I'm hoping to find and I
believe the best way to do it is to have a macro that will loop
through each file, open it and send the email automatically.

This is what I have.

Named ranges:
worksheet name = cover
person1, person2, person3, email1, email2, email3, ccdescription

One thing I should note is that person1 will always be used but
person2 & person3 may not be used and be blank.

I've been looking at this link - http://www.rondebruin.nl/mail/folder2/mail1.htm
and based on this, I'm guessing that it would be something like....

Sub loopworkbooks()
For each workbook in folder
Open workbook
Call Sub Mail_workbook_Outlook
Close workbook and do not save
End Sub


Sub Mail_workbook_Outlook_1()
'Working in 2000-2007
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = activeworkbook.sheetname.email1 & ...email2 & ...email3
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub



Any assistance would be sincerely appreciated.