Mail Sending to Lotus From VBA Form

[sakthi santhanam]

Hi,
I am working with the code sends the a mail to lotus notes. The code is working fine for me when I am working on wth my system... i am using win 7 and excel 2007... But, when i worked with the other system with the same configuration I got an error as "Can't find project or library" and i found the reference which was mentioning as Missing: ATPVBAEN.xlma...

Set lot = CreateObject("Lotus.NotesSession")

lpwd = UserForm5.TextBox2.Value

Call lot.Initialize(lpwd)
Set ndir = lot.GetDbDirectory("")
Set ndb = ndir.OpenMailDatabase
Set ndoc = ndb.CreateDocument

nto = "sam123@in.ars.com"
'nto = UserForm4.TextBox10.Value

attach1 = pth1 & "score.xls"
   
With ndoc
  Set natt = .CreateRichTextItem("Body")
  Call .ReplaceItemValue("Form", "Memo")
  Call .ReplaceItemValue("Copyto", nto)
  Call .ReplaceItemValue("Subject", "Ticket Quality Audit ---" & UserForm4.ComboBox1.Value)
  Call .ReplaceItemValue("Body", _
  "Please find the attachment of quality audit done against the ticket that you have dealt")
  ndoc.SaveMessageOnSend = True
  Set attachme = ndoc.CreateRichTextItem("attach1")
  Set embed = attachme.EmbedObject(1454, "attach1", pth1 & "score.xls", "")
  Call .Send(False, nto)
 
  End With
 
  MsgBox "Mail has sent to-" & nto

in this at code first the error was thrown at

set lot = createobject("Lotus.Notessession")

and after that I got the same error at

Set ndir = lot.GetDbDirectory("")

Please suggest me and aswell if I want to use the code in Excel 2003 what else I have to do? what are the reference that I need to check.

Regards,
sakthi

 

[NARAYANK991]

Hi Santhanam ,

The missing reference does not have anything to do with Lotus Notes , as far as I know. The missing reference is to the Analysis Toolpak , which I don't think you need for the purpose for which your code is written.

See if this link helps , as to the correct references you need to include :

http://vbadud.blogspot.co.nz/2007/10/automate-lotus-notes-email-using-visual.html

Narayan

 

[sakthi santhanam]

Hi Narayan,

Thanks for your immediate response... when I work with some other system which has excel 2007 I got the error Can't find project or library and the listed references were highlighted as Missing.
1) ATPVBAEN.XLAM
2) ADO 6.0

what causing this error though both the machine is working with excel 2007...

Regards,
sakthi

 

[NARAYANK991]

Hi Santhanam ,

You will have to see whether the files exist on those systems. See the following links for more information :

http://www.bettersolutions.com/excel/EFN147/LT423111411.htm

http://www.mrexcel.com/forum/excel-...crosoft-activex-data-objects-6-0-library.html

Narayan

 

[sakthi santhanam]

Hi Narayan,

The problem is solved by using ADO 2.8 instead ADO 6.0... But, don't know why this has happened? What is the difference between ADO 2.8 and ADO 6.0.

Regards,
sakthi

 

[shrivallabha]

The code that you've posted doesn't need the library references you've mentioned above. However, the unposted code might have to do with these two references.

You can find lotus notes COM documentation here:
http://www.redbooks.ibm.com/redbooks/pdfs/sg245670.pdf

 

[sakthi santhanam]

Hi Shrivallabha,

Thanks for the document and it's really help full.

Regards,
sakthi

 

[NARAYANK991]

Hi Santhanam ,

There is no difference as such. Refer this link :

http://msdn.microsoft.com/en-us/library/windows/desktop/ms676506(v=vs.85).aspx

Narayan

 

[sakthi santhanam]

Hi Narayan,

Thanks for the link and other info I wish to know from you...

Can we represent userform as HTML page... Like in Excel I create form and that form I need to use it in HTML so that I can paste the sheet some where in the common repository and people can use it which will open in the internet explorer.

Regards,
sakthi