Auto mail from Excell 1

[smithcza]

Hi. How can I save a excell workbook and mail it to a specified address using code (via a button)
Regards,
Smithcza

SmithcZA

 

[PHV]

Have a look at the SendMail method of the Workbook object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smithcza]

Will give it a try. Thanks.

SmithcZA

 

[smithcza]

Hi.

I am still new with this one, can you please help.

Look at my code, this is what I got from the excel help file, when i try to use it, it errors "Object doesn't support this object or method" - highlighting the "With Aplication.ActiveDocument.MailEnvelope" line

Sub SendMail(ByVal strRecipient As String)

'Use a With...End With block to reference the MsoEnvelope object.
With Application.ActiveDocument.MailEnvelope

'Add some introductory text before the body of the e-mail.
.Introduction = "Please read this and send me your comments."

'Return a Microsoft Outlook MailItem object that
'you can use to send the document.
With .Item

'All of the mail item settings are saved with the document.
'When you add a recipient to the Recipients collection
'or change other properties, these settings will persist.
.Recipients.Add strRecipient
.Subject = "Here is the document."

'The body of this message will be
'the content of the active document.
.Send
End With
End With
End Sub

SmithcZA

 

[xlbo]

that's funny - the help file for Sendmail uses this as an example:

ActiveWorkbook.SendMail recipients:="Jean Selva"

no mention of using the MAilEnvelope object - anyhoo, I would imagine the error is being caused as you are trying to refer to the activedocument
there is no such object in excel - you need to use activeworkbook

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smithcza]

Works like a charm, thanks a mil.

SmithcZA

 

[firefytr]

I prefer to use straight Outlook myself, have never been a big fan of the SendMail object. Here are some examples ...

http://vbaexpress.com/kb/getarticle.php?kb_id=97

http://vbaexpress.com/kb/getarticle.php?kb_id=326

-----------
Regards,
Zack Barresse

 

[xlbo]

if all you want to do is send a file and you're not bothered about subject lines / text etc, I don't see any reason not to use it.......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smithcza]

will look at them, thanks

SmithcZA

 

[firefytr]

I never want to 'just send a file'.

-----------
Regards,
Zack Barresse