Sleeper: Sending email via Lotus Notes

[tactps]

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?

[Bob Phillips]

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?

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]

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]

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