Solved: Using Excel To Send Emails Through Lotus Notes

[joee74]

Hi Kenneth.

I have done this and I can see its working looking for it, but instead of my signature appearing, the following is appearing:


C:\Documents and Settings\Joe\My Documents\joeellis-sig.html

I am using Windows 7, so there is no documents and settings.

the signature file is saved at the following destination.

C:\Users\joeellis\Documents\joeellis-sig.html

Obviously the user name and file name will change depending on who is using this spreadsheet
I really do appreciate your help here.

[Kenneth Hobs]

That is getting pretty far off this topic. Please start a new thread as I explained.

Sounds like you would use some of this. http://www.alcs.ch/html-lotus-notes-....html#more-375

[joee74]

Thanks Kenneth, excuse me for my ignorance, I am new to all of this.

I will start a new thread.

Appreciate your help

[amauta]

Thanks!

[Jacques.Grob]

Hi Shrivallabha

GREAT post! Thanks

[peto04]

Hello,
i need to send emails from excel via lotus. I tried your code but i have always error in row:
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}

What i exactly need to change or add to this vba code to correct working? I am new in vba.

Thanks for help.

Peter

[shrivallabha]

Hello,
i need to send emails from excel via lotus. I tried your code but i have always error in row:
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}

What i exactly need to change or add to this vba code to correct working? I am new in vba.

Thanks for help.

Peter

Welcome to VBAX. I don't use lotus notes any more but I can definitely try to answer. What error do you get when you run the code?

I have attached a sample workbook which you can download and see if it works Check specifically post #5 for all explanation and useful links.

[peto04]

Hello,
i just copied all vba code from post 5 and I don´t know what i need to change or fill in to this code.
I download your document from post 5 and when i click on SEND; i have error:

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

I don´t know what does it means. Can you help me with this?

Thanks a lot.
Peter

I

[shrivallabha]

Hello,
i just copied all vba code from post 5 and I don´t know what i need to change or fill in to this code.
I download your document from post 5 and when i click on SEND; i have error:

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

I don´t know what does it means. Can you help me with this?

Thanks a lot.
Peter

I

Bit strange. I've posted working code and some others have used successfully as well.

If you go to Visual Basic Editor | Tools | References, can you find the following reference:
"Lotus Domino Objects"

[Kenneth Hobs]

There are several responses here so I don't know which you mean. In this, .Send should suffice.

Option Explicit

Sub test()
  Dim lErr As ErrObject
  SendNotesMail _
    "PMP Handbook5", _
    "c:\t.pdf", _
    "khobson@work.org,khobson@aaahawk.com", _
    "Click file: " & vbCrLf & _
    "file://u:\Material\pmp\PMP%20Handbook.pdf" & vbCrLf & _
    "or, open the attachement.", , lErr
    If lErr.Number <> 0 Then MsgBox lErr.Number & vbCrLf & lErr.Description
  End Sub

'Escape characters, %20=space, http://everything2.com/node/1350052
'Similar to: Brian Walters, http://www.ozgrid.com/forum/showthread.php?t=67089
Public Sub SendNotesMail(Subject As String, Attachment As String, _
    ByVal Recipient As String, _
    BodyText As String, _
    Optional SaveIt As Boolean = True, _
    Optional ByRef lErr As ErrObject)
    'lErr is used when using the Sub in a batch process,
    'to handle instances where an error appears
    
    'Example of use:
    'SendNotesMail "The Subject", "C:\My Documents\TestFile.txt", _
        "john@doe.com, jane@doe.com", _
        "This is the body text, can be longer", True, lErr
    
  'Set up the objects required for Automation into lotus notes
  Dim Maildb As Object            'The mail database
  Dim UserName As String          'The current users notes name
  Dim MailDoc As Object           'The mail document itself
  Dim AttachME As Object          'The attachment richtextfile object
  Dim Session As Object           'The notes session
  Dim EmbedObj As Object          'The embedded object (Attachment)
  Dim ArRecipients() As String    'Array of recipients
  Dim i As Long                   'Counter
  Dim oBody As Object             'Body of text in for rich text format
  
  'Early Bind - Tools > Reference > Lotus Notes Automation Classes, notes32.tlb
  'Dim ln As lotus.NOTESSESSION
  'Set ln = CreateObject("Notes.NotesSession")
  'Dim db As lotus.NOTESDATABASE
  'Set db = ln.GETDATABASE("", "mail\username.nsf")
  'Dim mDoc As lotus.NOTESDOCUMENT
  'Set mDoc = db.CREATEDOCUMENT
  
  
  'Create an array of recipients (Separated by commas)
  ArRecipients() = Split(Recipient, ",")
  
  'Start a session to notes
  Set Session = CreateObject("Notes.NotesSession")
  On Error GoTo err_h
  
  'Open the mail database in notes
  UserName = Session.UserName
