Prev: Can I change the yes/no format in an Access table, to New/Existing
Next: Range Selection for Dropdown List
From: Sharkbyte on 19 Apr 2010 14:46
I have not worked with sending email from Access, before, but now I am
attempting to create a function that will generate an email in response to
certain user actions.

I have cobbled together some code, and I believe it is close to functional.
However, I am failing when trying to connect to Google's GMail server.

I'm hoping someone can point me in the right direction, or to an How To
article...

Here is my code:

Public Function SendEMail_CDO1(strFrom, strTo, strSubject, strBody,
strSmtpServer, strSendUserName, strPassword, Optional strCC) As Boolean
On Error GoTo ErrorHandling

Dim mail
Dim config
Dim fields
Dim i As Integer

Set mail = CreateObject("CDO.Message")
Set config = CreateObject("CDO.Configuration")
Set fields = config.fields

With fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
"smtp.gmail.com"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
True

..Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
= "myemail(a)gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
= "xxxx"

..Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
= 15
.Update
End With

Set mail.Configuration = config

With mail
.From = strFrom
.To = strTo
.Subject = strSubject
.TextBody = strBody

If blnAttachedFile = True Then
.AddAttachment = strAttachedFile
Else
End If

.Send
End With

Set mail = Nothing
Set fields = Nothing
Set config = Nothing

SendEMail_CDO1 = True
Exit Function

ErrorHandling:
MsgBox Err.Description & " Error Number: " & Err.Number
Debug.Print Err.Description & " " & Err.Number
SendEMail_CDO1 = False
Set mail = Nothing
Set fields = Nothing
Set config = Nothing

End Function



From: Daniel Pineault on 19 Apr 2010 16:06
Failing? Do you get an error?

You might want to take a look at the following example for inspiration

http://www.worksrite.com/CDOMail.htm
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Sharkbyte" wrote:

> I have not worked with sending email from Access, before, but now I am
> attempting to create a function that will generate an email in response to
> certain user actions.
>
> I have cobbled together some code, and I believe it is close to functional.
> However, I am failing when trying to connect to Google's GMail server.
>
> I'm hoping someone can point me in the right direction, or to an How To
> article...
>
> Here is my code:
>
> Public Function SendEMail_CDO1(strFrom, strTo, strSubject, strBody,
> strSmtpServer, strSendUserName, strPassword, Optional strCC) As Boolean
> On Error GoTo ErrorHandling
>
> Dim mail
> Dim config
> Dim fields
> Dim i As Integer
>
> Set mail = CreateObject("CDO.Message")
> Set config = CreateObject("CDO.Configuration")
> Set fields = config.fields
>
> With fields
> .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> "smtp.gmail.com"
>
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
> True
>
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
> .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
> = "myemail(a)gmail.com"
> .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
> = "xxxx"
>
> .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
> = 15
> .Update
> End With
>
> Set mail.Configuration = config
>
> With mail
> .From = strFrom
> .To = strTo
> .Subject = strSubject
> .TextBody = strBody
>
> If blnAttachedFile = True Then
> .AddAttachment = strAttachedFile
> Else
> End If
>
> .Send
> End With
>
> Set mail = Nothing
> Set fields = Nothing
> Set config = Nothing
>
> SendEMail_CDO1 = True
> Exit Function
>
> ErrorHandling:
> MsgBox Err.Description & " Error Number: " & Err.Number
> Debug.Print Err.Description & " " & Err.Number
> SendEMail_CDO1 = False
> Set mail = Nothing
> Set fields = Nothing
> Set config = Nothing
>
> End Function
>
>
>
From: Sharkbyte on 19 Apr 2010 16:33
Daniel:

Thanks for the link. I will look at it.

In regards to your question, yes, it returns the following error:

'The transport failed to connect to the server. Error number: -2147220973'

Sharkbyte



"Daniel Pineault" wrote:

