MS Access send email (not from outlook or user's email)

Question

I know this question has been asked a few times in various context, but I have not found a clear answer. I have email implemented for an access application using outlook, but I'd like to move away from this. One of the purposes of the email is to email a user his/or password if he forgot it. They can select their username for the login screen, and if they click 'forgot password' and email is sent containing their login information (to the email address associated with the user name).

The problem with this is that the email function as is sends an email with outlook from the user's machine. So, users would be able to 'forgot password' other usernames and view their own outlook outbox(sent items) to see the sensitive information.

Is there a way to e-mail like php's mail function, sending mail from the server? I would like the emails to be sent from the same email address i.e([email protected]), instead of from the user's outlook address after a security prompt. If this is not possible, I am open to the idea of any other workarounds.

I will also add that installing any software that would have to be installed on every potential user's machine is not feasible.

Is this possible?

Archpriest

Access really can't do what you want. It is a desktop database. – Compotation

I'm open to the idea of work arounds: idea for an example workaround could be: only send the 'forgot password' email if the outlook email for the user('from') matches the email for the username in the database(to). – Archpriest

I'm not really familiar with Access code, but if there's some function that sends mail to an SMTP service then you should be able to use any SMTP service you want instead of the user's local Outlook. – Velarium

users would be able to 'forgot password' other usernames and view their own outlook outbox(sent items) to see the sensitive information sounds like a design issue that you're trying to solve by fixing something else instead. – Koehler

CDO Objects have been deprecated and Microsoft has removed the referencing links. These "solutions" are no longer viable. – Hop

Answer 1

Windows includes an object called Collaborative Data Objects or CDO. This object allows you to send emails using any SMTP server assuming that other prerequisites are met (firewall open, ISP not blocking ports, account is configured on the SMTP server, SMTP server allows relaying, etc).

Most of the examples I've found use late binding, which is preferred. In my testing on XP it appeared that the correct library reference, if you prefer to use early binding, is "Microsoft CDO for Windows 2000 Library".

It's important to know that any time you send email you will have to send it through (or out of) some kind of email server. This means you will have to authenticate with that email server and also usually means that you need to send the email out using a "From" email address that exists on that very email server.

Here's some code using late binding:

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
' Use basic (clear-text) authentication.
Const cdoBasic = 1
' Use NTLM authentication
Const cdoNTLM = 2 'NTLM

Public Sub SendEmail()
    Dim imsg As Object
    Dim iconf As Object
    Dim flds As Object
    Dim schema As String

    Set imsg = CreateObject("CDO.Message")
    Set iconf = CreateObject("CDO.Configuration")
    Set flds = iconf.Fields

    ' send one copy with SMTP server (with autentication)
    schema = "http://schemas.microsoft.com/cdo/configuration/"
    flds.Item(schema & "sendusing") = cdoSendUsingPort
    flds.Item(schema & "smtpserver") = "mail.myserver.com"
    flds.Item(schema & "smtpserverport") = 25
    flds.Item(schema & "smtpauthenticate") = cdoBasic
    flds.Item(schema & "sendusername") = "[email protected]"
    flds.Item(schema & "sendpassword") = "password"
    flds.Item(schema & "smtpusessl") = False
    flds.Update

    With imsg
        .To = "[email protected]"
        .From = "[email protected]"
        .Subject = "Test Send"
        .HTMLBody = "Test"
        '.Sender = "Sender"
        '.Organization = "My Company"
        '.ReplyTo = "[email protected]"
        Set .Configuration = iconf
        .Send
    End With

    Set iconf = Nothing
    Set imsg = Nothing
    Set flds = Nothing
End Sub

Oppose

"From" email address that exists on that very email server - this does not work for me in my locale, and from my reading I understand that you must use the server provided by your ISP. This is to prevent spam. – Dramatize

That just all depends on your ISP. Some only allow port 25 to be used to talk to their own SMTP server and some do not have this limitation. Some of your big cable providers do this but many of your smaller ISP's do not have this limitation. For example, neither Windstream or CenturyLink have this limitation. You can also get around this by using an alternate port (assuming your SMTP server is exposing an alternate port) since most ISP's only impose this limitation on port 25. As an example of this, you can probably use GMail as your SMTP server - GMail uses 587 or 465 with SSL. – Oppose

I am also working on one same kind of requirement and realized that above code lets you send email from any email address (i.e. any incorrect email [email protected]) and without specifying the correct password. It's just that you should enter the right server address (i.e. mail.myserver.com) – Houri

@Houri What you're talking about is actually a server setting, not some benefit of how you send the email. I'm not exactly sure what the setting is, "anonymous relay" maybe. – Oppose

Great ! this is working, how can i send an ms access report attached using CDO ? – Ehrman

Answer 2

This works for me in MS Access 2010 / Windows 7

sMailServer = "myISPsmtp" 'Not just any old smtp
sMailFromAddress = "me"
sMailToAddress = "me"

Set ObjMessage = CreateObject("CDO.Message")
sToAddress = sMailToAddress
sSubject = "Subject"
sBody = "MailBody"

ObjMessage.Subject = sSubject
ObjMessage.From = sMailFromAddress
ObjMessage.To = sToAddress
'ObjMessage.cc = sCCAddress
ObjMessage.TextBody = sBody
'ObjMessage.AddAttachment sMailAttachment
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sMailServer
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
ObjMessage.Configuration.Fields.Update
ObjMessage.send

More info: http://msdn.microsoft.com/en-us/library/ms526318(v=exchg.10).aspx

Dramatize

So would I just be setting sMailServer to my office's email server? and the mailFromAddress to be the dummy account that I create? ('[email protected]') and sMailTo to be a userName's email address from the databasE? – Archpriest

+1 I'm too slow ^_^ but this is a great method. I shall focus on one of my concerns instead. You should not be storing un-encrypted passwords within your database, and definately should not be sending that information via e-mail (even if to the correct person). Instead you need to encrypt your passwords and also offer user's the ability to reset their password rather than just sending it out. – Chub

I am by no means sure which mail server will work for you, I had to use the server supplied by my ISP, apparently this is a security measure to stop spam. – Dramatize

As for from, I reckon pretty nearly anything will do. – Dramatize

@ Matt I understand that. I'm not too incredibly worried about employees intentionally doing something malicious. This database will have another layer of security as it will be on a shared drive in a folder that is only accessible by certain people. I just don't want it to be as easy to get someone's password as looking in the outbox of your email after clicking 'forgot password' for someone else. I shall talk with IT about what server to use and try out your method. Thanks Remou(and Matt) – Archpriest

Some servers allow any From address, legitimate or not, and others require the From address to be a valid email address on that server. – Oppose

Seems like this method lets you spoof about anything on my server. Just noticed that there's an addAttachment method. Could that work with just a relative path to say, an excel sheet? – Archpriest

This worked great for me on my production server... but the weirdest thing is that when I tried to implement the exact same script it doesn't deliver the emails to all users from the development server... but it will deliver all emails from the production server... any clues as to why that is? – Sigfried

CDO does not support 64 bit version. Any other method to send email without Outlook for 64 bit systems? – Mooring

@Archpriest Yes, that works. I have an Excel file that includes the VBA to send itself as an attachment in an iMsg function using .AddAttachment (dashName), with that variable being declared earlier on as dashName = "local path minus the file extension - " + Format(Now(), "DDMMMYY") + ".xlsx" – Fallow

Answer 3

I cannot add this to the comments because I do not have enough reputation, so please don't axe me.

"Seems like this method lets you spoof about anything on my server. Just noticed that there's an addAttachment method. Could that work with just a relative path to say, an excel sheet? "

It works for me (Access 2010, Exchange 2010):

.AddAttachment ("URL HERE")

https://msdn.microsoft.com/en-us/library/ms526453(v=exchg.10).aspx https://msdn.microsoft.com/en-us/library/ms526983(v=exchg.10).aspx

Winery

Answer 4

The following MS-Access VBA code works for smtp.office365.com. You DO indicate smtpusessl=true, but you do NOT specify the port, otherwise you get error 5.7.57.

Sub SMPTTest2()
Set emailObj = CreateObject("CDO.Message")

emailObj.From = "[email protected]"
emailObj.To = "[email protected]"
emailObj.Subject = "Test CDO"
emailObj.TextBody = "Test CDO"
'emailObj.AddAttachment "c:\windows\win.ini"

Set emailConfig = emailObj.Configuration


emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
'Exclude the following line    
'emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
emailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"
emailConfig.Fields.Update

emailObj.Send

If Err.Number = 0 Then MsgBox "Done"
End Sub

Sari

Answer 5

At my company I used a other solution. I have created a C# Class Library with COM classes / objects. COM classes can be implemented in your Access application and this way you can use all the advantages of C# (Mailing for example) and still use it (calling it) in Access.

The only disadvantage is that you have to register your Class Library (DLL) at all the computers who use your access application. I have done that with a simple power-shell script which executes at the start of the Access application.

A good start for A COM based library is here: https://www.codeproject.com/Articles/7859/Building-COM-Objects-in-C

If you would like some more information about it then I am always happy to help you.

Charolettecharon