'************** ADD YOUR username.
  Set Maildb = Session.GETDATABASE("", "mail\khobson.nsf")
  If Maildb.IsOpen = False Then
      Maildb.OPENMAIL
  End If
  
  'Set up the new mail document
  Set MailDoc = Maildb.CREATEDOCUMENT
  MailDoc.Form = "Memo"
  MailDoc.sendto = ArRecipients
  MailDoc.Subject = UCase(Subject)
  'MailDoc.Body = UCase(BodyText)
  Set oBody = MailDoc.CREATERICHTEXTITEM("Body")
  oBody.APPENDTEXT BodyText
  
  'This is supposed to be the property, but works
  'on some systems only
  'without an apparent reason of failure
  MailDoc.SAVEMESSAGEONSEND = SaveIt
  
  'Set up the embedded object and attachment and attach it
  If Attachment <> "" Then
      Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
      Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
  End If
  
  'Send the document
  MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
  MailDoc.Send 1
  MailDoc.Save True, True, False
  
  'Clean Up
err_h:
Set lErr = Err
  Set Maildb = Nothing
  Set MailDoc = Nothing
  Set AttachME = Nothing
  Set Session = Nothing
  Set EmbedObj = Nothing
End Sub

[Lift]

I had been searching for a function like this but everything seemed to require Outlook. I'm using Notes and just wanted to say thanks for sharing this.
Lift

[Mirek1983]

Hello,

I have a issue with this code by one of my employees.

I have used this code to send e-mail via Excel.

Sub SendEmailUsingCOM() 
     
     '*******************************************************************************************
     ' Unlike OLE automation, one can use Early Binding while using COM
     ' To do so, replace the generic "object" by "commented" UDT
     ' Set reference to: Lotus Domino Objects
     '*******************************************************************************************
    Dim nSess       As Object 'NotesSession
    Dim nDir        As Object 'NotesDbDirectory
    Dim nDb         As Object 'NotesDatabase
    Dim nDoc        As Object 'NotesDocument
    Dim nAtt        As Object 'NotesRichTextItem
    Dim vToList     As Variant, vCCList As Variant, vBody As Variant 
    Dim vbAtt       As VbMsgBoxResult 
    Dim sFilPath    As String 
    Dim sPwd        As String 
     
     '*******************************************************************************************
     'To create notesession using COM objects, you can do so by using.
     'either ProgID  = Lotus.NotesSession
     'or     ClsID   = {29131539-2EED-1069-BF5D-00DD011186B7}
     'Replace ProgID by the commented string below.
     '*******************************************************************************************
    Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}
     
     '*******************************************************************************************
     'This part initializes the session and creates a new mail document
     '*******************************************************************************************
    sPwd = Application.InputBox("Type your Lotus Notes password!", Type:=2) 
    Call nSess.Initialize(sPwd) 
    Set nDir = nSess.GetDbDirectory("") 
    Set nDb = nDir.OpenMailDatabase 
    Set nDoc = nDb.CreateDocument 
     
     '*******************************************************************************************
     'If you want to send it to multiple recipients then use variant array to get the names from
     'the specified range as below
     'Add / Remove Comment mark from vCCList as per your needs.
     '*******************************************************************************************
    vToList = Application.Transpose(Range("A1").Resize(Range("A" & Rows.Count).End(xlUp).Row).Value) 
    vCCList = Application.Transpose(Range("B1").Resize(Range("B" & Rows.Count).End(xlUp).Row).Value) 
     
     '*******************************************************************************************
     'If you want to send it to multiple recipients then use variant array to get the names from
     'the specified range as below
     'Add / Remove Comment mark from vCCList as per your needs.
     '*******************************************************************************************
    With nDoc 
         
        Set nAtt = .CreateRichTextItem("Body") 
        Call .ReplaceItemValue("Form", "Memo") 
        Call .ReplaceItemValue("Subject", "Test Lotus Notes Email using COM") 
         
        With nAtt 
            .AppendText (Range("C2").Value) 
             
             'Decide if you want to attach a file.
            vbAtt = MsgBox("Do you want to attach document?", vbYesNo, "Attach Document") 
             
            Select Case vbAtt 
            Case 6 
                .AddNewLine 
                .AppendText ("********************************************************************") 
                .AddNewLine 
                sFilPath = Application.GetOpenFilename 
                Call .EmbedObject(1454, "", sFilPath) '1454 = Constant for EMBED_ATTACHMENT
            Case 7 
                 'Do Nothing
            End Select 
             
        End With 
         
        Call .ReplaceItemValue("CopyTo", vCCList) 
        Call .ReplaceItemValue("PostedDate", Now()) 
        Call .Send(False, vToList) 
         
    End With 
     