> Failing? Do you get an error?
>
> You might want to take a look at the following example for inspiration
>
> http://www.worksrite.com/CDOMail.htm
> --
> Hope this helps,
>
> Daniel Pineault
> http://www.cardaconsultants.com/
> For Access Tips and Examples: http://www.devhut.net
> Please rate this post using the vote buttons if it was helpful.
>
>
>
> "Sharkbyte" wrote:
>
> > I have not worked with sending email from Access, before, but now I am
> > attempting to create a function that will generate an email in response to
> > certain user actions.
> >
> > I have cobbled together some code, and I believe it is close to functional.
> > However, I am failing when trying to connect to Google's GMail server.
> >
> > I'm hoping someone can point me in the right direction, or to an How To
> > article...
> >
> > Here is my code:
> >
> > Public Function SendEMail_CDO1(strFrom, strTo, strSubject, strBody,
> > strSmtpServer, strSendUserName, strPassword, Optional strCC) As Boolean
> > On Error GoTo ErrorHandling
> >
> > Dim mail
> > Dim config
> > Dim fields
> > Dim i As Integer
> >
> > Set mail = CreateObject("CDO.Message")
> > Set config = CreateObject("CDO.Configuration")
> > Set fields = config.fields
> >
> > With fields
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> > "smtp.gmail.com"
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
> > True
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
> > = "myemail(a)gmail.com"
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
> > = "xxxx"
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
> > = 15
> > .Update
> > End With
> >
> > Set mail.Configuration = config
> >
> > With mail
> > .From = strFrom
> > .To = strTo
> > .Subject = strSubject
> > .TextBody = strBody
> >
> > If blnAttachedFile = True Then
> > .AddAttachment = strAttachedFile
> > Else
> > End If
> >
> > .Send
> > End With
> >
> > Set mail = Nothing
> > Set fields = Nothing
> > Set config = Nothing
> >
> > SendEMail_CDO1 = True
> > Exit Function
> >
> > ErrorHandling:
> > MsgBox Err.Description & " Error Number: " & Err.Number
> > Debug.Print Err.Description & " " & Err.Number
> > SendEMail_CDO1 = False
> > Set mail = Nothing
> > Set fields = Nothing
> > Set config = Nothing
> >
> > End Function
> >
> >
> >
From: Sharkbyte on 19 Apr 2010 16:45
Daniel:

Using the code from your link, I get the same Transport error. It would
seem my GMail connection lines are incorrect, but all the information I can
find says that I'm pointing to the right place...

I will keep trying.

Sharkbyte


"Daniel Pineault" wrote:

> Failing? Do you get an error?
>
> You might want to take a look at the following example for inspiration
>
> http://www.worksrite.com/CDOMail.htm
> --
> Hope this helps,
>
> Daniel Pineault
> http://www.cardaconsultants.com/
> For Access Tips and Examples: http://www.devhut.net
> Please rate this post using the vote buttons if it was helpful.
>
>
>
> "Sharkbyte" wrote:
>
> > I have not worked with sending email from Access, before, but now I am
> > attempting to create a function that will generate an email in response to
> > certain user actions.
> >
> > I have cobbled together some code, and I believe it is close to functional.
> > However, I am failing when trying to connect to Google's GMail server.
> >
> > I'm hoping someone can point me in the right direction, or to an How To
> > article...
> >
> > Here is my code:
> >
> > Public Function SendEMail_CDO1(strFrom, strTo, strSubject, strBody,
> > strSmtpServer, strSendUserName, strPassword, Optional strCC) As Boolean
> > On Error GoTo ErrorHandling
> >
> > Dim mail
> > Dim config
> > Dim fields
> > Dim i As Integer
> >
> > Set mail = CreateObject("CDO.Message")
> > Set config = CreateObject("CDO.Configuration")
> > Set fields = config.fields
> >
> > With fields
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> > "smtp.gmail.com"
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
> > True
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
> > = "myemail(a)gmail.com"
> > .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
> > = "xxxx"
> >
> > .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
> > = 15
> > .Update
> > End With
> >
> > Set mail.Configuration = config
> >
> > With mail
> > .From = strFrom
> > .To = strTo
> > .Subject = strSubject
> > .TextBody = strBody
> >
> > If blnAttachedFile = True Then
> > .AddAttachment = strAttachedFile
> > Else
> > End If
> >
> > .Send
> > End With
> >
> > Set mail = Nothing
> > Set fields = Nothing
> > Set config = Nothing
> >
> > SendEMail_CDO1 = True
> > Exit Function
> >
> > ErrorHandling:
> > MsgBox Err.Description & " Error Number: " & Err.Number
> > Debug.Print Err.Description & " " & Err.Number
> > SendEMail_CDO1 = False
> > Set mail = Nothing
> > Set fields = Nothing
> > Set config = Nothing
> >
> > End Function
> >
> >
> >
From: Daniel Pineault on 19 Apr 2010 21:43
It may be coincidence, but the three examples I have found only for using CDO
with gmail use port 465??? Might be worth a try.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Sharkbyte" wrote:

