Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2022
  • Last Modified:

Lotus Notes Stationary - Excel VBA

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
sstampf
Asked:
sstampf
  • 5
  • 4
1 Solution
 
mbonaciCommented:
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
 
sstampfAuthor Commented:
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
 
mbonaciCommented:
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!

 
mbonaciCommented:
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
 
mbonaciCommented:
Also, check this out (I knew I've been involved in something similar before):

http://www.e-e.com/Q_26537527.html
0
 
sstampfAuthor Commented:
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
 
sstampfAuthor Commented:
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
 
sstampfAuthor Commented:
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
 
mbonaciCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now