End Sub 

After the employee enter his password to LotusNotes he is getting a Object error -2######.

New Picture.jpg

I don't know where the issue can be?

Thank you for any help,

[bronzetch]

Thanks a lot Shrivallabah

[majdimarmas]

Can anyone advice how to add one attachment to the code above ( by Mr. Kenneth Hobs 09-13-2013, 08:38 AM )
I'de like to always have the same file attached.

Thank you in advance

[majdimarmas]

Can you please help me adding modifying the code to have attach a file to the created mails? Always the same file.

Sub SendQuoteToEmail()
    Dim NSession As Object
    Dim NDatabase As Object
    Dim NUIWorkSpace As Object
    Dim NDoc As Object
    Dim NUIdoc As Object
    Dim WordApp As Object
    Dim subject As String
    Dim EmailAddress As String
    Dim s(1 To 5) As String
    Dim r As Integer
    Dim x As Double
    

    
    NumOfRows = Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row
For r = 2 To NumOfRows

     
    subject = Worksheets("sheet1").Range("B2")
    EmailAddress = Worksheets("sheet1").Range("c2")
     'Debug.Print subject
     
    Set NSession = CreateObject("Notes.NotesSession")
    Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GETDATABASE("", "")
    If Not NDatabase.IsOpen Then NDatabase.OPENMAIL
     
    Set NDoc = NDatabase.CREATEDOCUMENT
     
    With NDoc
        .SendTo = EmailAddress
        .CopyTo = "sami"
        .subject = subject
        s(1) = "Dear" & " " & Worksheets("sheet1").Range("D2")
        s(2) = "Many Thanks for your enquiry"
        s(3) = "Please find Attached your Quotation"
        s(4) = "If you would like to go ahead with this order, please let  me know and I will send you a template, artwork guidelines and  procedures for processing your order."
        s(5) = " "
        .body = Join(s, vbCrLf & vbCrLf) & _
        NDatabase.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
        .Save True, False
    End With
     
    NUIWorkSpace.EDITDOCUMENT True, NDoc
     
    Set NDoc = Nothing
    Set WordApp = Nothing
    Set NSession = Nothing
    Next r
End Sub

[dontlose]

thanks a lot!

[angie]

I'm trying something similar, but want to send it to different email addresses based on content in the spreadsheet. i'll use this as a start. THANKS!

[Liamhead]

Thanks Shrivallabha!

[eurano]

This is awesome.

[mdrr_73]

Thanks for posting