Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to link to an Excel file when sending an email in Outlook? | Excel Discussion (Misc queries) | |||
How to get rid of the warning msg from Outlook when sending email via VBA code in Excel | Excel Programming | |||
How to get rid of the warning msg from Outlook when sending email via VBA code in Excel | Excel Programming | |||
How to get rid of the warning msg from Outlook when sending email via VBA code in Excel | Excel Programming | |||
sending email from excel using outlook express | Excel Programming |