Solved

Access - Application or Object - Defined Error

Posted on 2014-07-25
20
253 Views
Last Modified: 2014-07-25
I receive run time error 287 Application or Object-Defined Error when trying to execute the following code - the error occurs on the .send line --- when I use .display the code executes fine and opens the  email in the display mode, however I would like to send the email automactically --- Any thoughts on how to resolve the problem. Thanks

Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
Dim varEmail As String
xlPath = "\\cdc\project\OD_FMO_Systems_Branch\Systems Metrics Database\Email\Email_6_Days.xlsx"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblEmail")

Do Until rs.EOF
  If rs("email") & "" <> "" Then
       varEmail = rs("email")
           sMail = sMail & ";" & Left(varEmail, InStr(varEmail, "#") - 1)
   End If
    rs.MoveNext
   
 
   
Loop

sMail = Mid(sMail, 2)

Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

 

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
   

         .Subject = sSubj & "Help Desk Tickets Approching 6 Day Target"
         .Body = sBody & "This is a test for automated emails - Service Desk Tickets Approching The 6 Day Target - See Attachment"
      .Attachments.Add xlPath

      '.display
            .send
           
End With

End Sub
0
Comment
Question by:shieldsco
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
try adding error handling procedure to your codes


on error goto SendError_Proc

' place all the codes above here


Exit_Send:
     exit sub

SendError_Proc:
    select case err.number
          case 287
          err.clear
          resume Exit_Send
   case else
         msgbox err.description
         resume Exit_Send

end sub

Open in new window

0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
what if you save it before .send?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
correction

' place all the codes above here

should read

' place all the codes you posted above here, except the end sub
0
 

Author Comment

by:shieldsco
Comment Utility
Nothing happens
0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
Not sure who you are responding to
0
 

Author Comment

by:shieldsco
Comment Utility
To Rey
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
pls. be explicit in your comment <Nothing happens >

are you still getting the error?
was the mail sent?, send it to your self so you will know.
0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
His will just continue execution without displaying anything including the error. Did you try the the .save before the .send?
0
 

Author Comment

by:shieldsco
Comment Utility
Yes I'm saving before sending
0
 

Author Comment

by:shieldsco
Comment Utility
Rey - no error nor did it send the email
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:shieldsco
Comment Utility
Randy when I use the .save it goes to Draft folder
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
Try writing your concatenated string to the Immediate Window using a Debug.Print statement.  Also, check that the file path and name is correct.  You are using a variant to get the email addresses.  It might be best to check whether there is an email in that field, and set a String variable to it if found, and go to the next record if not.
0
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 250 total points
Comment Utility
and if you do a .send after the .save does it send it?
0
 

Author Comment

by:shieldsco
Comment Utility
It sends to the Drafts Folder

Here's the code:
On Error GoTo SendError_Proc
Dim objOutlook As Object, objEmailMessage As Object
Dim sSubj As String, sBody As String, sTo As String, strCC As String
Dim xlPath As String
Dim varEmail As String
xlPath = "\\cdc\project\OD_FMO_Systems_Branch\Systems Metrics Database\Email\Email_6_Days.xlsx"

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblEmail")

Do Until rs.EOF
  If rs("email") & "" <> "" Then
       varEmail = rs("email")
           sMail = sMail & ";" & Left(varEmail, InStr(varEmail, "#") - 1)
   End If
    rs.MoveNext
   
 
   
Loop

sMail = Mid(sMail, 2)

Set objOutlook = CreateObject("Outlook.Application")
Set objEmailMessage = objOutlook.CreateItem(0)

 

With objEmailMessage
         .To = sMail
         If strCC & "" <> "" Then
                    .CC = strCC
         End If
   

         .Subject = sSubj & "Help Desk Tickets Approching 6 Day Target"
         .Body = sBody & "This is a test for automated emails - Service Desk Tickets Approching The 6 Day Target - See Attachment"
      .Attachments.Add xlPath

     '.display
     .Save
            .send
           
End With

'DoCmd.Quit


Exit_Send:
     Exit Sub

SendError_Proc:
    Select Case Err.Number
          Case 287
          Err.Clear
          Resume Exit_Send
         
         
   Case Else
         MsgBox Err.Description
         Resume Exit_Send
   End Select
End Sub
0
 
LVL 21

Expert Comment

by:Randy Poole
Comment Utility
From what I can see, outlook just does not have enough time to send the message.  You would need to create global instances of the outlook app then initiate a timer and verify that the outbox is empty before continuing.  That is why when you do display it works, gives the application time to send it.
0
 

Author Comment

by:shieldsco
Comment Utility
Ok I will try
0
 

Author Comment

by:shieldsco
Comment Utility
The code works as look as Outlook is not open
0
 

Author Comment

by:shieldsco
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for shieldsco's comment #a40219586
Assisted answer: 500 points for Rey Obrero's comment #a40219443

for the following reason:

Thanks Guys
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
:-0 omg!!!
0
 

Author Closing Comment

by:shieldsco
Comment Utility
Sorry about that - I split the points
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now