Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 43

Thread: Solved: Using Excel To Send Emails Through Lotus Notes

  1. #1
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location

    Solved: Using Excel To Send Emails Through Lotus Notes

    I never thought that this was possible. But I found following code at
    http://www.fabalou.com/VBandVBA/lotusnotesmail.asp

    This code works fine except at one line:
    [VBA]'This public sub will send a mail and attachment if neccessary to the
    'recipient including the body text.
    'Requires that notes client is installed on the system.
    Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
    'Thanks to http://www.fabalou.com/VBandVBA/lotusnotesmail.asp
    '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 MailDbName As String 'THe current users notes mail database 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)

    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")

    'Next line only works with 5.x and above. Replace password with your password
    Session.Initialize ("password")
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.

    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    'Open the mail database in notes

    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    'Already open for mail
    Else
    Maildb.OPENMAIL
    End If

    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Subject = Subject
    MailDoc.Body = BodyText
    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")
    MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If

    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder

    MailDoc.SEND 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
    End Sub[/VBA]
    The program however fails at the Red Marked line. The error is application does not support this property or method (438).

    So I opened Notes Session. Then commented out the red marked line and tested the code. The code runs correctly.

    I'd like the code to start Notes - session and login with the specified ID and then execute further. This is for the first time I'm working on official project. And as usual I've stumbled. You guys are as usual the best help I can think of. Thank you.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  2. #2
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    I think, I am being little impatient here. I found a hint at ozgrid. So I specifically searched for XLDennis. I found some really terrific codes on Ron De Bruin's website which are developed by XLDennis.

    Looking at them perhaps its not necessary to access inbox after all. Following is the link:
    http://www.rondebruin.nl/notes.htm

    I will test some more and get back with results tomorrow.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  3. #3
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    The correct syntax for providing password is:
    [vba]session.hashpassword ("Password")[/vba]

    However, using it also turned futile.

    The problem now comes at:
    [vba]Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    'Already open for mail
    Else
    Maildb.OPENMAIL
    End If
    [/vba]

    Since the database is protected with password, it doesn't get opened straightaway (even after providing password at beginning). The provide password screen pops up again. Here VBA transfers control to inputbox. I tried to use SendKeys. But that wouldn't send keys. I used below syntax:
    [vba]Else
    Maildb.OPENMAIL
    application.sendkeys "Password~", True
    End If
    [/vba]
    But the code remained hung at Maildb.OPENMAIL and inputbox remained waiting. So I pressed ALT + TAB to get to the lotus inputbox and inserted password + ENTER. The control returned back to VBA to execute sendkeys which was meaningless (as password was already provided). So the revised question now:=

    Can I mimic the operation:
    1. To get the lotus-inputbox
    2. Punch in password + ENTER
    reliably? I've searched many forums to find (MrExcel / Ozgrid / ExpertExchange) and the site above but could not find what I was looking for. Kindly note, of all the trolling, I do not have cross-posted this thread anywhere.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  4. #4
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    A few things before saying closed on this thread.

    1. The password part is real tricky one. I could not come across a code which did that. In fact, a few posts recommended against it.

    2. Initialize is not method but it is subroutine where you pass on the password string (Found through object browser).

    3. It is member of NotesSession alright but the hitch is it is owned by:
    Domino.NotesSession and Not by Notes.Notesession. I tried creating ActiveX object using the same method but it fails.

    For all my money, I do not know what difference between 'Notes and Domino' is and it is not within my current capabilities either.

    For now I have adopted and changed XLDennis' code and have provided an option button of sending mails so the user can always turn on his lotus notes and send. Thank you.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  5. #5
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Gist of all 4 posts:
    If anyone wants to dig deeper to understand how things work with Lotus Notes then he / she should download IBM-Redbook and read.

    I came across numerous codes on internet and some of them were really useful. Anyone who is interested in using OLE automation shall read very good code samples posted by XLDennis on Ron De Bruin's website. Following is the link:
    http://www.rondebruin.nl/notes.htm

    However, the problem with OLE automation is that you need Notes running to send emails [and you can do only late-binding]. That said, it otherwise gives you the flexibility that you may need. So if you want to avoid that then you will have to Domino COM which you can early bind as well. All this is available in Redbook.
    OLE Automation:
    [vba]nSession = CreateObject("Notes.Notesession")[/vba]
    COM:
    [vba]nSession = CreateObject("Lotus.Notesession")[/vba]

    Using all these resources I have been able to put together a code which doesn't need notes running.
    1. It will prompt you to provide password through InputBox
    2. Ask you if you want to attach a file.
    I have commented where I thought I might forget.
    Note: The code is using late-binding but can be made early-binding using
    [vba]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
    [/vba]
    I am also attaching the Excel Workbook used for this.
    Usage:
    Put addresses in separate cells (To addresses in Col A; Cc addresses in Col B)
    In C2, type in message that you want to send.

    And then click on "Send" and you are done!

    BTW, this thread is now really "SOLVED" for me. Thanks for looking into it.
    Attached Files Attached Files
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  6. #6
    Hi Shrivallabha,

    Wow, this is amazing . I tried using the code and it worked perfectly for me when sending out emails.

    I also tried adding some format however its sending out plain text. How can I send it as an email if there is a table (ex D9:J13) which contains some formatted text with stats and a graph (ex called Graph 1) ?

    Thanks in advance.

  7. #7
    VBAX Expert CatDaddy's Avatar
    Joined
    Jun 2011
    Posts
    581
    Location
    thank you! this is awesome
    ------------------------------------------------
    Happy Coding my friends

  8. #8
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Quote Originally Posted by chuckster101
    Hi Shrivallabha,

    Wow, this is amazing . I tried using the code and it worked perfectly for me when sending out emails.

    I also tried adding some format however its sending out plain text. How can I send it as an email if there is a table (ex D9:J13) which contains some formatted text with stats and a graph (ex called Graph 1) ?

    Thanks in advance.
    Hello chuckster,

    Welcome to VBAX. I will see if something can be worked out.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  9. #9
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Quote Originally Posted by shrivallabha
    Hello chuckster,

    Welcome to VBAX. I will see if something can be worked out.
    Check this link:
    http://www.mrexcel.com/forum/showthread.php?t=518746
    Your issue is addressed.

    Only thing is it uses OLE automation so your notes shall be up and running before you run the code supplied there.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  10. #10
    VBAX Newbie
    Joined
    May 2013
    Posts
    1
    Location

    tks for sharing

    tks for sharing

  11. #11
    VBAX Newbie
    Joined
    Jul 2013
    Posts
    1
    Location
    .

  12. #12
    VBAX Newbie
    Joined
    Aug 2013
    Posts
    1
    Location

    Thanks for posting

    Quote Originally Posted by dayanrod View Post
    .

  13. #13
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Welcome to VBAX.

    I may not be able to answer anything 'new' in Lotus Notes regard as I do not use it any more.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  14. #14
    Shirivallabha
    I hope that even without using Lotus Notes you can answer to my question:
    What should I change in the file attached by you to save messages in sent folder?

    Thanks in advance!

  15. #15
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Quote Originally Posted by Ferdinando View Post
    Shirivallabha
    I hope that even without using Lotus Notes you can answer to my question:
    What should I change in the file attached by you to save messages in sent folder?

    Thanks in advance!
    Hi Ferdinando,

    Welcome to VBAX. I'd try to change:
    Call .Send(False, vToList)
    to
    Call .Send(True, vToList)
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  16. #16
    VBAX Regular
    Joined
    Sep 2013
    Posts
    7
    Location
    Hi Guys

    I am very new to VBA (just one week now) and I am working on a quoting system for work, which I have finished, but now need to have a button that opens an email, puts in a set statement, then adds the signature which I have set in Notes.

    I have written the code below, (ok, so I copied it from the web and ammended it to suit me) and all is working apart from I dont know how to get this to insert my signature into the mail when it opens..

    When I open a new mail in Notes normally, it always inserts my signature. but not when I run this module.

    Additionally, there will be two users using this sheet from two separate computers, each with their own LotusNotes operating, so it will need to take the signature from their machine.

    Here is the code I have so far

    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

    subject = Worksheets("Internal").Range("BD1")
    EmailAddress = Worksheets("Internal").Range("BD2")
    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 = "digitalprint@zcard.com"
    .subject = subject

    .body = "Dear" & " " & Worksheets("internal").Range("j10") & vbLf & vbLf & _
    "Many Thanks for your enquiry" & vbLf & vbLf & _
    "Please find Attached your Quotation" & vbLf & vbLf & _
    "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."

    .Save True, False
    End With

    Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
    With NUIdoc

    Set WordApp = Nothing
    End With

    Set NSession = Nothing

    End Sub

  17. #17
    VBAX Regular
    Joined
    Sep 2013
    Posts
    7
    Location
    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
            
                subject = Worksheets("Internal").Range("BD1")
                EmailAddress = Worksheets("Internal").Range("BD2")
        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 = "digitalprint@zcard.com"
            .subject = subject
            
            .body = "Dear" & " " & Worksheets("internal").Range("j10") & vbLf & vbLf & _
                "Many Thanks for your enquiry" & vbLf & vbLf & _
                "Please find Attached your Quotation" & vbLf & vbLf & _
                "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."
            
            .Save True, False
        End With
        
        Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
        With NUIdoc
        
       Set WordApp = Nothing
           End With
        
        Set NSession = Nothing
    
    End Sub

  18. #18
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,919
    Location
    Welcome to the forum! Please start your own thread for future posts. If a thread like this one relates, post the link (after you get 5 posts) or reference the thread number, 35917, if it might help others help you.

    signature = Maildb.GETPROFILEDOCUMENT("CalendarProfile").GETITEMVALUE("Signature")(0)
    Last edited by Kenneth Hobs; 09-13-2013 at 09:41 AM.

  19. #19
    VBAX Regular
    Joined
    Sep 2013
    Posts
    7
    Location
    Hi Kenneth

    I will remember that for future posts thank you.

    One question, where do I put this line of code into my existing code?

  20. #20
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,919
    Location
    I put my signatures at the end of the body myself.

    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
         
        subject = Worksheets("Internal").Range("BD1")
        EmailAddress = Worksheets("Internal").Range("BD2")
        '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 = "digitalprint@zcard.com"
            .subject = subject
            s(1) = "Dear" & " " & Worksheets("internal").Range("j10")
            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
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •