PDA

View Full Version : Sleeper: Sending email via Lotus Notes



tactps
08-01-2005, 09:30 PM
Hi all,

I have a spreadsheet that I was running in Excel 2000 with Lotus Notes R5. Our systems have been upgraded (Excel, Lotus Notes and Windows) and the following code fails on computers that were upgraded on our network.

Set Session = CreateObject("Notes.NotesSession")

How can I determine what this should now read? (Is there a place I can look at the names of the object to determine the new syntax?)

Another thought - does this have anything to do with VBA references? Could an unchecked reference lead to this issue?
:dunno

Bob Phillips
08-02-2005, 02:25 AM
Hi all,

I have a spreadsheet that I was running in Excel 2000 with Lotus Notes R5. Our systems have been upgraded (Excel, Lotus Notes and Windows) and the following code fails on computers that were upgraded on our network.
code: Set Session = CreateObject("Notes.NotesSession") How can I determine what this should now read? (Is there a place I can look at the names of the object to determine the new syntax?)

Another thought - does this have anything to do with VBA references? Could an unchecked reference lead to this issue?
:dunno

It seems most likely that you don't have a reference to the latest version of Lotus Notes. If you have a missing reference, and it looks like it is associated with Notes, odds are that that is it. Check down the list if there is another of a similar name, maybe later version, and check that. You might have to browse for the library though.

tactps
08-02-2005, 06:24 PM
Thanks xld - but I am not quite with you on this (such is the danger of using code that I don't fully understand).

There are 3 references that are being used:
Visual Basic for Applications,
Microsoft Excel 9.0 Object Library (which under the upgraded system is 10.0), and
Microsoft Forms 2.0 Object Library

Upon further testing we discovered that when you launch the spreadsheet from Lotus Notes, the macro works. However, when you detatch the spreadsheet, open Excel and run it from there, it does not. It appears that Excel does not recognise that Lotus Notes is open in the latter case.

So it appears that either:
There is a new reference that I require, or
I am missing or have some incorrect syntax, or
I need to add something in to let Excel know that Lotus Notes is open.

Can you help - What is my next step?

tactps
08-02-2005, 06:34 PM
If this helps, this is the code:


Sub SendToLotusNotes()
ans = MsgBox("Are you sure you would like to email this order to Amcor?" _
, vbYesNo + vbQuestion, "Email")
If ans = vbYes Then
Application.ScreenUpdating = False
'Setup to send email from Lotus Notes
Set Session = CreateObject("Notes.NotesSession")
SiteName = Range("B3").Value
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Not Maildb.IsOpen Then Maildb.OpenMail
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
Sheets("AmcorEmailOrder1").Select
Emailaddress1 = Range("C17").Text
Emailaddress2 = Range("C18").Text
OrderNumber = UCase(Range("B25").Text)
Sheets("Amcor Header").Select
MailDoc.sendto = Emailaddress1 'Nickname or full address
If Len(Emailaddress2) > 1 Then
MailDoc.copyto = Emailaddress2 'Nickname or full address
End If
MailDoc.Subject = "Amcor Order - " & SiteName & " - " & OrderNumber
MailDoc.Body = Replace("Please find attached today's order.", "@", vbCrLf)
MailDoc.savemessageonsend = True
' Select File to Attach to E-Mail
MailDoc.savemessageonsend = True
nattachment1 = "U:\AmcorEmailOrder1.csv"
If attachment1 <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "U:\amcoremailorder1.csv", "")
End If
MailDoc.PostedDate = Now
nCall MailDoc.Send(False)
Cuser = Environ("username")
continue = MsgBox("Message sent successfully.", vbOKOnly, "Thank You - " & Cuser)
Else
Sheets("AmcorEmailOrder1").Select
Range("A25:L500").ClearContents
Sheets("Amcor Header").Select
End If
Application.ScreenUpdating = True
End Sub
:banghead: