Hi Ya'll,

I want to automate a daily task which gets some data from a SAP extract,
processes it, then mails a new file to a number of recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file created to a
predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

Hi
see: http://www.rondebruin.nl/sendmail.htm

-----Original Message-----
Hi Ya'll,

I want to automate a daily task which gets some data from
a SAP extract,
processes it, then mails a new file to a number of
recipients.
I have done all the data stuff to the point where the new
files are
successfully created. How can I then (with VBA) e-mail
the file created to a
predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail
client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.


.

Try looking up www.rondebruin.nl


"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP extract,
processes it, then mails a new file to a number of recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file created to
a
predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

John wrote:
Try looking up www.rondebruin.nl


"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP
extract, processes it, then mails a new file to a number of
recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file
created to a predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

thanks fellaz

VBA can easily send mail via Outlook. Here is some example code

Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True

Set objMailItem = objOutlook.CreateItem(0)
Set objRecipient = _
objMailItem.Recipients.Add("bob.phillips@somewhere .com")
objRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
objMailItem.Subject = "The extract has finished."
objMailItem.Body = "This is an automatic email notification"
objMailItem.Attachments.Add (Filename) 'you only need this if
'you are sending attachments?
objMailItem.Send

If you want to include an attachment as shown, it has to be a file not the
activeworkbook, so if you want to send the activeworkbbok, save it first,
and then send it as that file. You can use 'ActiveWorkbook.FullName' to
access it without using hard-coded values.

You just will need to add the code to trigger the mailing.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP extract,
processes it, then mails a new file to a number of recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file created to
a
predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

Bob Phillips wrote:
VBA can easily send mail via Outlook. Here is some example code

Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True

Set objMailItem = objOutlook.CreateItem(0)
Set objRecipient = _
objMailItem.Recipients.Add("bob.phillips@somewhere .com")
objRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
objMailItem.Subject = "The extract has finished."
objMailItem.Body = "This is an automatic email notification"
objMailItem.Attachments.Add (Filename) 'you only need
this if 'you are sending attachments?
objMailItem.Send

If you want to include an attachment as shown, it has to be a file
not the activeworkbook, so if you want to send the activeworkbbok,
save it first, and then send it as that file. You can use
'ActiveWorkbook.FullName' to access it without using hard-coded
values.

You just will need to add the code to trigger the mailing.



"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP
extract, processes it, then mails a new file to a number of
recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file
created to a predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

Thanks Bob, exactly what I was looking for

cheers

Damo

Bob I implemented this code in my workbook and it works like a charm.

The only change I made was to dynamically select the e-mail addresses from a
range in the workbook so that the administrator can type the addresses on
one of the worksheets rather that edit VBA.

thanks for your assistance.

Damien



Bob Phillips wrote:
VBA can easily send mail via Outlook. Here is some example code

Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True

Set objMailItem = objOutlook.CreateItem(0)
Set objRecipient = _
objMailItem.Recipients.Add("bob.phillips@somewhere .com")
objRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
objMailItem.Subject = "The extract has finished."
objMailItem.Body = "This is an automatic email notification"
objMailItem.Attachments.Add (Filename) 'you only need
this if 'you are sending attachments?
objMailItem.Send

If you want to include an attachment as shown, it has to be a file
not the activeworkbook, so if you want to send the activeworkbbok,
save it first, and then send it as that file. You can use
'ActiveWorkbook.FullName' to access it without using hard-coded
values.

You just will need to add the code to trigger the mailing.



"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP
extract, processes it, then mails a new file to a number of
recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file
created to a predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.

Damien,

Good move, that is the way I do it when I use it.

I also use DLs a lot with it.

Glad to be of help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Damien McBain" wrote in message
...
Bob I implemented this code in my workbook and it works like a charm.

The only change I made was to dynamically select the e-mail addresses from
a
range in the workbook so that the administrator can type the addresses on
one of the worksheets rather that edit VBA.

thanks for your assistance.

Damien



Bob Phillips wrote:
VBA can easily send mail via Outlook. Here is some example code

Dim objOutlook As Object
Dim objMailItem As Object
Dim objRecipient As Object
Dim objNameSpace As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
objNameSpace.Logon , , True

Set objMailItem = objOutlook.CreateItem(0)
Set objRecipient = _
objMailItem.Recipients.Add("bob.phillips@somewhere .com")
objRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
objMailItem.Subject = "The extract has finished."
objMailItem.Body = "This is an automatic email notification"
objMailItem.Attachments.Add (Filename) 'you only need
this if 'you are sending attachments?
objMailItem.Send

If you want to include an attachment as shown, it has to be a file
not the activeworkbook, so if you want to send the activeworkbbok,
save it first, and then send it as that file. You can use
'ActiveWorkbook.FullName' to access it without using hard-coded
values.

You just will need to add the code to trigger the mailing.



"Damien McBain" wrote in message
...
Hi Ya'll,

I want to automate a daily task which gets some data from a SAP
extract, processes it, then mails a new file to a number of
recipients.
I have done all the data stuff to the point where the new files are
successfully created. How can I then (with VBA) e-mail the file
created to a predefined set of recipients?
Using Excel 2k & Outlook 2k (with exchange) as the mail client.

Any help appreciated.

I have looked at this:
http://www.microsoft.com/exceldev/articles/bulkmail.htm
but I can't d/l the sample app.