(DoCmd.SendObject) Is there an Excel 2000 equal?

[MrMajik]

I want MS Excel 2000 to automatically send me an e-mail with the Subject line being determined by the contents of a cell.

I have the code doing everything except:

1. Open Outlook Express
2. Fill in the Subject line
3. Send the e-mail.

All of this must be done by code because the computer will be running without a person in the area or assigned to do this.

I have used the DoCmd.SendObject function in MS Access XP but MS Office 2000 does not recognize it.

Is there a way to get MS Office 2000 to open Outlook Express, fill in the subject line and then send the email using VB code?

Thank you.

 

[RobBroekhuis]

I don't think you'll need Outlook for this. How about:

activeworkbook.SendMail "me@my.com", range("MySubject&quot

Rob

 

[MrMajik]

Hi RobBroekhuis;

That works with one problem. It sends the entire spreadsheet as an attachment and I don't want to share the workbook with all e-mail recipients

Any other suggestions?

Thank you.

 

[RobBroekhuis]

Ah, I see. I misinterpreted your post. Opening Outlook from Excel isn't difficult. I'll post below some code I use, which you can modify for your purposes. You'll need to create a reference to the Outlook object model in your project. I forget exactly where I got it (and would probably write it a little differently now that I understand it better), but here goes.

Public golApp As Outlook.Application
Public gnspNameSpace As Outlook.NameSpace

Public Function InitializeOutlook() As Boolean
On Error GoTo Init_Err
Set golApp = New Outlook.Application
Set gnspNameSpace = golApp.GetNamespace("MAPI&quot
InitializeOutlook = True
Init_End:
Exit Function
Init_Err:
InitializeOutlook = False
Resume Init_End
End Function

Function CreateMail(astrRecip As Variant, _
strSubject As String, _
strMessage As String, _
astrAttachment As String) As Boolean

Dim objNewMail As Outlook.MailItem
Dim varRecip As Variant
Dim varAttach As Variant
Dim blnResolveSuccess As Boolean

On Error GoTo CreateMail_Err
If golApp Is Nothing Then
If InitializeOutlook = False Then
MsgBox "Unable to initialize Outlook Application " _
& "or NameSpace object variables!"
Exit Function
End If
End If

Set golApp = New Outlook.Application
Set objNewMail = golApp.CreateItem(olMailItem)
With objNewMail
For Each varRecip In astrRecip
.Recipients.Add varRecip
Next varRecip
blnResolveSuccess = .Recipients.ResolveAll
.Attachments.Add astrAttachment, , , Mid(astrAttachment, InStrRev(astrAttachment, "\&quot + 1)
.Subject = strSubject
.Body = strMessage
If blnResolveSuccess Then
.Send
Else
MsgBox "Unable to resolve all recipients. Please check " _
& "the names."
.Display
End If
End With
CreateMail = True

CreateMail_End:
Exit Function
CreateMail_Err:
CreateMail = False
Resume CreateMail_End
End Function

Rob