How to send email from Access without using Outlook

[Seth Schrock]

I would like to be able to send emails from my database. Since not all of the users have Outlook, I need a method that doesn't use Outlook. I do have a VBScript that sends emails, but I don't know how to incorporate the code into my database. Here is my VBScript code if anyone is interested (with the email addresses edited)

Const OverwriteExisting = True
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
 
objFSO.CopyFile "\\ftcbank1\databases\Loan Tracking\Installation Files\Loan Tracking.accdb" , "C:\Loan Tracking\", OverwriteExisting
 
WScript.Echo EMail( "Loan Tracking <[e-mail]>", _
            "Seth Schrock <[e-mail]>", _
                    "IT Inventory has been updated.", _
                    "This works" & vbCrLf & "xyz", _
                    "", _
                    "", _
                    "Exchange", _
                    25 )
 
Function EMail( myFrom, myTo, mySubject, myTextBody, myHTMLBody, myAttachment, mySMTPServer, mySMTPPort )
 
    set objNet = CreateObject("WScript.NetWork")
    strUserName = objNet.UserName
 
    ' Standard housekeeping
    Dim i, objEmail
 
    ' Use custom error handling
    On Error Resume Next
 
    ' Create an e-mail message object
    Set objEmail = CreateObject( "CDO.Message" )
 
    ' Fill in the field values
    With objEmail
        .From     = strUserName & "@company.com"
        .To       = "[e-mail]"
        .Subject  = "Loan Tracking"
        .HTMLBody = "The Loan Tracking database on " & strUserName & "'s computer has been updated"
 
        If mySMTPPort = "" Then
            mySMTPPort = 25
        End If
        With .Configuration.Fields
            .Item( "http://schemas.microsoft.com/cdo/configuration/sendusing"      ) = 2
            .Item( "http://schemas.microsoft.com/cdo/configuration/smtpserver"     ) = mySMTPServer
            .Item( "http://schemas.microsoft.com/cdo/configuration/smtpserverport" ) = mySMTPPort
            .Update
        End With
        ' Send the message
        .Send
    End With
    ' Return status message
    If Err Then
        EMail = "ERROR " & Err.Number & ": " & Err.Description
        Err.Clear
    Else
        EMail = "Message sent ok"
    End If
 
    ' Release the e-mail message object
    Set objEmail = Nothing
    ' Restore default error handling
    On Error Goto 0
End Function

If there is another solution that is better, I'm willing to use it. We use MS Exchange with most users having Outlook, but our roaming users use the Outlook Web App and not the desktop application. I suppose it would be possible to link to the server directly and send an email that way, but not sure how to or how easy that would be.

I read online that the DoCmd.SendObject sends emails using the default email application. In most cases this is Outlook, but the user might not be setup in Outlook so I don't think that this would work.

[zmbd]

I've used the CDO method that you seem to be attempting with this code.
However, I don't use the scripting as the IT often shuts it off.
I declare the CDO object directly...
I found the method online.... too bad I didn't save the link, that's not like me, with the code for reference; however you should be able to find it too, keywords I did note were, "MS Access CDO EMAIL VBA"

[Seth Schrock]

I finally found a working example. Most of them were very complicated some even had references to variables that weren't declared or set. Here is the link: email using Access and VBA without MAPI and here is my implementation of it

Sub mtest()
 
Dim cdoConfig
Dim msgOne
 
Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Exchange.ftc.com"
.Update
End With
 
Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = "[e-mail]"
msgOne.From = "[e-mail]"
msgOne.Subject = "Test email"
msgOne.TextBody = "It works just fine." & vbCrLf & "Line 2."
msgOne.send
End Sub

I will make changes so that the values can be passed to the sub instead of hard coded, but I thought this worked splendidly.

I assume that I will have to install CDO on each computer that will use this?

[Rabbit]

CDO should be standard. At least I haven't run into one where I couldn't use CDO.

[Seth Schrock]

Just noticed one thing. Lines 3 & 4 don't have a data type. Should these be left as varients or is there a better type? I'm afraid I don't understand exactly what is hapening in this code.

<<Edit>> I found another website that had them declared as objects and I tried it and it works.

[Seth Schrock]

I cross posted with you rabbit. I found a website that said I needed to install CDO for it to work and I didn't question it, nor did I try it without it being installed on my PC. I will be sure to try it on my test PC and see if I need it when I role it out to the users.

[zmbd]

FYI: Seth started another thread dealing with a related topic:
Verify Email Sent via CDO
Members looking into the CDO method may fine the inforamation there useful as well.