Insert an email address into the "To" box

NickPaton

New Member
Joined
Oct 31, 2002
Messages
34
Hi

Apologies for the simple question, but cannot find the answer.

I have an active worksheet, which users currently send by email, using the following code:

Application.Dialogs(xlDialogSendMail).Show

This allows me to insert the addresses of those people, to whom I want to send the worksheet, and for me to choose when to send the email (using the "Send" button)

I want to have those addresses automatically pre-inserted into the "To" box, but for the email to not be automatically sent, until the user clicks on the "Send" button.

I know the code for automatically sending the email, using typically:

ActiveWorkbook.SendMail Recipients:="nick.paton@home.com"

But this does not give me the control to choose when to send the email.

I am using Excel 97 and Outlook

Please help and many thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
I'm no VBA monkey but there's a load of great stuff on this page: http://www.rondebruin.nl/sendmail.htm

From the sounds of it though you know how to insert the address but not when to send the mail? Your subject for the topic (if I'm right) is misleading(?)

Sorry I can't help any more than that!
 

NickPaton

New Member
Joined
Oct 31, 2002
Messages
34
Thanks for the reply.

I have had a look at the SendMail examples there, but they seem to automatically send the email to those addresses.

What I want to do is to have an email window opened up when I run the macro, with the active sheet as the attachment, and to also have the recipients of the attachment automatically filled in.

The user will automatically fill in the Subject box, and add any additional comments in the email body, as required.

I know it's easy to insert email addresses by hand, it just saves the users having to look them up and insert them.

Many thanks

Nick
 

NickPaton

New Member
Joined
Oct 31, 2002
Messages
34
You are right!

I found within the hyperlink the following code which I changed a little to add multiple recipients, which does the trick:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail

.Recipients.Add "nickpaton@home.co.uk"
.Recipients.Add "xyzpaton@home.co.uk"
.Subject = "This is the email"
.Body = "Thank you."
.Attachments.Add ("C:\Temp\abc.xls")

' Or wherever the attachment has been previously saved

.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

ActiveWorkbook.Close

The ".Display" allows you to see the email before it is sent.

To send automatically without seeing it, change to ".send".

I include this coz it did take a bit of finding, and I'm sure that I'm not the first to need it.

Many thanks for your help and comments - appreciated

Nick
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,872
Messages
5,766,864
Members
425,382
Latest member
IronM

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
Top