PDA

View Full Version : Excel sending emails from a worksheet



rpieri
09-15-2011, 10:28 AM
hello,

i currently have a worksheet created in office 2003 we are migrating to 2010. if i run my code in 2010 i have two issues.

1) if outlook is not open i get a run time error 287

2) if outlook is open then you have to switch over to outlook after code runs and select allow someone to send an email on your behalf.

is there a way to modify my code to allow this to work i am kind of an amature at this and i set this up years ago. Below is my code. thanks

Windows 7
Exchange 2007
Office 2010



Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.SaveAs (["\\Simba\CSHARE\Sales_Log_sheets\FIAT\Co#7_FIAT_Daily_sales_log_review.xls"])
Application.DisplayAlerts = True

Dim oOLapp As Object
Dim oMailItem As Object
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem

.Subject = "Co#7 FIAT Daily sales log ready for Review"
.Body = "\\Simba\CSHARE\Sales_Log_sheets\FIAT\Co#7_FIAT_Daily_sales_log_review.xls"
.Recipients.Add "Joseph Calabrese"
.Recipients.Add "Ron Croce"
.Recipients.Add "Craig Schreiber"
.Recipients.Add "Rich Pieri"
.Recipients.Add "Harold B. Erbacher"
.Recipients.Add "- FIAT General Manager"
.Send '(or .Display if you want to look at it first
Set oOLapp = Nothing
Set oMailItem = Nothing

End With

Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
oMailItem.DeleteAfterSubmit = True
With oMailItem

.Attachments.Add "\\Simba\CSHARE\Sales_Log_sheets\FIAT\Co#7_FIAT_Daily_sales_log_review.xls"
.Subject = "Co#7 FIAT Daily sales log ready for Review"
.Recipients.Add "Larry Schreiber"
.Recipients.Add "Rich Pieri"
.Send '(or .Display if you want to look at it first
Set oOLapp = Nothing
Set oMailItem = Nothing

End With


Application.Visible = True
Application.EnableEvents = True
ThisWorkbook.Close
End Sub

mancubus
09-16-2011, 04:50 AM
you may find sample codes here:
http://www.rondebruin.nl/sendmail.htm