none
Opening an email template in Outlook using Access control #2

    Question

  • I am using the follow:

    Private Sub Command9_Click()

        Dim olApp As New Outlook.Application
        Dim olMailItem As Outlook.MailItem
     
        Set olMailItem = olApp.CreateItem(olMailItem)

        With olMailItem
            .To = MessageTo
            .Subject = Subject
            .Body = MessageBody
            .Display
        End With

        Set olMailItem = Nothing
        Set olApp = Nothing

    End Sub

    and receive the following error:  Run-time Error '91':  Object Variable or With Block variable not set

    Can someone tell me what I need to do in order to run this code properly?

    Wednesday, December 18, 2013 10:41 PM

Answers

  • NoviceVBAuser1775,

    Your code is ok but line str1 is wrong becouse you must escape " in string:

    str1 = "INSERT INTO tblTracking ( irsTaskerID, memDescription, dtmDateCompleted ) " & _
               "SELECT Forms![Task Details-A].[idstaskersID] AS Expr1, DLookup(""[Last Name]"", ""[Contacts]"", ""[idsContactsID] = [chrPersonOfInterest]"") AS Expr2, Now() As Expr3 "
      

    , but you query 3x  for the one record, use recordset:

    Private Sub Send_Email_Click()
    
        Dim MessageTo As String
        Dim SubjectTo As String
        Dim myPerson As Integer
        
        Dim olTaskerID As String
        Dim olName As String
        Dim sql as String
        Dim rs as DAO.Recordset
        
        'Get data for mail
        sql = "Select [Last Name] as LName,Nz([E-mail Address],""NO EMAIL FOR CONTACT"")  as email From Contacts where [idsContactsID] = chrPersonOfInterest"
        Set rs = CurrentDB.OpenRecordset(sql)
        
        if not rs.eof Then        
            
            'Prep data for email
            olName = rs!LName
            MessageTo = rs!email
            SubjectTo = "Department of Defense, Office of the Inspector General"
    
            'Open Outlook Email        
            Set myOlApp = CreateObject("Outlook.Application")
            Set myItem = myOlApp.CreateItem(OlItemType.olMailItem)
            
            With myItem
                .To = MessageTo
                .Subject = SubjectTo
                .Display
            End With
            sql = "INSERT INTO tblTracking ( irsTaskerID, memDescription, dtmDateCompleted ) VALUES " & _
               "(" & Forms![Task Details-A].[idstaskersID] & ", """ & olName & """, Now())"    
            DoCmd.RunSQL sql
        End If
        
        'clear
        rs.close
        set rs = nothing
    


    Michał

    Thursday, December 19, 2013 10:07 PM

All replies

  •     Dim olApp       As Outlook.Application
        Dim olMailItem  As Outlook.MailItem

        Set olApp = New Outlook.Application
        Set olMailItem = olApp.CreateItem(OlItemType.olMailItem)

        With olMailItem
            .To = "user@domain.com"
            .Subject = "mySubject"
            .Body = "myBody"
            .Display
        End With

        Set olMailItem = Nothing
        Set olApp = Nothing


    -Tom. Microsoft Access MVP

    Thursday, December 19, 2013 3:34 AM
  • Hello NoviceVBAuser1775,

    It looks like you attempted to use an object variable that isn't yet referencing a valid object. Did you try to debug? What line of code causes an error message?

    Anyway, you can read more about the run-time error #91 in the Visual Basic for Applications Reference section of MSDN.

    Thursday, December 19, 2013 11:54 AM
  • Thanks, that worked.  I guess I missed referenceing the object.

    another questions, I am doing this with the function:


    Private Sub Send_Email_Click()

        Dim MessageTo As String
        Dim SubjectTo As String
        Dim myPerson As Integer
       
        Dim olTaskerID As String
        Dim olName As String
           
        Dim str1 As String
       
        str1 = "INSERT INTO tblTracking ( irsTaskerID, memDescription, dtmDateCompleted ) " & _
               "SELECT Forms![Task Details-A].[idstaskersID] AS Expr1, DLookup("[Last Name]", "[Contacts]", "[idsContactsID] = [chrPersonOfInterest]") AS Expr2, Now() As Expr3 "
           
        olName = DLookup("[Last Name]", "Contacts", "[idsContactsID] = chrPersonOfInterest")
       
        'Open Outlook Email
        MessageTo = Nz(DLookup("[E-mail Address]", "Contacts", "[idsContactsID] = chrPersonOfInterest"), "NO EMAIL FOR CONTACT")
        SubjectTo = "Department of Defense, Office of the Inspector General"
           
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(OlItemType.olMailItem)
       
        With myItem
            .To = MessageTo
            .Subject = SubjectTo
            .Display
        End With
       
        DoCmd.RunSQL str1

    But I keep getting a Compile Error:  Expected:  end of statement

    I belive it is with my use of quotations, but I do not know how to remedy.  Would you be able to provide more insight?

    Thursday, December 19, 2013 4:44 PM
  • Either remove the  _  continuation from the row, or make it all one continuation; and remember that each open quote will require a matching close quote.



    Thursday, December 19, 2013 6:13 PM
  • getting there, I was able to make the above work with the assistance provided, but with I replaced the [chrPersonOfInterest] with text  (like SI) i again get an error.  I am sure it is with the quotation, but I can not seem to get it to work.   Can anyone help with this?

    Thursday, December 19, 2013 9:42 PM
  • NoviceVBAuser1775,

    Your code is ok but line str1 is wrong becouse you must escape " in string:

    str1 = "INSERT INTO tblTracking ( irsTaskerID, memDescription, dtmDateCompleted ) " & _
               "SELECT Forms![Task Details-A].[idstaskersID] AS Expr1, DLookup(""[Last Name]"", ""[Contacts]"", ""[idsContactsID] = [chrPersonOfInterest]"") AS Expr2, Now() As Expr3 "
      

    , but you query 3x  for the one record, use recordset:

    Private Sub Send_Email_Click()
    
        Dim MessageTo As String
        Dim SubjectTo As String
        Dim myPerson As Integer
        
        Dim olTaskerID As String
        Dim olName As String
        Dim sql as String
        Dim rs as DAO.Recordset
        
        'Get data for mail
        sql = "Select [Last Name] as LName,Nz([E-mail Address],""NO EMAIL FOR CONTACT"")  as email From Contacts where [idsContactsID] = chrPersonOfInterest"
        Set rs = CurrentDB.OpenRecordset(sql)
        
        if not rs.eof Then        
            
            'Prep data for email
            olName = rs!LName
            MessageTo = rs!email
            SubjectTo = "Department of Defense, Office of the Inspector General"
    
            'Open Outlook Email        
            Set myOlApp = CreateObject("Outlook.Application")
            Set myItem = myOlApp.CreateItem(OlItemType.olMailItem)
            
            With myItem
                .To = MessageTo
                .Subject = SubjectTo
                .Display
            End With
            sql = "INSERT INTO tblTracking ( irsTaskerID, memDescription, dtmDateCompleted ) VALUES " & _
               "(" & Forms![Task Details-A].[idstaskersID] & ", """ & olName & """, Now())"    
            DoCmd.RunSQL sql
        End If
        
        'clear
        rs.close
        set rs = nothing
    


    Michał

    Thursday, December 19, 2013 10:07 PM