Creating a MailItem in Outlook from Excel using Late Binding

[Steven Kelder]

Hi there, thanks for your Attention !! Excel 2002, WinXP

I have problems with the Following Code, copied from the excellent site
http://www.dicks-clicks.com, with some changes introduced by me.

I want to use Late Binding.

The problem is with the line:

Set olMail = olApp.CreateItem(olMailItem),

which gives Compile Error "Variable not Defined".

The previous line:
Set olNs = olApp.GetNamespace("MAPI")
I left in the code without really undestanding what I need it for...

Following is the Code, and Really thanks for your time !!!
Steven Kelder

Sub SendResults()
Dim olApp As Object
Dim olNs As Object
Dim olMail As Object 'Late Binding-generic Object data type

'Activate Outlook if it isn't Open yet:
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set olApp = CreateObject("Outlook.application")
End If

On Error GoTo 0
'End Activate Outlook

Set olNs = olApp.GetNamespace("MAPI") 'Do I need this ??
Set olMail = olApp.CreateItem(olMailItem)

'Send Mail:
With olMail
.To = "(e-mail address removed)"
.Subject = "Sample Subject"
.Body = "Sample Body Text" & vbCrLf
.Send
End With

Set olNs = Nothing
Set olApp = Nothing
End Sub

 

[Colo]

Hi Steven,
My machine not have Outlook now, but please try to use 0 instead of
olMailItem.
olMailItem is a constant of Outlook, so I think in the Late Binding,
Excel cannot understand what is olMailItem.

Code:
--------------------

Set olMail = olApp.CreateItem(0)

--------------------


And to use MAPI(Messaging Application Program Interface), we need to
get NameSpace object.
To handle email application, we need to use MAPI.

 

[Bill Manville]

Set olMail = olApp.CreateItem(olMailItem),

which gives Compile Error "Variable not Defined".

olMailItem is a constant defined within the Outlook object library.
If you create a reference to that library you can use the object
browser to find its value; then add your own definition in your module:
Const olMailItem = 0
and then remove the reference.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Dick Kusleika]

Steven

You don't need any of the olNs lines to send an email. You generally need
to get to the NameSpace to access items in folders, but not to create items.
So you can delete those lines.

'Activate Outlook if it isn't Open yet:
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
Set olApp = CreateObject("Outlook.application")
End If

On Error GoTo 0
'End Activate Outlook

There's no problem with this code, but you don't need all of it just to send
a mail. If you're just sending, you can use

Set olApp = CreateObject("Outlook.Application")

Even if you already have Outlook running, this will create a new instance of
Oultook. No matter how many instances you have, they all use the same pst
files, that is, they all access the same information. So creating a new
instance doesn't matter.