Full Version: Error Opening Outlook From Button On Form
UtterAccess Forums > Microsoft® Access > Access Forms
bis2k
Added a button to a subform to open outlook and send specific field information via email. This works on another form so I copied the code.
CODE
Option Compare Database   'Use database order for string comparisons
Option Explicit
'Dim vseqnum As Integer
'Dim colCheckBox As New Collection
Dim vuser As String
Dim eSQL As String
Dim strSQL As String
Dim eTo As String
Dim eCC As String
Dim eSubject As String
Dim eCust As String
Dim eSales1 As String
Dim eSales2 As String
Dim eSales3 As String
Dim eAcctType As String
Dim eProblem As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim objoutlook As New Outlook.Application
Dim objoutlookmsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim recdetect As Variant
Const stSelect As String = "SELECT * FROM qryEmailProblem "
Const conQuote = """"
Public Sub EmailMech()
On Error GoTo Err_Handler
    
   ' eSQL = "WHERE [ProblemID]= " & MySelected
    eSQL = Me.ProblemID
    strSQL = stSelect & eSQL
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    
    
     Dim blRet As Boolean
      blRet = PositionFormRelativeToControl("frmPleaseWait", Me.Label38, 3)
    
    rst.MoveFirst
    Do While Not rst.EOF
      eTo = rst.Fields("Memail")
      eCC = rst.Fields("S1email") & ";" & rst.Fields("S3email") & ";" & "sfeig@soapman.com" & ";" & "husher@soapman.com" & ";" & "bhittner@soapman.com" & ";" & "bclark@soapman.com" & ";" & "cjaramillo@soapman.com" & ";" & "mwebb@soapman.com"
      eSubject = "(" & rst.Fields("Cust Code") & ") Call ID: " & rst.Fields("Call ID") & " Problem #: " & rst.Fields("LineNum") & " Email Code: " & rst.Fields("ProblemID")
      eCust = rst.Fields("Cust")
      eSales1 = rst.Fields("s1name")
      eSales2 = rst.Fields("s2name")
      eSales3 = rst.Fields("s3name")
      eAcctType = rst.Fields("AcctType")
      eProblem = rst.Fields("Problem")
      Set objoutlookmsg = objoutlook.CreateItem(olMailItem)
  
           .To = eTo
           .CC = eCC
           .Subject = eSubject
           .Body = "Customer: " & Chr(13) & Chr(10) & eCust & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Problem: " & Chr(13) & Chr(10) & eProblem & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Salesman 1: " & eSales1 & Chr(13) & Chr(10) & "Salesman 2: " & eSales2 & Chr(13) & Chr(10) & "Salesman 3: " & eSales3 & Chr(13) & Chr(10) & "AcctType: " & eAcctType
           .Send

                
  
      rst.Close
      dbs.Close

when I click the button i receive a msg :
"Application-defined or object-defined error"
Can anyone help guide me as what I am doing wrong?
Thanks
Larry
ipisors
When you click Debug > Compile, in the VBA project, does that err too?
I think the Declarations (Dim ___ as ___) would go inside the Sub, not above it.
could be wrong on this, as you have the Sub inside a form's code, not a standard module (right?) and I don't usually do that.
bis2k
It's Larry again.
Omade a minor change - made sure the salesman field had a value (it was blank)
Now the error msg is:
"Invalid use of Null"
Can you help?
Thanks Larry
ipisors
Please comment out the On Error goto Errhandler line.
When the code errs, please click Debug and let us know which line is highlighted.
bis2k
Thanks everyone for your support. I researched UA and found straight forwrd code to send an email from code
Its basically the same but a little cleaner.
CODE
Dim blRet As Boolean
  
    eSQL = "where [problemid]= " & Me.ProblemID
    strSQL = stSelect & eSQL
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    
      blRet = PositionFormRelativeToControl("frmPleaseWait", Me.cmdEmail, 3)
      
   Set objoutlookmsg = objoutlook.CreateItem(olMailItem)
      eTo = rst.Fields("Memail")
  '    eCC = rst.Fields("S1email") & ";" & rst.Fields("S3email") & ";" & "sfeig@soapman.com" & ";" & "husher@soapman.com" & ";" & "bhittner@soapman.com" & ";" & "bclark@soapman.com" & ";" & "cjaramillo@soapman.com" & ";" & "mwebb@soapman.com"
      eSubject = "(" & rst.Fields("Cust Code") & ") Call ID: " & rst.Fields("Call ID") & " Problem #: " & rst.Fields("LineNum") & " Email Code: " & rst.Fields("ProblemID")
      eCust = rst.Fields("Cust")
      eSales1 = rst.Fields("s1name")
      eSales2 = rst.Fields("s2name")
      eSales3 = rst.Fields("s3name")
      eAcctType = rst.Fields("AcctType")
      eProblem = rst.Fields("Problem")

       Set objoutlookmsg = objoutlook.CreateItem(olMailItem)
          With objoutlookmsg
           .To = eTo
   '        .CC = eCC
           .Subject = eSubject
           .Body = "Customer: " & Chr(13) & Chr(10) & eCust & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Problem: " & Chr(13) & Chr(10) & eProblem & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Salesman 1: " & eSales1 & Chr(13) & Chr(10) & "Salesman 2: " & eSales2 & Chr(13) & Chr(10) & "Salesman 3: " & eSales3 & Chr(13) & Chr(10) & "AcctType: " & eAcctType
           .Send
           End With
      rst.Close
      dbs.Close
   Set objoutlook = Nothing

How when click the button it sends the email, but if I click it a second time I get the error message
"Object invalid or No longer set"
Ocan't figure this one out -can someone help!
Thanks
LArry
ipisors
Please answer the specific questions already asked...so that we can help you
bis2k
I'm sorry I was getting ahead of myself.
compiled the code and received no errors.
Then I commented out the error msg line. don't get an error message, rather I get a popup msgbox with the following message : No current Record
Is that any help?
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.