Trying to Create Email Function

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")
= "
.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

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")
= "
.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

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")
= "
.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

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")
= "
.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

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")
= "
.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

Here is a CDO example explicitly for gmail hopefully it can help.

http://www.a1vbcode.com/snippet-3691.asp
--
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")
= "
.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

2 more samples to check out

http://www.ozgrid.com/forum/showthread.php?t=84628
http://codingsense.wordpress.com/200...-using-vb-6-0/
--
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")
= "
.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

Daniel:

Thank you for sticking with this one. This sample code worked.

It appears I wasn't calling Authentication and SSL correctly, so the server
wouldn't accept my request.

Thanks again.

Sharkbyte


"Daniel Pineault" wrote:

Here is a CDO example explicitly for gmail hopefully it can help.

http://www.a1vbcode.com/snippet-3691.asp
--
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")
= "
.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