Code for Gmail From Access

Code for Gmail From Access

This Code allows you to send email directly from Microsoft Access, Excel, word, etc with VBA. Since I wrote this code Google have updated the way you access their Gmail system. You might have to jump through hoops to get it working! If you can give me any advice on what you did so that I can tell other people how to do it then please contact me!

					Option Compare Database
Option Explicit
'>>> Database by Tony Hine, alias Uncle Gizmo                                                  <<<
'>>> Created Mar, 2011                                                                         <<<
'>>> Last up-dated Mar, 2015                                                                   <<<
'>>> Telephone International: +44 7747 018 875                                                 <<<
'>>> Telephone UK: 07747 018 875                                                               <<<
'>>> e-mail:                                                            <<<
'>>> Skype: unclegizmo                                                                         <<<
'>>> I post at the following forum (mostly) :                                                  <<<
'>>>  (alias Uncle Gizmo)                          <<<
'>>> You can also find me on the Google+:     <<<
'>>> If my e-mail don't work, try this website:     <<<
'>>> I now have a website which contains video instructions here:                              <<<
'>>>                     <<<
'>>> CODE SUPPLIED NOT CHECKED AND TESTED FOR ERRORS!!!! Be Warned                             <<<

Private Sub btnSendEmailTest_Click()
If IsNull(txtGmailFrom) Or txtGmailFrom = "" Or IsNull(txtGmailPassword) Or txtGmailPassword = "" Or IsNull(txtGmailTo) Or txtGmailTo = "" Then
    MsgBox "ALL Fields MUST be Filled"
    Exit Sub
End If

    MsgBox "True means the Message was sent >>> = " & fSendGmail, , "Send Gmail from MS Access by Tony Hine"
End Sub      'btnSendEmailTest_Click

Private Function fSendGmail() As Boolean 'Returns True if No Errors are Generated
On Error GoTo Err_ErrorHandler
fSendGmail = True

'THIS Example FROM:
'Windows® XP Under the Hood
'Pg. 266 Chapter 6 Messaging and Faxing Objects

'This basic example sends a simple, no-frills text message every time the script is run:
'Example File script0603.vbs

'Essential WAP™ for Web Professionals
'Pg. 96 Chapter 8. Example ASP Application > The SendMail() Function

'The SendMail() Function
'While longer, SendMail( ) is itself a simpler function than GetData( ) . It simply creates three
'objects: CDO. Message, CDO. Configuration, and a subobject of CDO. Configuration called
'Fields . The Scripting library used in GetData() is a default part of the ASP namespace, and
'therefore any new object created in the Scripting library is known. To use objects in the CDO
'library, the METADATA statements at the top of the ASP page are necessary.

'Standard CDO Constants
'NOTE --- If you set conCdoSmtpUseSSL to True, you may need to set conCdoSendUsingPort to 465 or port number specified by your ISP.
Const conStrPrefix As String = ""
Const conCdoSendUsingPort As Integer = 2    'If incorrect raises this Error: -2147220960
'Const conSendPassword As String = "YourGmailPasswordHere"
Const conCdoBasic As Integer = 1
'Const conSendUserName As String = ""
Const conStrSmtpServer As String = ""     'If incorrect raises this Error: -2147220973
Const conCdoSmtpUseSSL As Boolean = True    'Use Secure Sockets Layer (SSL) when posting via SMTP.
Const conCdoSmtpServerPort As Integer = 465 'Can be 465 or 587 'If incorrect raises this Error: -2147220973

Dim oMsg As Object
Dim oConf As Object

Dim strEmailAddr As String
strEmailAddr = txtGmailTo & ">"

'Create Objects
Set oMsg = CreateObject("CDO.Message")
Set oConf = CreateObject("CDO.Configuration")
Set oMsg.Configuration = oConf

'Build the Message
With oMsg
    .to = "Testing <" & strEmailAddr       'If incorrect you will get an email From: Delivery Status Notification (Failure) Delivery to the following recipient failed permanently:
    .From = "From TextBox "    'If incorrect raises this Error: -2147220973
    .subject = "From Keyed in Email Address"
    .textBody = "From Keyed in Email Address"
    '.AddAttachment "H:\ATHDrive\ATH_Programming\ATH_Office\Access2007\My_MS_Access_Tools\GoogleEmail\TransscriptGmailFromVBA.txt"
End With
''Set Delivery Options
            With oConf.Fields
                .Item(conStrPrefix & "sendusing") = conCdoSendUsingPort
                .Item(conStrPrefix & "smtpserver") = conStrSmtpServer
                .Item(conStrPrefix & "smtpauthenticate") = conCdoBasic
                .Item(conStrPrefix & "sendusername") = txtGmailFrom
                .Item(conStrPrefix & "sendpassword") = txtGmailPassword
                '.Item(conStrPrefix & "sendusername") = conSendUserName 'IF you want to hard code the username you can reactivate this line.
                '.Item(conStrPrefix & "sendpassword") = conSendPassword 'IF you want to hard code the password you can reactivate this line.
                .Item(conStrPrefix & "smtpusessl") = conCdoSmtpUseSSL
                .Item(conStrPrefix & "smtpserverport") = conCdoSmtpServerPort
                .Update 'Commit Changes
            End With

'Deliver the Message

'Access 2007 Developer Reference > Microsoft Data Access Objects (DAO) Reference > DAO Reference > Recordset Object > Methods
'An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).
    Set oMsg.Configuration = Nothing
    Set oConf = Nothing
    Set oMsg = Nothing
    Exit Function

    If Err.Number <> 0 Then fSendGmail = False
        Select Case Err.Number

            Case -2147220977  'Likely cause, Incorrectly Formatted Email Address, server rejected the Email Format
                MsgBox "Error From --- fSendGmail --- Incorrectly Formatted Email ---  Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , "Format the Email Address Correctly"

            Case -2147220980  'Likely cause, No Recipient Provided (No Email Address)
                MsgBox "Error From --- fSendGmail --- No Email Address ---  Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , "You Need to Provide an Email Address"

            Case -2147220960 'Likely cause, SendUsing Configuration Error
                MsgBox "Error From --- fSendGmail --- The SendUsing configuration value is invalid --- LOOK HERE >>> sendusing) = conCdoSendUsingPort ---  Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , "SendUsing Configuration Error"
            Case -2147220973  'Likely cause, No Internet Connection
                MsgBox "Error From --- fSendGmail --- No Internet Connection ---  Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , "No Internet Connection"
            Case -2147220975  'Likely cause, Incorrect Password
                MsgBox "Error From --- fSendGmail --- Incorrect Password ---  Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , "Incorrect Password"
            Case Else   'Report Other Errors
                MsgBox "Error From --- fSendGmail --- Error Number >>>  " & Err.Number _
                & "  <<< Error Description >>  " & Err.Description
        End Select
    Resume Exit_ErrorHandler
End Function      'fSendGmail

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.