Solved

Lotus Notes Stationary - Excel VBA

Posted on 2010-11-19
9
1,836 Views
Last Modified: 2013-12-18
Hi,

I have created a stationary in Lotus Notes. I want to send it to different users with different attachments. The email IDs of users and path of attachment is present in an excel file (sample attached). I have been trying to create a macro in excel which will automate this task.

I would greatly appreciate if the experts can provide me with some help/guidence with this.

Thanks in advance. sample-EE.xls
0
Comment
Question by:sstampf
  • 5
  • 4
9 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 34172287
Pseudo:

Loop through Excel rows
    get e-mail and path from the current row
    Use the code from this post to send mail: http://www.e-e.com/Q_26160835.html
Until current row's A cell is empty

0
 
LVL 12

Author Comment

by:sstampf
ID: 34172342
Thanks for the quick response. Can you please help me figure out how to use the "Stationary memo" which I have created. Name of the stationary is "testCode".

Actually I already have a code which can create a new memo, attach file and send it. But the problem is that it goes unformatted (I mean the test in the "body" section). My stationary is heavily formatted and I am required to retain that formatting. Thanks for all your help.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34172417
Can you post your code?


Here's how to get the stationaries:

    Set session = CreateObject("Notes.NotesSession")

    Set Maildb = session.GETDATABASE("", "")
    If Maildb.IsOpen = False Then
        Maildb.OpenMail
    End If

    Set view = Maildb.GetView("Stationery")
    Set entries = view.AllEntries
    
    If entries.Count = 0 Then
        Exit Sub
    End If
    
    Set entry = entries.GetFirstEntry
    
    Do Until entry Is Nothing
        With entry.Document
            'check whether it's the one you want (by name) and if yes get it and exit the loop
        End With
        counter = counter + 1
        Set entry = entries.GetNextEntry(entry)
    Loop

Open in new window


Taken from here:
http://www.e-e.com/Q_26537680.html
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 34172471
Of course, this would be much faster, especially if you have many stationaries in the view.
Get document directly, by its name:

Set notesDocument = view.GetDocumentByKey( "stationary name" )

Open in new window

or
Set notesViewEntry = notesView.GetEntryByKey( "stationary name" )

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 22

Expert Comment

by:mbonaci
ID: 34172484
Also, check this out (I knew I've been involved in something similar before):

http://www.e-e.com/Q_26537527.html
0
 
LVL 12

Author Comment

by:sstampf
ID: 34172600
Thanks for the valuable inputs. Incidently, I am using the same code you suggested above. As I am not aware of Lotus Notes objects/properties so it seems that I am using wrong key words. Please help
Sub get_stationary()

    Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object

    Set Session = CreateObject("Notes.NotesSession")

    Set Maildb = Session.GETDATABASE("", "")

    If Maildb.IsOpen = False Then

        Maildb.OpenMail

    End If

    Set view = Maildb.GetView("Stationery")

    Set entries = view.AllEntries

    If entries.Count = 0 Then

        Exit Sub

    End If

    Set entry = entries.GetFirstEntry

    Do Until entry Is Nothing

        With entry.Document

            If .getItemValue("MailStationeryName") = "testCode" Then

                    'I am trying to change the "Send To" field here and it seems that its working....but I am not sure

                    .entersendto = "Test6151771@xyz.com"

                    'I am trying to change the "Send" the email here but its throwing error

                    entries.Sendentry (entry)

                    Exit Do

            End If

        End With

        counter = counter + 1

        Set entry = entries.GetNextEntry(entry)

    Loop

    Set Maildb = Nothing

    Set Session = Nothing

End Sub

Open in new window

0
 
LVL 12

Author Comment

by:sstampf
ID: 34172742
Based on your suggestions I have changed the code to below. Can you please confirm if I am moving in correct direction.

PS - I am still getting error at the line notesDoc.Sendto = "xyz@abc.com" I think because of using wrong key word.
Sub get_stationary()



    Dim Maildb As Object, view As Object, Session As Object, notesDoc As Object

    Set Session = CreateObject("Notes.NotesSession")

    Set Maildb = Session.GETDATABASE("", "")

    If Maildb.IsOpen = False Then

        Maildb.OpenMail

    End If

    Set view = Maildb.GetView("Stationery")



    Set notesDoc = view.GetDocumentByKey("testCode")

    notesDoc.Sendto = "xyz@abc.com"

    notesDoc.SaveMessageOnSend = True

    notesDoc.send False



    Set Maildb = Nothing

    Set Session = Nothing

    

End Sub

Open in new window

0
 
LVL 12

Author Comment

by:sstampf
ID: 34173028
I am thinking of a work around. Can you provide me some code which can save my stationary as a draft? Thank you so much
0
 
LVL 22

Accepted Solution

by:
mbonaci earned 500 total points
ID: 34173760
The only reason for an error in that line is that the document is null (Nothing).
Open the Stationery view in your mail db to find out how it looks (is called) in the view.
GetDocumentByKey looks in the first sorted column of the view.

And I suggest you use it like this:
Set notesDoc = view.GetDocumentByKey("testCode", True)

Open in new window


Where second param determines whether "exact match search" or "fuzzy search" will be used (True = exact match).

To save the doc in Drafts folder I think that you simply need to save it:
Call notesDoc.Save(True, False)

Open in new window


If that wont work, try like this:
Call notesDoc.PutInFolder( "($Drafts)", True )

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now