sending email with Outlook using Excel
 

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 = ; "

' 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
--------------------


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" wrote in message ...

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 = ; "

' 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
--------------------


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

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

http://www.rondebruin.nl/
--
HTH...

Jim Thomlinson


"DKY" wrote:


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 = ; "

' 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
--------------------


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

Read this

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) ToolsReferences in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number
--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" wrote in message ...

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

Ron de Bruin Wrote:
Read this

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) ToolsReferences in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number
--
Regards Ron de Bruin
http://www.rondebruin.nl

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
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

Outlook not Office


--
Regards Ron de Bruin
http://www.rondebruin.nl


"DKY" wrote in message ...

Ron de Bruin Wrote:
Read this

You must add a reference to the Microsoft outlook Library.

1) Go to the VBA editor, Alt -F11
2) ToolsReferences in the Menu bar
3) Place a Checkmark before Microsoft Outlook ? Object Library
? is the Excel version number
--
Regards Ron de Bruin
http://www.rondebruin.nl

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
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398

sending email with Outlook using Excel
 

This works beautifully, thanks again


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=517398