send filkes as attachment aut.

M

mariagloria

PLease help me with this. I need to make a macro so I can open 5 excell
files that are store in the same folder and send all of them as attachment in
1 mail automaticaly. I'm sure I there must be a way to do it. Thanks
 
R

Ron de Bruin

Hi mariagloria

You don't have to open the 5 files

Try this one
Copy the code in a normal module in a workbook

Sub Mail_workbooks_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ("C:\Data\test1.xls")
.Attachments.Add ("C:\Data\test2.xls")
.Attachments.Add ("C:\Data\test3.xls")
.Attachments.Add ("C:\Data\test4.xls")
.Attachments.Add ("C:\Data\test5.xls")
.display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

More information on this page
http://www.rondebruin.nl/sendmail.htm
 
M

mariagloria

thank yoou so much for your answer, but sorry I've never made a program in
excell or macro, besides using the wizard,. Can you tell me where do I have
to place the code you sent? Thanks
 
M

mariagloria

sorry but I miss something, I need to send all the context of the folder
something like "z:\transmission\*.*" but it doesn´t work like this, do I
always have to write file by file?
 
R

Ron de Bruin

Try this for the files in C:\Data

Sub Mail_workbooks_Outlook()
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim OutApp As Object
Dim OutMail As Object

SaveDriveDir = CurDir
MyPath = "C:\Data" '<----- Change this path
ChDrive MyPath
ChDir MyPath

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"

FNames = Dir("*.xls")
Do While FNames <> ""
.Attachments.Add MyPath & "\" & FNames
FNames = Dir()
Loop

.display 'or use .Send
End With

Set OutMail = Nothing
Set OutApp = Nothing
ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub
 
Top