Solved

Access - Application or Object - Defined Error

Posted on 2014-07-25
20
258 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40219443
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
ID: 40219445
what if you save it before .send?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40219448
correction

' place all the codes above here

should read

' place all the codes you posted above here, except the end sub
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:shieldsco
ID: 40219513
Nothing happens
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40219520
Not sure who you are responding to
0
 

Author Comment

by:shieldsco
ID: 40219529
To Rey
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40219551
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
ID: 40219553
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
ID: 40219572
Yes I'm saving before sending
0
 

Author Comment

by:shieldsco
ID: 40219575
Rey - no error nor did it send the email
0
 

Author Comment

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

Expert Comment

by:Helen_Feddema
ID: 40219648
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
ID: 40219663
and if you do a .send after the .save does it send it?
0
 

Author Comment

by:shieldsco
ID: 40219688
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
ID: 40219767
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
ID: 40219802
Ok I will try
0
 

Author Comment

by:shieldsco
ID: 40219830
The code works as look as Outlook is not open
0
 

Author Comment

by:shieldsco
ID: 40219845
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40219836
:-0 omg!!!
0
 

Author Closing Comment

by:shieldsco
ID: 40219846
Sorry about that - I split the points
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 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