Sending Emails without Outlook.

[highandwild]

Hi all

I have previously developed applications to manage the automation of emails from Access 2003 using Outlook but I now have Access 2010 and have not got Outlook.

I am under the impression from this site:http://www.dailyaccesstips.com/automating-microsoft-access-email-alerts/ that I can send emails using DoCmd.SendObject without using Outlook.

I do not quite understand how this would work.

Does anybody have any experience of doing this?

Thanks

 

[VilaRestal]

Microsoft's CDO package is the best way I think. It's just a normal reference, create a new CDO.Message object, set it's properties and then send it.

I did package it in a function at work but I don't have it with me That's what you should be looking to do with error handling and all.

The first good hit I could find on a google search for it gives this link:

http://www.blueclaw-db.com/access_email_gmail.htm

There's quite a lot of things that need changing for different mail servers.

If you need any help getting that to work say so.

 

[VilaRestal]

And in fact it's something that could do with being here.

Here's my take on a SendEmail function with error handling:

Option Compare Database
Option Explicit

Private Const URL_CDOCONFIG As String = "http://schemas.microsoft.com/cdo/configuration/"


Public Function SendEmail(ByVal sTo As String, ByVal sFrom As String, _
        Optional ByVal sCC As String = "", Optional ByVal sBCC As String = "", _
        Optional ByVal sSubject As String = "", Optional ByVal sBody As String = "", _
        Optional ByVal sServer As String = "smtp.gmail.com", Optional ByVal iPort As Integer = 587, _
        Optional ByVal sUsername As String = "mygmail@gmail.com", Optional ByVal sPassword As String = "mypassword", _
        Optional ByVal iSendUsing As Integer = 2, Optional ByVal bAuthenticate As Boolean = True, _
        Optional ByVal bUseSSL As Boolean = True, Optional ByVal iTimeout As Integer = 60) As Boolean
        
    On Error Resume Next
    Err.Clear
    Dim cdomsg As CDO.Message
    Set cdomsg = CreateObject("CDO.message")
    If Err Then
        Debug.Print Err.Description
        SendEmail = False 'Message not sent
    Else
        With cdomsg
            With .Configuration.Fields
                .Item(URL_CDOCONFIG & "sendusing") = iSendUsing
                .Item(URL_CDOCONFIG & "smtpserver") = sServer
                .Item(URL_CDOCONFIG & "smptserverport") = iPort
                .Item(URL_CDOCONFIG & "smtpauthenticate") = IIf(bAuthenticate, 1, 0)
                .Item(URL_CDOCONFIG & "smtpusessl") = bUseSSL
                .Item(URL_CDOCONFIG & "smtpconnectiontimeout") = iTimeout
                .Item(URL_CDOCONFIG & "sendusername") = sUsername
                .Item(URL_CDOCONFIG & "sendpassword") = sPassword
                .Update
            End With
            .To = sTo
            .From = sFrom
            .CC = sCC
            .BCC = sBCC
            .Subject = sSubject
            .TextBody = sBody
            If Err Then
                Debug.Print Err.Description
                SendEmail = False 'Message not sent
            Else
                DoCmd.Hourglass True
                .Send
                DoCmd.Hourglass False
                If Err Then
                    Debug.Print Err.Description
                    SendEmail = False 'Message not sent
                Else
                    SendEmail = True 'Message sent
                End If
            End If
        End With
        Set cdomsg = Nothing
    End If
End Function

Obviously needs appropriate settings for your email system.

And I've not tested it but it should work. (It's about the same as one I've done before which did work). If anyone finds a fault please say.

 

[spikepl]

Before writing a lot of code, set your email client, whatever that is, as the default email client in windows and try the docmd.send ...SendObject/SendnoObject should be MAPI-compliant, and able to talk to a MAPI-compliant mail client.

Without any email client you have to use eg CDO. But what is the point of writing code, if you can just DL some client.

 

[VilaRestal]

Yeah, it all boils down to whether
a) you can be sure the user will have mapi email setup
b) you want to be sending the email from the user

if yes to both of the above use DoCmd.SendObject
otherwise use CDO

 

[smig]

You are welcome to try the email application for Access I posted on the Code Repository

I use vbSendMail.dll - a free send mail DLL
it's easy to use. Only problem it has no SSL support
there are other DLL with SSL that can be used

 

[e2kman]

I am very grateful to the contributor of this code. I only found one error in the code it was 'Dim cdomsg As CDO.Message' which should have been 'Dim cdomsg as object'. It worked the first time. I do have a question though. When I supply a value for the parameter sFrom, it is accepted and the email is sent. However, the receiver of the email sees the email as being FROM the sUserName parameter (which is the mail server user name) instead of the sFrom email provided. Is that how it has to be? Have I done something wrong? Please advise. And again, thanks for the great code. I have been looking and trying others' suggestions for hours and your is the first to work.

 

[VilaRestal]

Hmm that might be something your mail server imposes on it. You could try adding either or both of these lines within the With cdomsg block

.Sender = sFrom
.ReplyTo = sFrom

ReplyTo should automatically be the same as From and your mail server might not allow Sender to be specified (it might consider that as relaying).

I've not tested either yet. I have used that code and it works but then I don't think there was a difference in the reply address of the mail account it connects to and the From address of the message so I wouldn't have noticed if it was using the mail account's address (I presume the username is the mail accounts address?).

 

[WaiveIt]

Before writing a lot of code, set your email client, whatever that is, as the default email client in windows and try the docmd.send ...SendObject/SendnoObject should be MAPI-compliant, and able to talk to a MAPI-compliant mail client.

Without any email client you have to use eg CDO. But what is the point of writing code, if you can just DL some client.

Can the CDO over-rule a default email client?

Or is there a way to use SendOjbect without evoking an Outlook response?

I want users to be able to send email based on MS Access application interaction with no changes in behavior depending on Outlook presence

 

[GregoryWest]

I am reading all the comments here. Think this is closer to what I need to do. I need to send out eMails based on a query the eMails will have different text in them and be sent to three people, the TO: CC: and BCC: Also I need the eMail to NOT come from the user running the access program but to come from a control user eMail (Specifically Service@company.com)

It is looking like I have to use CDO, correct.

 

[slickdog]

And in fact it's something that could do with being here.

Here's my take on a SendEmail function with error handling:



Obviously needs appropriate settings for your email system.

And I've not tested it but it should work. (It's about the same as one I've done before which did work). If anyone finds a fault please say.

Hi

I have tried this however when I try to use the function as below :
i get "Compile Error:Expected Expression"



SendEmail (varTo,"messages@XXXX.com",,,,stText,,,,,,,,)

where I am using "Varto" as the string that passes the email address for sending and "stText" as the message.
where am I - (Dumb user) going wrong?

 

[smig]

I had some troubles using CDO connecting to SSL servers.
I'll update my small App to use ostrosoft's SMTP dll. Its free to use.

 

[rpfisher]

Hi all
I have recently installed windows 10 and since then I cannot email from the default mail program (emclient).
I have tried the regedit fix but that didn't work.
Anyone else have a problem with this?
Thanks
Paul

--update--
I have found another section for setting default programs and it has fixed the problem, sorry for wasting your time, thanks
--end--