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?
Please Login or Register to view this content.
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" <[email protected]> wrote in message news:[email protected]...
>
> 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 = "[email protected]; [email protected]"
>
> ' 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
>
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" <[email protected]> wrote in message news:[email protected]...
>
> 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 = "[email protected]; [email protected]"
>
> ' 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
>
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 = "[email protected]; [email protected]"
>
> ' 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
>
>
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.
on this lineCompile error:
User-defined type not defined
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.Please Login or Register to view this content.
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
--
Regards Ron de Bruin
http://www.rondebruin.nl
"DKY" <[email protected]> wrote in message news:[email protected]...
>
> 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
>
Originally Posted by Ron de Bruin
Isn't that what I did?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.
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
Outlook not Office
--
Regards Ron de Bruin
http://www.rondebruin.nl
"DKY" <[email protected]> wrote in message news:[email protected]...
>
> 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) Tools>References 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
>
This works beautifully, thanks again
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks