Follow up about using VBA to send e-mail

Building up on what Marc mentioned, Here is a tried and tested version.

Option ExplicitDeclare 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 LongSub SendMail()    Dim objMail As String    Dim oMailSubj, oMailTo, oMailBody As String    On Error GoTo Whoa    oMailSubj = "YOUR SUBJECT GOES HERE"    oMailTo = "ABC@ABC.COM"    oMailBody = "BLAH BLAH!!!!"    objMail = "mailto:" & oMailTo & "?subject=" & oMailSubj & "&body=" & oMailBody    ShellExecute 0, vbNullString, objMail, vbNullString, vbNullString, vbNormalFocus    Application.Wait (Now + TimeValue("0:00:03"))    Application.SendKeys "%s"    Exit SubWhoa:    MsgBox Err.DescriptionEnd Sub

FOLLOWUP

Thanks for the info. I had already ruled out ShellExecute because it limits the entire parameter string to 250 char and I need about 2000 for the message. But it's looking like SE is the only option that will actually work in my case. – Humanoid1000 7 hours ago

Here is the "Horrible (I love the way JFC says that!!!)" way I mentioned below in the comments which works beautifully :) BTW I have only Outlook as my default client so I have tested it with that.

CODE

Option ExplicitDeclare 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 LongDeclare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)Sub SendMail()    Dim objMail As String    Dim oMailSubj As String, oMailTo As String    Dim i As Long    Dim objDoc As Object, objSel As Object, objOutlook As Object    Dim MyData As String, strData() As String    On Error GoTo Whoa    '~~> Open the txt file which has the body text and read it in one go    Open "C:\Users\Siddharth Rout\Desktop\Sample.Txt" For Binary As #1    MyData = Space$(LOF(1))    Get #1, , MyData    Close #1    strData() = Split(MyData, vbCrLf)    Sleep 300    oMailSubj = "YOUR SUBJECT GOES HERE"    oMailTo = "ABC@ABC.COM"    objMail = "mailto:" & oMailTo & "?subject=" & oMailSubj    ShellExecute 0, vbNullString, objMail, vbNullString, vbNullString, vbNormalFocus    Sleep 300    Set objOutlook = GetObject(, "Outlook.Application")    '~~> Get a Word.Selection from the open Outlook item    Set objDoc = objOutlook.ActiveInspector.WordEditor    Set objSel = objDoc.Windows(1).Selection    objDoc.Activate    Sleep 300    For i = LBound(strData) To UBound(strData)        objSel.TypeText strData(i)        objSel.TypeText vbNewLine    Next i    Set objDoc = Nothing    Set objSel = Nothing    '~~> Uncomment the below to actually send the email    'Application.Wait (Now + TimeValue("0:00:03"))    'Application.SendKeys "%s"    Exit SubWhoa:    MsgBox Err.DescriptionEnd Sub

SNAPSHOT

Text File which has the message

Email just before it is sent

You can probably use the Shell command.

Shell("mailto:username@isp.com")

If Outlook is your default email program, I think windows will interpret that properly (it does it from the windows RUN dialog, but not sure if from VBA). Try it out. I'm not in front of Excel at the moment.

Review the params for "mailto" to see how you can add subject and body to that string.

CORRECTION:That will only generate the email, but won't send it. Disregard my answer.

Here's the final result:

Turns out, the SendMail() worked at home but not at work, but CreateObject("Outlook.Application") did work at work.

My security settings were not the problem with Outlook, it was a mundane typo.

Either way, there's bunch of good info on this page for anyone else who's lost on this topic.