Results 1 to 11 of 11
  1. #1
    Colleen2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    26

    Company changing from Outlook to Gmail - Need assistance on coding for sending emails

    Below is my current coding in my module that sends emails via Outlook but we are switching to gmail. I tried updating to Gmail but it was still looking for outlook and not google/gmail.

    Option Compare Database
    Public strRptFilter As String
    Public blnRepIDHasData As Boolean




    Public Function IsOutlookOpen() As Boolean
    On Error Resume Next


    IsOutlookOpen = True

    Set oOutlook = GetObject(, "Outlook.Application")
    If oOutlook Is Nothing Then
    IsOutlookOpen = False
    End If


    End Function




    Function Email_Via_Outlook(varAddress, varSubject, varBody, varCC, DisplayMsg As Boolean, blnDoNotCC As Boolean, Optional AttachmentPath)
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    On Error GoTo errorHandler


    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
    ' Add the To recipient(s) to the message.


    Set objOutlookRecip = .Recipients.Add(varAddress)
    objOutlookRecip.Type = olTo
    .Subject = varSubject
    .Body = varBody
    'If NoCC is checked, bypass the addition of the cc addresses.
    If blnDoNotCC = False Then
    .CC = varCC
    End If

    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) And AttachmentPath <> "" Then
    Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    Next
    ' Should we display the message before sending?
    If DisplayMsg Then
    .Display
    Else
    .Save
    .Send
    End If
    End With
    Set objOutlook = Nothing
    Exit Function
    errorHandler:
    errNameFrom = "Email_Via_Outlook"
    MsgBox "Error occured at " & errNameFrom & ": " & Err.Number & "-" & Err.Description

    End Function




    Function CheckName(fileName As String) As String
    Dim pos As Long

    pos = InStrRev(fileName, ".")
    If (pos > 0) Then
    fileName = Left$(fileName, pos - 1)
    End If
    CheckName = fileName & ".xlsx"

    End Function

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you just use:
    DoCmd.SendObject acSendQuery, "qsQuery", acFormatXLS, "name@aol.com", , , "Subject", "message"
    or

    DoCmd.SendObject acSendReport, "rMyReport", acFormatPDF, "name@aol.com", , , "Subject", "message"

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Wouldn't it be more logical to show the code that does not work, rather than the code that did work?
    I would expect you might need to use CDO now as well.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Colleen2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    26
    The database send several emails each day based on daily data - so that won't work

  5. #5
    Colleen2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    26
    On the module I just tried changing outlook to Gmail, which didn't work - I made no other changes

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Look up CDO mail in that case.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Colleen2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    26
    ok i will try that

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Colleen2 View Post
    ok i will try that
    https://www.google.com/search?q=cdo....hrome&ie=UTF-8
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    usfarang is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2021
    Posts
    42

    Email Demo

    This is not my code but can help you figure it out. Please see attached demo.
    hth
    Attached Files Attached Files

  10. #10
    Colleen2 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    26
    Thank you for the information, I will give that a try

  11. #11
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    I used this successfully in the past:
    Dim strpath AsString
    Dim stDocName AsString
    Dim mypath As String
    strpath = "C:\Invoices"
    mypath = strpath &"invoice.pdf"
    Debug.Print mypath
    Me.invoicepath = mypath

    MyFileName = mypath
    emailfile = CStr(mypath &".pdf")

    stDocName ="rptinvoice"
    DoCmd.OutputToacReport, stDocName, acFormatPDF, mypath, False

    Dim msg As Object

    Set msg =CreateObject("CDO.Message")
    msg.From =Forms![mainProcessEntry]![UserEmail]
    msg.To =[Forms]![mainProcessEntry]!invoiceemail
    msg.Subject = "Your Invoice isAttached"
    msg.textbody = "Thank you!"
    msg.CC = ""
    msg.BCC = ""
    msg.ReplyTo = strMailFrom
    msg.AddAttachment"c:\invoices\invoice.pdf"

    msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver")= "smtp.gmail.com"
    msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport")= 465
    msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing")= 2
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername")= Forms![mainProcessEntry]![UserEmail]
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")= Forms![mainProcessEntry]![password]
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl")= True
    msg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")= 1
    msg.Configuration.Fields.Update

    msg.HTMLBody ="<font size='2' face='Verdana, Arial, Helvetica, sans-serif'>"
    msg.HTMLBody ="Thank you!"
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2"" color=""blue"">"

    msg.HTMLBody =msg.HTMLBody & "<img src='c:\pss\quantumprocesslogo.jpg' height=57width=192><br>"***We've removed this as of 3/9/16
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2""color=""blue""><b><BR>http ://www.companyname.com</b></font>" ***Add your company's signature here
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2"" color=""black""companyname</font><BR>"
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2"" color=""black"">ADDRESS</font><BR>"***Address goes here etc...
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2""color=""black"">CITY, STATE,ZIP</font><BR>"
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2"" color=""black"">PhoneNumber</font><BR>"
    msg.HTMLBody =msg.HTMLBody & "<font face=""arial""size=""2"" color=""black"">FAX Number fax</font><BR>"
    msg.HTMLBody =msg.HTMLBody & Forms![mainProcessEntry]![UserEmail]
    msg.HTMLBody =msg.HTMLBody & "<font face=""Arial""size=""2""color=""green""><u>" & SW &"</u></font><BR>"
    msg.HTMLBody =msg.HTMLBody & "<font face=""Arial""size=""2"" color=""blue"">"



    msg.Send

    Exit Sub

    errHandler:
    MsgBox "Error " & Err.Number& ": " & Err.Description & " in " & _
    VBE.ActiveCodePane.CodeModule,vbOKOnly, "Error"

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-18-2022, 06:42 AM
  2. Replies: 3
    Last Post: 06-19-2016, 07:46 AM
  3. Sending Emails from Access via Outlook
    By Terry Lawson in forum Programming
    Replies: 3
    Last Post: 11-14-2014, 10:03 AM
  4. Sending several emails causes Outlook crash
    By mercapto in forum Programming
    Replies: 3
    Last Post: 06-14-2013, 07:30 AM
  5. Sending Emails and VBA coding
    By lucy1216 in forum Programming
    Replies: 4
    Last Post: 05-20-2013, 05:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums