troubleshooting Question

Outlook 2007 Error in Excel VBA Script

Avatar of Glenn Stearns
Glenn StearnsFlag for United States of America asked on
OutlookVBAMicrosoft ExcelMicrosoft Office
6 Comments1 Solution191 ViewsLast Modified:
I am using this code, which works fine, to email a worksheet from within an Excel VBA script...
-------------------------------------------------------------------------------------
Dim outlookApp As Object
Dim outlookMail As Object
Dim wbTemp As Workbook
Dim strFileName As String

Set outlookApp = CreateObject("Outlook.Application")
Set outlookMail = outlookApp.CreateItem(0)

ActiveSheet.Copy

Set wbTemp = ActiveWorkbook

wbTemp.SaveAs "C:\Temp\" & wbTemp.Sheets(1).Name & ".xlsx", xlOpenXMLWorkbook

strFileName = wbTemp.FullName

wbTemp.Close False

With outlookMail
    .To = "test@yourco.com"
    .Subject = "Requested Spreadsheet"
    .BodyFormat = 2
    .HTMLBody = "Good morning!,<p>Attached is the spreadsheet you requested."
    .Attachments.Add strFileName
    .Importance = olImportanceHigh
    .Send
End With

Set outlookMail = Nothing
Set outlookApp = Nothing

Kill strFileName
-----------------------------------------------------------------------------------------------
As noted, the script works fine and runs to completion, but does generate an Outlook 2007 error message as shown in the attached file. I suspect this has to do with the version of Outlook being 2007, as I do not get the error when test-running the code on a machine with Outlook 2013. If any of you know how to adapt this code so as to eliminate the error, please post!

Thanks!

Glenn
Outlook-Error.JPG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros