I was able to automatically e-mail an Excel file using
Outlook with the following code:
Sub EmailFile()
'Create reference to Outlook
Set olApp = CreateObject("Outlook.Application")
Set MailItem = olApp.CreateItem(olMailItem)
'Enter all required details in e-mail
With MailItem
.to = "xxxx.com"
.Subject = ThisWorkbook.Name
.Body = "File attached."
.Attachments.Add ThisWorkbook.FullName
.Display 'Optional
End With
'Send e-mail
MailItem.Send
'Release object variable
Set olApp = Nothing
Set MailItem = Nothing
End Sub

Now that our e-mail system is on Lotus Notes, this macro
fails in the first line of code. The error message is:
ActiveX component can't create object (error no. 429). I
have not been able to successfully get the code to point
to Lotus Notes instead of Outlook.

Any help in solving this problem would be much
appreciated. Thanks in advance. I am using Excel97.

Partho

Hi Partho,

Here is the code for the Lotus notes. BUT this is not for the VBA
(INFACT i NEVER TRIED TO USE IT IN VBA). This is for the VB6. It works
fine for me. You can create an application which when launched, sends
email with attachment to receipent. (Receipent and attachment should
be predefined in the code.) You can then use a macro from VBA to
launch the exe file created from VB6.

'======================
' PUT THIS CODE IN A MODULE
Option Explicit
Public Sub SendNotesMail(Subject As String, Attachment As String,
Recipient As String, BodyText As String, SaveIt As Boolean)
Dim Session As NotesSession
Dim dbDirectory As NotesDbDirectory
Dim MailDB As NotesDatabase
Dim MailServerName As String
Dim Username As String
Dim MailDoc As NotesDocument
Dim body As NotesRichTextItem
Dim attachedFile As NotesEmbeddedObject

Set Session = CreateObject("Lotus.NotesSession")
Session.Initialize
MailServerName = Session.GetEnvironmentString("MailServer", True)
Set dbDirectory = Session.GetDbDirectory(MailServerName)
Username = Session.Username
Set MailDB = dbDirectory.OpenMailDatabase
Set MailDoc = MailDB.CreateDocument
MailDoc.ReplaceItemValue "Form", "Memo"
MailDoc.ReplaceItemValue "SendTo", Split(Recipient, ";")
MailDoc.ReplaceItemValue "Subject", Subject
Set body = MailDoc.CreateRichTextItem("Body")
body.AppendText (BodyText)
body.AddNewLine (2)
'MailDoc.SaveMessageOnSend = SaveIt 'To SAVE sent mail, remove comment
If Attachment < "" Then
Set attachedFile = body.EmbedObject(1454, "", Attachment,
"Attachment")
End If
MailDoc.Send (0) '= Recipient ' (1) False would be preferable;
recipient is already set by SendTo item
Set MailDB = Nothing
Set MailDoc = Nothing
Set body = Nothing
Set Session = Nothing

MsgBox "EXCEL FILE SENT SUCCESSFULLY"

End Sub

' PUT THE FOLLOWING CODE IN FORM (DOUBLE CLICK ON THE FORM)

Option Explicit
Private Sub Form_Load()
Call SendNotesMail("New VB6 Mail", "C:\123\123.xls",
", "This is a Body Text", True)
Unload Me
End Sub

'============================
Remember to change the file path and email id.
Let me know if you need any more help.

Enjoy.
Ketan