Lotus Notes Email Macro Stopped Working in Excel 2010 Version - Ideas???

[jtwusmc]

I used the following code in excel 07 and 03 and it worked flawlessly. Now that I have switched over to Excel 2010, I am getting an error.

The error is: "Variable not defined"
On the line: Set Session = CreateObject("Notes.NotesSession")

Anyone know why Excel 2010 is not accepting this Macro?

Option Explicit

Sub EmailNotes()
Dim objNotesSession As Object
Dim objNotesDb As Object
Dim objNotesDoc As Object
Dim vaRecipients As String
Dim r As Long
Dim Msg As String
    On Error GoTo Error_Handling
    
    Application.ScreenUpdating = False
    
    Set [B]Session =[/B] CreateObject("Notes.NotesSession")
    strServer = Session.GetEnvironmentString("MailServer", True)
    strMailfile = Session.GetEnvironmentString("MailFile", True)
    Set Db = Session.GETDATABASE(strServer, strMailfile)
'    Set uiws = CreateObject("Notes.NotesUIWorkspace")
'
'    If db.IsOpen = True Then
'        'Already open for mail
'    Else
'        db.OpenMail
'    End If
        
    For r = 9 To Range("AF65536").End(xlUp).Row
        'Create the e-mail and add the attachment.
'        Set objNotesDoc = objNotesDb.CREATEDOCUMENT
        
        Msg = ""
        Msg = Msg & Range("AK" & r) & "," & vbCrLf & vbCrLf
        Msg = Msg & Range("AI" & r) & "." & vbCrLf & vbCrLf
        Msg = Msg & "Thank you," & vbCrLf
        Msg = Msg & "John Doe"
        
        vaRecipients = Range("AF" & r)
        
        CreateAndDisplayNotesEmail vaRecipients, Range("AJ" & r) & " Timecard ", Msg, ""
                
        'increment for the next person
        r = r + 22
    Next
    
    MsgBox ("The e-mails have successfully been distributed."), vbInformation
ExitSub:
  'Release objects from memory.
    Set Db = Nothing
    Set Session = Nothing
    Set uiws = Nothing
    Exit Sub
Error_Handling:
    MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
    Resume ExitSub
End Sub

 

[VoG]

Shouldn't it be?

Sub EmailNotes()
Dim objNotesSession As Object
Dim objNotesDb As Object
Dim objNotesDoc As Object
Dim vaRecipients As String
Dim r As Long
Dim Msg As String
    On Error GoTo Error_Handling
    
    Application.ScreenUpdating = False
    
    Set objNotesSession = CreateObject("Notes.NotesSession")
    strServer = objNotesSession.GetEnvironmentString("MailServer", True)
    strMailfile = objNotesSession.GetEnvironmentString("MailFile", True)
    Set Db = objNotesSession.GETDATABASE(strServer, strMailfile)
'    Set uiws = CreateObject("Notes.NotesUIWorkspace")
'
'    If db.IsOpen = True Then
'        'Already open for mail
'    Else
'        db.OpenMail
'    End If
        
    For r = 9 To Range("AF65536").End(xlUp).Row
        'Create the e-mail and add the attachment.
'        Set objNotesDoc = objNotesDb.CREATEDOCUMENT
        
        Msg = ""
        Msg = Msg & Range("AK" & r) & "," & vbCrLf & vbCrLf
        Msg = Msg & Range("AI" & r) & "." & vbCrLf & vbCrLf
        Msg = Msg & "Thank you," & vbCrLf
        Msg = Msg & "John Doe"
        
        vaRecipients = Range("AF" & r)
        
        CreateAndDisplayNotesEmail vaRecipients, Range("AJ" & r) & " Timecard ", Msg, ""
                
        'increment for the next person
        r = r + 22
    Next
    
    MsgBox ("The e-mails have successfully been distributed."), vbInformation
ExitSub:
  'Release objects from memory.
    Set Db = Nothing
    Set objNotesSession = Nothing
    Set uiws = Nothing
    Exit Sub
Error_Handling:
    MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
    Resume ExitSub
End Sub

 

[jtwusmc]

Thanks for your response. Wierd that the coding with only "Session" worked fine in the older version of excel, but I gave this a try just to check and got an error (Variable not defined) on the next line of code:

strServer = objNotesSession.GetEnvironmentString("MailServer", True)

Using the same exact code as VoG put above.

 

[VoG]

Try

Option Explicit

Sub EmailNotes()
Dim objNotesSession As Object
Dim objNotesDb As Object
Dim objNotesDoc As Object
Dim vaRecipients As String
Dim r As Long
Dim Msg As String
Dim strServer As String
Dim strMailfile As String
Dim Db As Object
    On Error GoTo Error_Handling
    
    Application.ScreenUpdating = False
    
    Set objNotesSession = CreateObject("Notes.NotesSession")
    strServer = objNotesSession.GetEnvironmentString("MailServer", True)
    strMailfile = objNotesSession.GetEnvironmentString("MailFile", True)
    Set Db = objNotesSession.GETDATABASE(strServer, strMailfile)
'    Set uiws = CreateObject("Notes.NotesUIWorkspace")
'
'    If db.IsOpen = True Then
'        'Already open for mail
'    Else
'        db.OpenMail
'    End If
        
    For r = 9 To Range("AF65536").End(xlUp).Row
        'Create the e-mail and add the attachment.
'        Set objNotesDoc = objNotesDb.CREATEDOCUMENT
        
        Msg = ""
        Msg = Msg & Range("AK" & r) & "," & vbCrLf & vbCrLf
        Msg = Msg & Range("AI" & r) & "." & vbCrLf & vbCrLf
        Msg = Msg & "Thank you," & vbCrLf
        Msg = Msg & "John Doe"
        
        vaRecipients = Range("AF" & r)
        
        CreateAndDisplayNotesEmail vaRecipients, Range("AJ" & r) & " Timecard ", Msg, ""
                
        'increment for the next person
        r = r + 22
    Next
    
    MsgBox ("The e-mails have successfully been distributed."), vbInformation
ExitSub:
  'Release objects from memory.
    Set Db = Nothing
    Set objNotesSession = Nothing
    Set uiws = Nothing
    Exit Sub
Error_Handling:
    MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
    Resume ExitSub
End Sub

 

[jtwusmc]

Thanks for your helps with this. Your solution worked to bypass the original error, but caused an error "Sub or function not defined" on the line (highlighting "Range").

Line with error
CreateAndDisplayNotesEmail vaRecipients, Range("AJ" & r) & " Timecard ", Msg, ""

Almost there! Thanks for your help!

 

[VoG]

What is the error message?

 

[jtwusmc]

What is the error message?

Runtime Error 6 Overflow

Thanks

 

[VoG]

Sorry, I cannot see how that could generate an overflow message. Can you (re)post your exact code and highlight the line of code that causes the error.