Send Email from Excel 2007 causes run-time error 287

Hello Experts,
I have a scheduled task on a Server 2008 R2 system that opens an Excel spreadsheet.  VBA in the spreadsheet reads some sql data, creates some csv files, then emails a summary.  This was all working fine with our onsite Exchange Server 2003.  We just updated to Office 365 so now our exchange is hosted by Microsoft.  The original email code used the CDO method which did not require an interface to Outlook.  Since that does not work with Exchange 2010, I changed the code to send the email through Outlook 2007.  I configured the mail profile under the administrator account to connect to the proper account on Office 365.  In Outlook \ Tools \ Trust Center \ Program Access it is set to "Never warn me about suspicious activity".

So here's the problem.  If I have Outlook open, the email is sent with no problem.  If Outlook is not opened first, I get an error:  Run-time error '287': Application-defined or object-defined error.

Here is the send email code:
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .To = ThisWorkbook.Sheets("Setup").Cells(19, 2).Value
        .CC = ThisWorkbook.Sheets("Setup").Cells(20, 2).Value
        .Subject = "Sales for " & Format(ThisWorkbook.Sheets("Setup").Cells(3, 2).Value, "Long Date")
        .Body = strbody
        .send
    End With
If I debug the error the .send is highlighted.

Here's the question: How do I configure Outlook 2007, Excel 2007 and / or change the VBA code so this works without error when started by a Scheduled Task?  I don't have to send the email through Outlook so I am open to other methods to do the send

Thanks for your help.




wrm-duAsked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
Oh and  you would need to change line 11 to
   Set oFld = oApp.GetNamespace("MAPI").GetDefaultFolder(6)
 

Open in new window

as the default outlook constants won't be defined.
0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
before the code put
on error resume next

and try again , its will not crashed
0
 
wrm-duAuthor Commented:
Thanks for your reply.
I added the code and ran a test.  The error did not appear, but an email was not not sent.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
ok , try this code :


Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Trying to send email using VBA"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using VBA !!!!"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

Open in new window

0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
look that you need to change :
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Trying to send email using VBA"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"

to your values like thisWorkbook.Sheets("Setup").Cells(19, 2).Value
0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
note that the format of cells should be text to let vba get the value as text or using
text$(ThisWorkbook.Sheets("Setup").Cells(19, 2).Value) this will convert it to text
0
 
wrm-duAuthor Commented:
The code works without error if Outlook is started first.  The content of the referenced cell is text.  It seems to me that if the text qualifier was causing the problem I would still get the error even when Outlook is started first.
Thanks
0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
its should not start outlook , its strange .. vba must call outlook not you , i guess its security issue with excel maybe some add-ins disabled in excel that belongs to outlook
0
 
wrm-duAuthor Commented:
I will try your test code and let you know how it goes.
Thanks
0
 
wrm-duAuthor Commented:
I entered the sample code provided and ran a test.  The same problem occurs.  If I start Outlook first, the email sends without error.  If I don't start Outlook, the message box appears that says:  "Application-defined or object-defined error"

I think it is a security issue with Outlook but I don't know how to fix it.

Thanks for your help.
0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
why you don't thinking about VBscript used in VBA?
try this code its will work without outlook :
you should replace the line :
 server = " -SMTP smtp.sender.com -PORT 25" , with the SMTP defined on your outlook.  just change smtp.sender.com , to either your SMTP email or by IP address its will work.

Sub sending_email()
  ' Send Excel e-mail and output the result.

  Dim server
  Dim subj
  Dim body
  Dim command

  ' Define all email parameters.

  server = " -SMTP smtp.sender.com -PORT 25"
  subj = "EXCEL e-mail"

  body = """This is <I>HTML / MIME</I> e-mail message "
  body = body & "sent from MS EXCEL VB script<BR>"
  body = body & "using <B>Febooti Command line email</B>"""

  command = "febootimail.exe -HTML -FROM excel-macro@sender.com "
  command = command & "-TO john@recipient.com "
  command = command & "-SUBJ " & subj & " -BODY " & body & server

  ' Passing one long line to Scripting object.
  ' If there is need to add additional parameters, do it before.

  Set wsShell = CreateObject("wscript.shell")
  Set proc = wsShell.Exec(command)

  Dim s : s = ""
  Do While proc.Status = 0
    ' Wait until e-mail is sent...
    Application.Wait (Now + TimeValue("0:00:01"))
  Loop

  ' Use proc.ExitCode to check for returned %errorlevel%

  s = s & "StdOut=" & proc.StdOut.ReadAll()
  s = s & Chr(13) & Chr(10) & "ExitCode=" & proc.ExitCode
  MsgBox (s)

  Set wsShell = Nothing
  Set proc = Nothing

 
End Sub

Open in new window

0
 
wrm-duAuthor Commented:
Thanks for the sample code.  I will try in on my server and let you know how it works.
0
 
andrewssd3Commented:
Try this - if Outlook is not started, I think it needs a context for the mail to be sent from, so you get the default Inbox and send from there:
Sub sendit()

    Dim oApp As Outlook.Application
    Dim oMail As Outlook.MailItem
    Dim strBody As String
    Dim oFld As Outlook.MAPIFolder
    
    strBody = "This is the body of the email"

    Set oApp = CreateObject("Outlook.Application")
    Set oFld = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
  
    Set oMail = oFld.Items.Add
    With oMail
        .To = ThisWorkbook.Sheets("Setup").Cells(19, 2).Value
        .CC = ThisWorkbook.Sheets("Setup").Cells(20, 2).Value
        .Subject = "Sales for " & Format(ThisWorkbook.Sheets("Setup").Cells(3, 2).Value, "Long Date")
        .Body = strBody
        .send
    End With

End Sub

Open in new window

0
 
wrm-duAuthor Commented:
Regarding the code from Jodannet:  The code references a third party product from Febooti.  I am not opposed to using it.  I asked them a question if their product would send mail through hosted Exchange specifically Office 365.  I will respond with what they say.

Regarding the code from Andrewssd3:  I tried the code as provided and i get a compile error on the first statement Dim oApp as Outlook.Application.  I changed the variable types to Object instead of Outlook and then I get a Compile Error that olFolderInbox is not defined.

Thanks again for your help.

0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
i remember something about sending email with outlook and the issue you mentioned that you need to open outlook before you send , this issue modified by microsoft because vbscript viruses which was sending viruses through outlook , so i will search other way to do this.
0
 
wrm-duAuthor Commented:
Note that this VBA is in and Excel workbook and not Outlook.  I think the DIMs using Outlook types are reserved for Outlook VBA.
0
 
Maen Abu-TabanjehNetwork Administrator, Network ConsultantCommented:
yes i mean you working with outlook.application .. ok try this :

http://allbizanswers.com/how-to-automatically-send-emails-from-excel-based-on-cell-content/
0
 
andrewssd3Commented:
wrm-du - no you can use my code if you add a reference the Microsoft Outlook library.  I you don't want to do that. change the Outlook..... definitions to Object - e.g.
    Dim oApp As Object

Open in new window

etc...
0
 
wrm-duAuthor Commented:
Hey Mr. andrewssd3,
IT WORKS!!!!  Thank you for taking the time to share your expertise.
0
 
wrm-duAuthor Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.