Results 1 to 7 of 7
  1. #1
    lrobbo314 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    12

    Send E-Mail 2010

    I created an Access Database in 2003 and I got some code from the web that allowed me to send emails to everyone that came up in a query based on a certain criteria. I recreated the database in 2010 and now I am getting an error when it tries to send the email. The error says "Run-time error '438': Object doesn't support this property or method"



    Here is the function...

    Code:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim frm As String
    Dim strSQL As String
    Dim fld As String
    Dim wrw As String
    Dim qDef As DAO.QueryDef
    Dim qName As String
    Dim eThing As String
    Dim res As String
    res = MsgBox("This Will Send An E-Mail To All Agents Who Have Not Completed The Selected Course" & vbCrLf & "Do You Wish To Continue?", vbYesNo, "E-Mail")
    If res = vbNo Then
        Exit Sub
    Else
    fld = Me.cb_Course
    Set db = CurrentDb()
    qName = "qry_VLCEmail"
    strSQL = "SELECT name, Male, female, OutlookEmail as Email"
    frm = "FROM qry_VLCRecords "
    wrw = "WHERE (((qry_VLCRecords.CourseName)='" & Forms!frm_VLCRecords!cb_Course & " '" & ") AND ((qry_VLCRecords.Complete)=False) AND ((qry_VLCRecords.OutlookEmail) Is Not Null))" & ";"
    eThing = strSQL & vbNewLine & frm & vbNewLine & wrw
    Set qDef = db.QueryDefs("qry_VLCRecords")
    qDef.Parameters(0) = Forms!frm_VLCRecords!cb_Course
    Set rst = qDef.OpenRecordset(dbOpenSnapshot)
    '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
    Dim MailList As DAO.Recordset
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Dim Subjectline As String
    Dim BodyFile As String
    Dim fso As FileSystemObject
    Dim MyBody As TextStream
    Dim MyBodyText As String
    Dim TrainingName As Variant
    Dim TrainingDateStart As Variant
    Dim TrainingDateEnd As Variant
    Dim CourseName As Variant
    Set fso = New FileSystemObject
        Dim blnSuccessful As Boolean
        Dim strHTML As String
     
    MyBodyText = "Our Records indicate that you have yet to complete " & fld
    Set db = CurrentDb()
    Subjectline = "Incomplete VLC Training"
    Set MailList = rst
     
    ' now, this is the meat and potatoes.
    ' this is where we loop through our list of addresses,
    ' adding them to e-mails and sending them.
     
    Do Until MailList.EOF
    If MailList("Male") = -1 Then
        strHTML = "Sir," & vbNewLine & vbNewLine & MyBodyText
        blnSuccessful = FnSafeSendEmail(MailList("outlookemail"), Subjectline, strHTML)
    Else
        strHTML = "Ma'am," & vbNewLine & vbNewLine & MyBodyText
        blnSuccessful = FnSafeSendEmail(MailList("outlookemail"), Me.cb_Course, strHTML)
    End If
        'A more complex example...
        'blnSuccessful = FnSafeSendEmail( _
                            "body", _
                            "subject", _
                            strHTML, _
                            "attachment.txt")
    MailList.MoveNext
    Loop
        If blnSuccessful Then
        
            MsgBox "E-mail message(s) sent successfully!"
            
        Else
        
            MsgBox "Failed to send e-mail!"
        
        End If
    End If
    Maybe there is an easier way to accomplish this goal in 2010 that I'm not aware of.

    Any help would be greatly appreciated.

    Thanks.

  2. #2
    lrobbo314 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    12
    Sorry, that first part is the code that calls this function

    Code:
    'This is the procedure that calls the exposed Outlook VBA function...
    Public Function FnSafeSendEmail(strTo As String, _
                        strSubject As String, _
                        strMessageBody As String, _
                        Optional strAttachmentPaths As String, _
                        Optional strCC As String, _
                        Optional strBCC As String) As Boolean
        Dim objOutlook As Object ' Note: Must be late-binding.
        Dim objNameSpace As Object
        Dim objExplorer As Object
        Dim blnSuccessful As Boolean
        Dim blnNewInstance As Boolean
        
        'Is an instance of Outlook already open that we can bind to?
        On Error Resume Next
        Set objOutlook = GetObject(, "Outlook.Application")
        On Error GoTo 0
        
        If objOutlook Is Nothing Then
        
            'Outlook isn't already running - create a new instance...
            Set objOutlook = CreateObject("Outlook.Application")
            blnNewInstance = True
            'We need to instantiate the Visual Basic environment... (messy)
            Set objNameSpace = objOutlook.GetNamespace("MAPI")
            Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
            objExplorer.CommandBars.FindControl(, 1695).Execute
                    
            objExplorer.Close
                    
            Set objNameSpace = Nothing
            Set objExplorer = Nothing
            
        End If
        blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                    strSubject, strMessageBody, _
                                                    strAttachmentPaths)
                                    
        If blnNewInstance = True Then objOutlook.Quit
        Set objOutlook = Nothing
        
        FnSafeSendEmail = blnSuccessful
        
    End Function
    and the error comes at this line

    Code:
    blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                    strSubject, strMessageBody, _
                                                    strAttachmentPaths)

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does it work if you delete "objOutlook." from the call? The error is telling you that the function is not a property or method of the Outlook object (which it isn't).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    lrobbo314 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    12
    I tried your suggestion and I ran into a new problem. The function is

    Code:
    Public Function FnSafeSendEmail(strTo As String, _
                        strSubject As String, _
                        strMessageBody As String, _
                        Optional strAttachmentPaths As String, _
                        Optional strCC As String, _
                        Optional strBCC As String) As Boolean
    which says "FnSafeSendEmail"

    and the part where you had me take out "objOutlook" said "FnSendMailSafe". So, just taking out the objOutlook it tells me "Sub is not defined" and highlights the same line as before. And if I change it from "FnSendMailSafe" to "FnSafeSendEmail" then obviously the function just keeps looping back to the beginning.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I was unfamiliar with that function, but a quick Google makes me think it came from here:

    http://www.everythingaccess.com/tuto...curity-Warning

    I note the highlighted area that says the solution doesn't work with Outlook 2010.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    lrobbo314 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    12
    Aha! Well, thanks for the help anyway. I actually was able to modify the code in a slighlty different way and it works perfectly. It doesn't actually send the email, but it does open it up in outlook with all of the recipients and body text, which is actually better because I can review it to make sure that it is squared away.

    For anyone who may run into this problem, this was my solution...

    Code:
    Function mOrder()
    On Error GoTo ErrorHere
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim fld As String
    Dim wrw As String
    Dim qDef As DAO.QueryDef
    Dim qName As String
    Dim eThing As String
    
    fld = Me.cb_Course
    Set db = CurrentDb()
    qName = "qry_VLCEmail"
    strSQL = "SELECT name, Male, female, OutlookEmail as Email"
    frm = "FROM qry_VLCRecords "
    wrw = "WHERE (((qry_VLCRecords.CourseName)='" & Forms!frm_VLCRecords!cb_Course & " '" & ") AND ((qry_VLCRecords.Complete)=False) AND ((qry_VLCRecords.OutlookEmail) Is Not Null))" & ";"
    eThing = strSQL & vbNewLine & frm & vbNewLine & wrw
    Set qDef = db.QueryDefs("qry_VLCRecords")
    qDef.Parameters(0) = Forms!frm_VLCRecords!cb_Course
    Set rst = qDef.OpenRecordset(dbOpenSnapshot)
    rst.MoveFirst
    Dim olApp As New Outlook.Application, olNameSpace As Outlook.Namespace
    Dim myItem As Outlook.MailItem
    Set olApp = Outlook.Application
    Set olNameSpace = GetNamespace("MAPI")
    Set myItem = olApp.CreateItem(olMailItem)
    With myItem
    .Importance = olImportanceHigh
        Do Until rst.EOF
            .To = .To & rst("OutlookEmail") & ";"
            rst.MoveNext
        Loop
    .Body = "ALCON," & vbCrLf & vbCrLf & "Our Records indicate that you have yet to complete " & fld
    .Display
    End With
    ExitHere:
    Set olApp = Nothing
    Set olNameSpace = Nothing
    Set myItem = Nothing
    Exit Function
    ErrorHere:
    MsgBox Err.Description
    Resume ExitHere
    End Function

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out. A lot of that original code was to get around the Outlook security warnings, which you don't need if you're going to let the user send it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. VBA to Send E-mail to Addresses from Query
    By alpinegroove in forum Programming
    Replies: 4
    Last Post: 12-23-2011, 09:45 AM
  2. Send e-mail with info from Report
    By mari_hitz in forum Import/Export Data
    Replies: 22
    Last Post: 12-17-2011, 06:24 PM
  3. send a form via mail
    By Fabdav in forum Forms
    Replies: 1
    Last Post: 10-12-2011, 07:35 AM
  4. Automatically Send E-Mail from Access
    By lilygtg in forum Access
    Replies: 1
    Last Post: 08-24-2011, 05:48 PM
  5. Send mail to the chosen ones
    By carstenhdk in forum Import/Export Data
    Replies: 0
    Last Post: 05-18-2010, 11:51 PM

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