sending email with Outlook using Excel

[DKY]

I'm just trying to send a basic message to a list of people with
Microsoft Outlook using Excel. I thought that it would be a great idea
to, once a macro finished, put a snippet of code that sends an email to
others that are waiting on this particular macro to finish that says
"File's Done!". I've googled and here's the best I could find, problem
is, it opens the email but you have to press the send button. How can I
get this to automatically send?

Code:
--------------------
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

' Get the email address
Email = "(e-mail address removed); (e-mail address removed)"

' Message subject
Subj = "The File you've been waiting for"

' Compose the message
Msg = "File is Done!"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
End Sub

 

[Ron de Bruin]

Hi DKY

If you use Outlook and not Outlook Express not use this code
See this page for example code
http://www.rondebruin.nl/sendmail.htm

 

[Jim Thomlinson]

Ron de Bruin is the guru in this area. Check out his web site.

http://www.rondebruin.nl/

 

[DKY]

Okay, I decided that I might use this one
http://www.rondebruin.nl/mail/folder3/smallmessage.htm
and I plug in the code and try to step into it only to get this error.

Compile error:
User-defined type not defined

on this line

Code:
--------------------
Dim OutApp As Outlook.Application
--------------------


I followed the directions and went into my Tools/Reference and noticed
that my "Microsoft Office 11.0 Object Library" was already checked so I
don't know what I'm missing here.

 

[Ron de Bruin]

Read this

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number

 

[DKY]

Read this

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) Tools>References in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number

I followed the directions and went into my Tools/Reference and noticed
that my "Microsoft Office 11.0 Object Library" was already checked so I
don't know what I'm missing here.

Isn't that what I did?

 

[DKY]

No, that's not what I did. Alright, it seems to not give me errors, let
me see if I can get it to work for what I need. Thanks Ron de Bruin

 

[Ron de Bruin]

Outlook not Office

 

[DKY]

This works beautifully, thanks again