Macro that sets up an email?

Audiojoe

Active Member
Joined
Feb 20, 2002
Messages
285
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top