send outlook mail from excel vba

Question

I have the following code that sends outlook mail. But this will not work when outlook is closed.
Sub DraftMail(emailAddr, strBody, strSub) Dim OutApp As Object Dim OutMail As Object Set OutApp = GetObject(, "Outlook.Application") If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application") End If Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .To = ""
    .CC = ""
    .BCC = emailAddr
    .Subject = strSub
    .HTMLBody = strBody
    .Send   'or use .Display
    .ReadReceiptRequested = True
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Can anybody help me how to make it work even when the outlook is closed?

score 0 · Answer 1 · answered May 31 '13 at 09:18

Try this code : (Not tested)

Sub SendMail()

    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1
    Set Flds = iConf.Fields

    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "<server>"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With

    With iMsg

        Set .Configuration = iConf
        .To = "test@gmail.com"
        .From = "test@gmail.com"
        .Subject = "MIS Reports" & " " & Date & " " & Time
        .TextBody = "Link to Currency Data :" & vbNewLine & "<" & myDest & ">"
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing

End Sub

Thanks Santosh for your response. But I need to send a mail only through Outlook. Could you please modify my code to make it work — Tjs, May 31 '13 at 10:58
@Tjs You need to enter server details, Change to, from email id & hopefully it should work even for outlook. — Santosh, May 31 '13 at 11:19

score 0 · Answer 2 · edited May 23 '17 at 12:18

First do you have any error handling in there? For example if you call getObject and it is closed you should get an error?

So the way that most people use is to call get object and if that errors then they know outlook is closed and they create a new instance.

If you want to be very precise the error code number is 429 see for example this code here Link to previous question.

To get you started this should also work

On Error Resume Next

Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then
   Set OutApp = CreateObject("Outlook.Application")
End If

Once you have this working then you can remove the "On Error Resume Next" and catch the specific error 429 if you want, and then you know that the error is because Outlook is not running.

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

The easiest thing to do is construct a mailto:// url and run it through a shell command.

You must URL encode the subject and body for it to show up properly.

example (paste in Run command in Windows):

mailto://user@domain.com?subject=New%20Email&body=This%20is%20the%20message%20body.

Result

edited May 23 '17 at 10:30

Community

1
1

answered May 31 '13 at 16:58

John Alexiou

28,472
11
77
133

send outlook mail from excel vba

3 Answers3