asked on

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.

Maen Abu-Tabanjeh

before the code put
on error resume next

and try again , its will not crashed

wrm-du

ASKER

Thanks for your reply.
I added the code and ran a test. The error did not appear, but an email was not not sent.

Maen Abu-Tabanjeh

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

Maen Abu-Tabanjeh

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

Maen Abu-Tabanjeh

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

wrm-du

ASKER

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

Maen Abu-Tabanjeh

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

wrm-du

ASKER

I will try your test code and let you know how it goes.
Thanks

wrm-du

ASKER

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.

Maen Abu-Tabanjeh

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

wrm-du

ASKER

Thanks for the sample code. I will try in on my server and let you know how it works.

andrewssd3

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

wrm-du

ASKER

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.

Maen Abu-Tabanjeh

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.

wrm-du

ASKER

Note that this VBA is in and Excel workbook and not Outlook. I think the DIMs using Outlook types are reserved for Outlook VBA.

Maen Abu-Tabanjeh

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/

andrewssd3

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...

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wrm-du

ASKER

Hey Mr. andrewssd3,
IT WORKS!!!! Thank you for taking the time to share your expertise.

wrm-du

ASKER

Thanks for your help