send outlook mail from excel vba

1
votes

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?

3
vbaoutlook

3 Answers

0
votes

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 = "[email protected]"
        .From = "[email protected]"
        .Subject = "MIS Reports" & " " & Date & " " & Time
        .TextBody = "Link to Currency Data :" & vbNewLine & "<" & myDest & ">"
        .Send
    End With

    Set iMsg = Nothing
    Set iConf = Nothing

End Sub
0
votes

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.

0
votes

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://[email protected]?subject=New%20Email&body=This%20is%20the%20message%20body.

Result