Solved

Lotus Notes Stationary - Excel VBA

Posted on 2010-11-19
9
1,824 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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

20 Experts available now in Live!

Get 1:1 Help Now