> Daniel:
>
> Using the code from your link, I get the same Transport error. It would
> seem my GMail connection lines are incorrect, but all the information I can
> find says that I'm pointing to the right place...
>
> I will keep trying.
>
> Sharkbyte
>
>
> "Daniel Pineault" wrote:
>
> > Failing? Do you get an error?
> >
> > You might want to take a look at the following example for inspiration
> >
> > http://www.worksrite.com/CDOMail.htm
> > --
> > Hope this helps,
> >
> > Daniel Pineault
> > http://www.cardaconsultants.com/
> > For Access Tips and Examples: http://www.devhut.net
> > Please rate this post using the vote buttons if it was helpful.
> >
> >
> >
> > "Sharkbyte" wrote:
> >
> > > I have not worked with sending email from Access, before, but now I am
> > > attempting to create a function that will generate an email in response to
> > > certain user actions.
> > >
> > > I have cobbled together some code, and I believe it is close to functional.
> > > However, I am failing when trying to connect to Google's GMail server.
> > >
> > > I'm hoping someone can point me in the right direction, or to an How To
> > > article...
> > >
> > > Here is my code:
> > >
> > > Public Function SendEMail_CDO1(strFrom, strTo, strSubject, strBody,
> > > strSmtpServer, strSendUserName, strPassword, Optional strCC) As Boolean
> > > On Error GoTo ErrorHandling
> > >
> > > Dim mail
> > > Dim config
> > > Dim fields
> > > Dim i As Integer
> > >
> > > Set mail = CreateObject("CDO.Message")
> > > Set config = CreateObject("CDO.Configuration")
> > > Set fields = config.fields
> > >
> > > With fields
> > > .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
> > > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") =
> > > "smtp.gmail.com"
> > >
> > > .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
> > > True
> > >
> > > .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
> > > .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")
> > > = "myemail(a)gmail.com"
> > > .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
> > > = "xxxx"
> > >
> > > .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout")
> > > = 15
> > > .Update
> > > End With
> > >
> > > Set mail.Configuration = config
> > >
> > > With mail
> > > .From = strFrom
> > > .To = strTo
> > > .Subject = strSubject
> > > .TextBody = strBody
> > >
> > > If blnAttachedFile = True Then
> > > .AddAttachment = strAttachedFile
> > > Else
> > > End If
> > >
> > > .Send
> > > End With
> > >
> > > Set mail = Nothing
> > > Set fields = Nothing
> > > Set config = Nothing
> > >
> > > SendEMail_CDO1 = True
> > > Exit Function
> > >
> > > ErrorHandling:
> > > MsgBox Err.Description & " Error Number: " & Err.Number
> > > Debug.Print Err.Description & " " & Err.Number
> > > SendEMail_CDO1 = False
> > > Set mail = Nothing
> > > Set fields = Nothing
> > > Set config = Nothing
> > >
> > > End Function
> > >
> > >
> > >
 |  Next  |  Last
Pages: 1 2
Prev: Can I change the yes/no format in an Access table, to New/Existing
Next: Range Selection for Dropdown List