Insert an email address into the "To" box

[NickPaton]

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!

 

[Iridium]

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]

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]

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