Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Lotus Notes Stationary - Excel VBA

Posted on 2010-11-19
9
Medium Priority
?
1,995 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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