Send mail from access?

[Behedwin]

Hey



I have a login form.
The user selects a user in a combobox named "txtSelectUser"

I have a table storing the username, password and emailadress to the user.

I would like the user to be able to press a button and receive a mail with the password that he/she have.

So if the user selects "Johan" in the combobox and press the button "mailpassword"
Then a mail is sent to the adress connected to the "Johan" record in the table
with the "password" information that is stored on that record.

How can i do this?

[CJ_London]

there are a number of ways, depends on whether or not you are using outlook and what you are sending. google 'access vba send email' for examples, but sounds like using the sendobject function may be your simplest solution

https://msdn.microsoft.com/en-us/vba...-method-access

just leave object type and name blank

[Behedwin]

Ive seen that page but i dont really understand how to do this.

I would like to setup a @gmail.com account and send all mails from that account if possible.

And i dont see how i would setup the code so that the mail only contain the text stored in the column "password" of the record being picked from a combobox selection.

[alansidman]

here is an older video that you should be able to adapt to your needs. It was originally developed for Access 2003.

http://www.datapigtechnologies.com/f...sendemail.html

[Behedwin]

found this

Option Compare Database
Option Explicit

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") = "smtp.gmail.com"
    flds.Item(schema & "smtpserverport") = 25
    flds.Item(schema & "smtpauthenticate") = cdoBasic
    flds.Item(schema & "sendusername") = "xxxx@gmail.com"
    flds.Item(schema & "sendpassword") = "xxxx"
    flds.Item(schema & "smtpusessl") = False
    flds.Update
 
    With imsg
        .To = "xxxx@yyyy.se"
        .From = "xxxx@gmail.com"
        .Subject = "Test Send"
        .HTMLBody = "Test"
        Set .Configuration = iconf
        .Send
    End With
 
    Set iconf = Nothing
    Set imsg = Nothing
    Set flds = Nothing
End Sub

however it seems this is no longer availible from microsoft.
schema = "http://schemas.microsoft.com/cdo/configuration/"

https://msdn.microsoft.com/en-us/lib...exchg.65).aspx

is there some other options for this to work?

[CJ_London]

if you want to use gmail, cdo is the way to go

not sure where you got that code from, suggest take a look at this site http://www.blueclaw-db.com/access_email_gmail.htm

[Behedwin]

Using that site i get this error code

http://prntscr.com/hpzs9z

using this code:

Dim cdomsg As Object

Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxx@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxx"
.Update
End With

' build email parts
With cdomsg
.To = "xxxx@xxxx.se"
.From = "xxxx@gmail.com"
.Subject = "the email subject"
.TextBody = "the full message body goes here. you may want to create a variable to hold the text"
.Send
End With
    Set cdomsg = Nothing

[CJ_London]

regret I cannot read the error, but if you are using the code exactly as is, you need to change it to put your gmail account name etc. And if you have done those changes, how do you expect me to help if I don't know what you have done?

I do notice one potential typo in the example

.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587

[Behedwin]

sry, forgot to translate the error message.

it says the following:
message could not be sent SMTP-server. errorcode 0x80040217. Server not availible.

i found the solution here
https://stackoverflow.com/questions/...ail-in-asp-net

it was my google account, i had to enable less secure apps on it.


Now it works to send the email.

Now i need help to configure the mail so it contains the info i want.

table: user_table
columns in table: user_id (primary key), username (text), password (text), mail (text)

FORM: login_form
controls: combobox1
if user select username "johan" in combobox (combobox contains user_id column + username column)
then i want to send the mail to johans mailadress
and the mail should contain the password of johan

im thinking ill do this with variables

Dim cdomsg As Object
Dim receiver as string
Dim pw as string


Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "yyyy@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxx"
.Update
End With


set receiver = DLookup("[Mail]", "User_Table", "[User_ID] =" & Forms![user_form]!combobox1)
set pw = DLookup("[Password]", "User_Table", "[User_ID] =" & Forms![user_form]!combobox1)



' build email parts
With cdomsg
.To = receiver
.From = "xxxx@gmail.com"
.Subject = "the email subject"
.TextBody = pw
.Send
End With
    Set cdomsg = Nothing

is this correct thinking?

[CJ_London]

you could bring all the info through in your combobox (hiding the columns you don't want to see). Also you don't use set when assigning a value to a string

then assuming the code is in your form just reference it directly e.g.

With cdomsg
.To = me.combobox1.column(2)
.From = "xxxx@gmail.com"
.Subject = "the email subject"
.TextBody = me.combobox1.column(3)
.Send
End With

columns start from 0 (I'm assuming the first column is a PK, the 2nd is a name (i.e. column 2), the third is their email and the fourth the password

[Behedwin]

Cool.
Ill keep that in mind for future things.
I figured out how to do it in the way i first posted... so since it works just fine (so far)
ill keep it

Public Sub SendEmail()

Dim cdomsg As Object
Dim receiver As String
Dim pw As String
Dim answer As Integer


Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "okuus83@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "senapochtomtar12"
.Update
End With


If IsNull(Form_Login_Form.txtUsername) = True Then
MsgBox "Du måste välja ett namn i listan. Väjler du någon annans namn får de sitt lösenord skickat till sig. Så var noga med att välja ditt namn för att få ett lösenord!"
Else

answer = MsgBox("Är du " & DLookup("[Username]", "Access_Table", "[User_ID] =" & Forms![Login_Form]!txtUsername) & " med mail: " & DLookup("[Mail]", "Access_Table", "[User_ID] =" & Forms![Login_Form]!txtUsername), vbYesNo + vbQuestion, "Är du")
If answer = vbYes Then
receiver = DLookup("[Mail]", "Access_Table", "[User_ID] =" & Forms![Login_Form]!txtUsername)
pw = DLookup("[Password]", "Access_Table", "[User_ID] =" & Forms![Login_Form]!txtUsername)

' build email parts
With cdomsg
.To = receiver
.From = "okuus83@gmail.com"
.Subject = "Ditt lösenord är"
.TextBody = "Ditt lösenord är: " & pw
.Send
End With
    Set cdomsg = Nothing
Else
End If

End If


End Sub

added a few messages aswell when performing the tasks.