E-mail from excel

[Damien McBain]

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.

 

[Frank Kabel]

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

 

[Bob Phillips]

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("(e-mail address removed)")
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)

 

[John]

Try looking up

 

[Damien McBain]

Try looking up

thanks fellaz

 

[Damien McBain]

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("(e-mail address removed)")
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.

Thanks Bob, exactly what I was looking for

cheers

Damo

 

[Damien McBain]

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]

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)