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