Problems with Excel and Lotus Notes interactivity

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
I helped design a spreadsheet that uses Excel to send an email via Lotus Notes and for the actual emailing code I will freely admit that I just borrowed it from someone else and don't understand it.

The spreadsheet is used by several users and seems to work fine in the main, but one has encountered problems. This one user has the same operating system (XP), the same Excel (2003) and the same Notes (6.5). The only difference is that she is using a laptop, whereas I used a desktop to set up and test the spreadsheet.

I've had her step through the code and the problem occurs on the line:
Set Session = CreateObject("Notes.NotesSession")

When it reaches this line, it skips to the error handling code at the bottom.

Does anyone know of any reasons that this could be happening or any possible solutions? Full code is below:

Code:
Sub emailer()

'Code to email RCR

    With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    End With

TodaysDate = Date
    ActiveWorkbook.SaveAs ("C:\Documents and Settings\" & Environ("username") & "\My Documents\Recruitment Campaign Request " & Sheets("Summary").Range("C10").Value & ", " & Sheets("Summary").Range("C57").Value & " " & Left(TodaysDate, 2) & "-" & Mid(TodaysDate, 4, 2) & "-" & Right(TodaysDate, 4) & ".xls")
    savedworkbook = "C:\Documents and Settings\" & Environ("username") & "\My Documents\Recruitment Campaign Request " & Sheets("Summary").Range("C10").Value & ", " & Sheets("Summary").Range("C57").Value & " " & Left(TodaysDate, 2) & "-" & Mid(TodaysDate, 4, 2) & "-" & Right(TodaysDate, 4) & ".xls"
    If ActiveWorkbook.Saved = False Then GoTo ExitSub
    On Error GoTo ExitSub
        
    user = Application.UserName
    Mid(user, 1, 1) = UCase(Mid(user, 1, 1))
    For counter = 1 To Len(user)
        If Mid(user, counter, 1) = "." Then
            Mid(user, counter, 1) = " "
            Mid(user, counter + 1, 1) = UCase(Mid(user, counter + 1, 1))
        End If
    Next counter
        
    ' Declare Variables for file and macro setup
    Dim UserName As String
    Dim MailDbName As String
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim AttachME As Object 'Attachment bit
    Dim Session As Object
    Dim EmbedObj1 As Object 'Attachment bit
    
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    Else
    Maildb.OPENMAIL
    End If
    
    Maildb.CreateDocument
    
Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
    MailDoc.from = Sheets("Summary").Range("C6").Value
    MailDoc.Subject = Sheets("Summary").Range("C8").Value & " RCR Request: " & Sheets("Summary").Range("C10").Value & ", " & Sheets("Summary").Range("C57").Value
    MailDoc.principal = Sheets("Summary").Range("C6").Value
    MailDoc.Body = Sheets("Additional information").Range("a31").Value
    
    attachment1 = savedworkbook
    'Attachment bit
        Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
        Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", attachment1, "")
    Application.ScreenUpdating = True
    'End Attachment bit
    
    MailDoc.SaveMessageOnSend = True

On Error GoTo 0

   sent = False

SendBit:
MailDoc.SaveMessageOnSend = True
    While sent = False
        On Error GoTo IncorrectAddressee
        emailto = InputBox("Please enter the Lotus Notes name of who you would like to send the RCR to:" & vbNewLine & "(Please remember that the RCR will need authorisation first)", "Email Addressee", "") '"Enter Details Here....")
        If emailto = Cancel Then Exit Sub
        MailDoc.SendTo = emailto 'Sheets("email wording").Range("a2").Value
        MailDoc.SaveMessageOnSend = True
        Call MailDoc.Send(False)
        If ErrorMessage1 = "" Then
        sent = True
        ErrorMessage1 = ""
        Else
        sent = False
        ErrorMessage1 = ""
        End If
        MailDoc.SaveMessageOnSend = True
        GoTo sentok
IncorrectAddressee:
        ErrorMessage1 = MsgBox("This form has not been submitted. Please check the Lotus Notes name of the recipient and try again.", vbOKOnly, "Incorrect Lotus Notes name")
        Resume Next
sentok:
MailDoc.SaveMessageOnSend = True
    Wend
MoreRecipients = MsgBox("Would you like to add another recipient?", vbYesNo, "Multiple Recipients")
If MoreRecipients = vbYes Then
sent = False
GoTo SendBit
Else
MessageSent = MsgBox("Your email has now been successfully sent", vbOKOnly, "Email Success")
End If
Exit Sub
    
ExitSub:
    MsgBox ("This form has not been submitted. Please fill in all the required fields and try again.")
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
Just had a second user contact me with the exact same problem. It falls over at the same point:

Set Session = CreateObject("Notes.NotesSession")

This user has the same set-up as the original problem user (XP, Excel 2003, laptop)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What's the error message?

Note, you might need to remove the error handling to find that out.
 

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
The error message is as follows:

Run-Time error '429':
ActiveX component can't create object
 

Forum statistics

Threads
1,181,412
Messages
5,929,782
Members
436,689
Latest member
IbraheemWard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top