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

[dognz]

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

[Audiojoe]

Thanks that's brilliant! I'll try it out and get back to you

Thanks again

[Audiojoe]

Hi, sorry about this but it doesnt seem to work. I copied the code exactly and when I use it I get:

Compile Error: User defined type-not defined

Any suggestions?

[Douglas]

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

[Audiojoe]

That's great, but all I want to do is when the button is pressed is for a blank email to come up, no file attached, but the address filled in, and the subject filled in.

I dont mind manually clicking send, but I don't want the file attached

[dognz]

what line of the code do you get the compile error?

[Audiojoe]

On these lines:

Sub OUTLOOK_test_Email()

Dim objOL As New Outlook.Application

[dognz]

You neeed to make sure you have references to the Microsoft Outlook Object Library activated.

To do this go to Tools|Referecnes in the Visual Basic Editor and select the Check box next to Microsoft Outlook Object Library.

This will then allow your Excel VBA to understand the Outlook commands that were causing you the compile errors - hopefully!

If you do not want to attach a workbook to the email - just delete the relavent line of code.

[Audiojoe]

Superb! All working now!

Thanks a lot guys