Macro that sets up an email?

[Audiojoe]

Does anyone know how I can do a macro that will set up a mail to be sent. I've seen it done, where you click on a button and a mail comes up with a set address written in and a subject filled in.

Can anyone tell me how to do it? By the way, I'm using Outlook 98

Thanks in advance

 

[Guest]

On 2002-02-27 03:59, Douglas wrote:
If you just want to send the active open workbook, you could use Excel's "Send to Mail Recipient as Attachment" function (located in the file menu.
In VBA this would be:

Application.Dialogs(xlDialogSendMail).Show _
arg1:="e-mail address", _
arg2:="Subject Line"

arg1 is the e-mail address in standard format (is x@xy.com etc)
arg2 is what will appear in the subject line.

The result of this code is to open a new e-mail window, attach the file and complete the To: and Subject lines - you have to manually press SEND.

Hope this helps
D

What to do if I want to send only one sheet and not the whole workbook?
Is this possible?
Many thanks.

 

[Ivan F Moala]

Excel2000 Yes

File > Send to > mail recipient

Then select Send sheet

Ivan

 

[Guest]

On 2002-02-27 03:22, dognz wrote:
Hi,
See the code below - this is a example of how to automate the sending of emails with attachments. If you step through the code you will see which lines to change to suit your needs - you can attach multiple files by repeating the "attachments.add" instruction.

Sub OUTLOOK_test_Email()

'creates link to Outlook
Dim objOL As New Outlook.Application
Dim objMail As MailItem

On Error Resume Next

Set objOL = New Outlook.Application
Set testsemail = objOL.CreateItem(olMailItem)



'sets charactersitics for email
With testsemail
'requests a read confimeration receipt for the email
.ReadReceiptRequested = True
'sets receipent
.To = "Testing, Testing"
'enters subject
.Subject = "Test Email"
'displays email to allow manipulation fro comments
.Display
'enters text for email
.Body = "Testing Email"
attach1 = .Attachments.Add("c:temptest.xls", , 60)
End With

End Sub


Hope this helps,

Dognz

This is the way to send an attachments.Is there also a way to send only one sheet of a workbook and not the hole workbook
Many thanks.

 

[johnou]

Thanks for the coding. But just a quick question, can the program send out an email automatically without my click the SendandReceive button? If this can be done, it will be great because I can use Excel to generate query results and automatically send out to customers.

John