[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how can I feed password to Lotus, when sending an email from ACCESS using vba?

Posted on 2005-04-07
31
Medium Priority
?
557 Views
Last Modified: 2013-12-18
Hi!
 
The logic I found on the web works fine....to send email from Access to Lotus
 
However, would like to open Lotus notes with my Password via vba.
 
Way below is the email logic that I use....I am using Lotus 5.0.10 .....
Thanks in advance for your assistance, Sandra
 
 
'================my code
Option Compare Database

Option Explicit


Public Sub testLotusNotes1(strFileName As String)

Dim strLine1 As String
Dim strLine2 As String
Dim strBody As String

strLine1 = "This is an automated email"
strLine2 = "Please review"
strBody = strLine1 & vbCrLf & vbCrLf & strLine2

LotusNotes2 "Email auto...", _
strFileName, _
strBody, _
True, _
Null, _
"test@..."

End Sub




Public Sub LotusNotes2(Subject As String, _
Attachment As String, _
Bodytext As String, _
Saveit As Boolean, _
strBCC As Variant, _
Recipient1 As String, _
Optional Recipient2 As String, _
Optional Recipient3 As String)

' ParmArrary RecipientList() As Variant)


'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.


'Set up the objects required for Automation into lotus notes

Dim Maildb As Object 'The mail database
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)

Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database
name

Dim arrRecipientList(25) As Variant
arrRecipientList(0) = Recipient1
arrRecipientList(1) = Recipient2
arrRecipientList(2) = Recipient3


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

'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

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 = arrRecipientList ' Recipient

If Not IsNull(strBCC) Then
MailDoc.CopyTo = "xxx@..." ' ccRecipient
End If

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

' Set AttachME = MailDoc.CreateRichTextItem("body")
' AttachME.AddNewLine (2)
' AttachME.AppendText ("more text")

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

'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing

Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub


0
Comment
Question by:mytfein
  • 13
  • 13
  • 5
31 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13726810
Hi mytfein,

> However, would like to open Lotus notes with my Password via vba.
"Open"... What's your idea? Do you want the Notes client to start up? Or what are you trying to do?

Cheers!
   Sjef
0
 

Author Comment

by:mytfein
ID: 13726963
Hi Sjef,

The above logic works smoothly if Lotus notes is open.

However, if Lotus is not up, the above vba code instantiates a session and brings up a password dialogue box.

I would like my vba script to run at night, so would like the procedure that creates the email, to send along the password,
so that the script runs without human intervention.

I added the line:
    Call Session.Initialize("mytfein")

getting error: object does not support property or method

So in access vba set another references to: Lotus Dominoes object -- still does not work....

Any ideas would be appreciated..thanks, Sandra
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13727053
Got it :)

Read this link: http:Q_21203754.html "How to send EMAIL through VBA by preventing password promt from being shown"
0
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!

 
LVL 31

Expert Comment

by:qwaletee
ID: 13727105
Hi sjef_bosman,

Dim Session As New NotesSession 'may have to use Lotus.NotesSession or Lotus.Domino.NotesSession or Domino.NotesSession -- depends on how things ar ergeistered
Call Session.Initialize("Password for local Notes user's ID")

Rest should be able to stay the same.

Best regards,
qwaletee
0
 

Author Comment

by:mytfein
ID: 13727537
Dear Experts,

I tried qwaletee's ideas.... having trouble...

I commented out these statementsin original code :
    'Dim Session              As Object 'The notes session
    'Start a session to notes
    '    Set Session = CreateObject("Notes.NotesSession")


with following ideas:
a)    
    Dim Session             As New NotesSession
       got error: object does not support this property or method  
                  on:  db.openmail

   ' Dim session             As lotus.NotesSession
       got error: object variable or with block variable not set
                 on: Call session.Initialize("mytfein")
c)
    'Dim session             As lotus.Domino.NotesSession
      got error: user defined type not defined
d)
   Dim session             As Domino.NotesSession
                        got error: object variable or with block variable not set
                 on: Call session.Initialize("mytfein")

Please note that:
when I dim as lotus  -or-
           dim as domino
                 micorsoft intellisense does drop down with possible values

Please advise, thanks, Sandra
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13727793
mytfein,

>     'Dim Session              As Object 'The notes session
>     'Start a session to notes
>     '    Set Session = CreateObject("Notes.NotesSession")

These should stay, but in different form. Try:

     Dim Session As Object 'The notes session
     'Start a session to notes
     Set Session = CreateObject("Lotus.NotesSession")

Sjef
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13727810
Followed by the
    Call session.Initialize("mytfein")
0
 

Author Comment

by:mytfein
ID: 13728422
Hi Sjef,

per your suggestion, codes now looks like:

Dim Session              As Object 'The notes session
  'Start a session to notes
 Set Session = CreateObject("Lotus.NotesSession")
 Call Session.Initialize("mytfein")
   
I step thru the above statements successfully. However:
      getting error msg of this statement:    Maildb.OPENMAIL
                error: "object does not support this property or method  "

Do I have a references problem? Dominoes is checked off under Tools/references.
Do I have a version problem? Using 5.0.10
thx, Sandra
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13728762
As per Lotus Help, OpenMail is supported in LotusScript only. Since the language is much alike VB, most of the coding is the same. Skip the lines with the GetDatabase-call until the first End If, and use this instead:

    Dim dir As Object

    Set dir= session.GetDbDirectory("")
    Set mailDb= dir.OpenMailDatabase()
0
 

Author Comment

by:mytfein
ID: 13729557
Hi Sjef,

Followed your advice, revised routine  pasted below.
Getting: getting error: object does not support property or method

on statement:
                   MailDoc.Form = "Memo"

tia, Sandra


'============
Public Sub LotusNotes2(Subject As String, _
                       Attachment As String, _
                       Bodytext As String, _
                       Saveit As Boolean, _
                       strBCC As Variant, _
                       Recipient1 As String, _
                       Optional Recipient2 As String, _
                       Optional Recipient3 As String)
                       
            '           ParmArrary RecipientList() As Variant)
           
On Error GoTo Err_LotusNotes2

                     
'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.


'Set up the objects required for Automation into lotus notes

    Dim Maildb               As Object 'The mail database
    Dim MailDoc              As Object 'The mail document itself
    Dim AttachME             As Object 'The attachment richtextfile object
   
    Dim Session              As Object 'The notes session
    'Start a session to notes
    Set Session = CreateObject("Lotus.NotesSession")
    Call Session.Initialize("mytfein")
   
   ' Dim Session             As New NotesSession
   ' Dim session             As lotus.NotesSession
   ' Dim session             As lotus.Domino.NotesSession
   ' Dim Session             As Domino.NotesSession
   
   
       
    Dim EmbedObj             As Object 'The embedded object (Attachment)

    Dim UserName             As String 'The current users notes name
    Dim MailDbName           As String 'THe current users notes mail database name
   
    Dim arrRecipientList(25) As Variant
    arrRecipientList(0) = Recipient1
    arrRecipientList(1) = Recipient2
    arrRecipientList(2) = Recipient3
   
       
   
    '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
   
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & _
                 Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
   
    Dim dir As Object

    Set dir = Session.GetDbDirectory("")
    Set Maildb = dir.OpenMailDatabase()

       
    'Set up the new mail document
    Set MailDoc = Maildb.CreateDocument
   
    MailDoc.Form = "Memo"
    MailDoc.SendTo = arrRecipientList                     ' 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
   
 '   Set AttachME = MailDoc.CreateRichTextItem("body")
 '   AttachME.AddNewLine (2)
 '   AttachME.AppendText ("more text")
   
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    ' MailDoc.Send False, RecipientList
    MailDoc.Send (False)
   
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
   
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
   
   
Exit_LotusNotes2:
    Exit Sub

Err_LotusNotes2:
    MsgBox Err.Description
    Resume Exit_LotusNotes2
   
   
End Sub
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13730747
It could just be that MailDoc is Nothing, i.e. that CreateDocument fails for some reason. It might even be the line before, if MailDb is Nothing.

If it's the .Form that is refused, for there is no early recognition of Notes types, then try to convert the line as follows:

    maildoc.ReplaceItemValue "Form", "Memo"
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 600 total points
ID: 13731101
mytfein,

>                    MailDoc.Form = "Memo"
That must have come from LotusScript code.  Within Notes, there is a special late-binding property for NotesDocument objects that allows you to access their fields as if they are properties of the object itself.  That is not available in COM, so you have to change it to:

MailDoc.ReplaceItemValue "Form" , "memo"

Similarly,
    MailDoc.SendTo = arrRecipientList                     ' Recipient
beocmes  MailDoc.ReplaceItemValue "SendTo" , arrRecipientList                     ' Recipient
...and the same for any MailDoc.FIELDNAME = lines


I believe VBA supports this sytax, but if not it would be:
    Call MailForm.ReplaceItemValue ( "Form" , "Memo" )


- qwaletee
0
 

Author Comment

by:mytfein
ID: 13731337
Hi qwaletee,

Thx for your response....I appreciate the help.....

I followed your suggestions. Code is below. Was able to step thru additional lines successfully,
                                             however:
a)   got error msg: vector must contain object of the same class
   for statement:   MailDoc.ReplaceItemValue "SendTo", arrRecipientList                     ' Recipient"

b) When I commented out the above line to see if I can proceed further,
   got error msg: The object involved has disconnected from its client
   for statement:  MailDoc.ReplaceItemValue "SaveMessageOnSend", Saveit

Code is below....Thank you very much, Sandra
==================================================
Public Sub LotusNotes2(Subject As String, _
                       Attachment As String, _
                       Bodytext As String, _
                       Saveit As Boolean, _
                       strBCC As Variant, _
                       Recipient1 As String, _
                       Optional Recipient2 As String, _
                       Optional Recipient3 As String)
                       
            '           ParmArrary RecipientList() As Variant)
           
On Error GoTo Err_LotusNotes2

                     
'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.


'Set up the objects required for Automation into lotus notes

    Dim Maildb               As Object 'The mail database
    Dim MailDoc              As Object 'The mail document itself
    Dim AttachME             As Object 'The attachment richtextfile object
   
    Dim Session              As Object 'The notes session
    'Start a session to notes
    Set Session = CreateObject("Lotus.NotesSession")
    Call Session.Initialize("mytfein")
   
   ' Dim Session             As New NotesSession
   ' Dim session             As lotus.NotesSession
   ' Dim session             As lotus.Domino.NotesSession
   ' Dim Session             As Domino.NotesSession
   
   
       
    Dim EmbedObj             As Object 'The embedded object (Attachment)

    Dim UserName             As String 'The current users notes name
    Dim MailDbName           As String 'THe current users notes mail database name
   
    Dim arrRecipientList(25) As Variant
    arrRecipientList(0) = Recipient1
    arrRecipientList(1) = Recipient2
    arrRecipientList(2) = Recipient3
   
       
   
    '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
   
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & _
                 Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
   
    Dim dir As Object

    Set dir = Session.GetDbDirectory("")
    Set Maildb = dir.OpenMailDatabase()

       
    'Set up the new mail document
    Set MailDoc = Maildb.CreateDocument

   '===========================
   ' BEGIN: commenting out old statements
  '=============================
    'MailDoc.Form = "Memo"
    'MailDoc.SendTo = arrRecipientList                     ' 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
   
 '   Set AttachME = MailDoc.CreateRichTextItem("body")
 '   AttachME.AddNewLine (2)
 '   AttachME.AppendText ("more text")
   
    'Send the document
    'MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    ' MailDoc.Send False, RecipientList
    'MailDoc.Send (False)
   
   '===========================
   ' END: commenting out old statements
  '=============================
   
   
    MailDoc.ReplaceItemValue "Form", "memo"    
    MailDoc.ReplaceItemValue "Subject", Subject
    MailDoc.ReplaceItemValue "Body", Bodytext

'------------> new code got errors at this point:
    MailDoc.ReplaceItemValue "SendTo", arrRecipientList                     ' got error (a) described above
   
    MailDoc.ReplaceItemValue "SaveMessageOnSend", Saveit              ' got error (b) described above

   
   

    '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.ReplaceItemValue "CreateRichTextItem", ("Attachment")
    End If
   
   
    'Send the document
    MailDoc.ReplaceItemValue "PostedDate", Now() 'Gets the mail to appear in the sent items folder
    MailDoc.ReplaceItemValue "Send", (False)
   
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
   
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
   
   
Exit_LotusNotes2:
    Exit Sub

Err_LotusNotes2:
    MsgBox Err.Description
    Resume Exit_LotusNotes2
   
   
End Sub
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 1400 total points
ID: 13734146
a) arrRecipientList is declared as a Variant array of 26 places; you only use the first 3 entries, apparently, they are filled with strings; and the rest has a non-string value, something Notes cannot cope with; declare arrRecipientList as
    Dim arrRecipientList(2) As Variant

b) MailDoc.ReplaceItemValue "SaveMessageOnSend", Saveit
SaveMessageOnSend is NOT a field, it is a property of a document. In this case you shouldn't use ReplaceItemValue, but
    MailDoc.SaveMessageOnSend= Saveit

Sjef
0
 

Author Comment

by:mytfein
ID: 13736110
Hi sjef,

Followed your suggestions, made the above 2 changes, and the subroutine ran without error msgs!
                 however:

the email was not sent.....cannot tell what "happened"......

The logic pasted in the the initial post did the following:
    a) opened an instance of lotus
    b) sent the email
    c) (prompting for a password if lotus not already open)
    d) left lotus open

I was hoping for a, b, and d....yet they did not seem to happen.....
Your assistance is very appreciated....thx, Sandra
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13736248
Things to check:
- If Saveit is True, is the mail in the Sent folder in the Notes mail database?
- Is there anything relevant in the Domino log database (log.nsf, Miscellaneous) around the time you tried to send the mail?
- ...

Whoa! What's this?
    MailDoc.ReplaceItemValue "Send", (False)

Send is a method of a NotesDocument, probably intended was:
    MailDoc.Send False
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13736273
And I'm doubt whether the attachment will be sent. Test that anyway, if it isn't sent do come back for refreshments :)
0
 

Author Comment

by:mytfein
ID: 13736592
Hi Sjef,

Thank you! Made the above change and the email was sent!
Feedback and questions, please:

Feedback:
=======
a) the attachment went out successfully
b) logic works when Lotus is --- not --- open, and when Lotus is --- already --- open

Questions:
=======
a) The module that calls the Lotus-email-module contains these statements:

     strLine1 = "Hi xxx, This is an automated email"
     strLine2 = "Please review attached spreadsheet ...thanks!l"
     strBody = strLine1 & vbCrLf & vbCrLf & strLine2

The original email logic,
              properly had line1 and line2      as 2 --- separate --- lines in the body of the email, due to the embedded
                                                                                         vbCrLf characters in  strBody that divided line 1 and 2

The new     email logic,
                           has line 1 and line 2 on the --- same --- line with 3 unprintable (thick pipe characters) separating them
question: In the new logic: is vbCRLf  ---not --- being understood?
The vba is  way below:

b)  based on your earlier correction regarding the array size. If I had more recipients, do you suggest that I redim the array to the number of elements that I need?

c) With the revised routine, I do not see Lotus "physically" being opened (as in the original logic). Is that ok/is that to be expected?


'============= set up email parameters
Public Sub testLotusNotes1(strFileName As String)

Dim strLine1 As String
Dim strLine2 As String
Dim strBody As String

strLine1 = "Hi xxx, This is an automated email"
strLine2 = "Please review attached spreadsheet ...thanks!l"
strBody = strLine1 & vbCrLf & vbCrLf & strLine2

LotusNotes2 "Email auto...", _
            strFileName, _
            strBody, _
            True, _
            Null, _
            "test@test.com"
           
End Sub


'==============  send email routine
Public Sub LotusNotes2(Subject As String, _
                       Attachment As String, _
                       Bodytext As String, _
                       Saveit As Boolean, _
                       strBCC As Variant, _
                       Recipient1 As String, _
                       Optional Recipient2 As String, _
                       Optional Recipient3 As String)
                       
            '           ParmArrary RecipientList() As Variant)
           
On Error GoTo Err_LotusNotes2

                     
'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.


'Set up the objects required for Automation into lotus notes

    Dim Maildb               As Object 'The mail database
    Dim MailDoc              As Object 'The mail document itself
    Dim AttachME             As Object 'The attachment richtextfile object
   
    Dim Session              As Object 'The notes session
    'Start a session to notes
    Set Session = CreateObject("Lotus.NotesSession")
    Call Session.Initialize("mytfein")
   
   ' Dim Session             As New NotesSession
   ' Dim session             As lotus.NotesSession
   ' Dim session             As lotus.Domino.NotesSession
   ' Dim Session             As Domino.NotesSession
   
   
       
    Dim EmbedObj             As Object 'The embedded object (Attachment)

    Dim UserName             As String 'The current users notes name
    Dim MailDbName           As String 'THe current users notes mail database name
   
   
    Dim arrRecipientList(2) As Variant          'offset 0, 3 elements
    arrRecipientList(0) = Recipient1
    arrRecipientList(1) = Recipient2
    arrRecipientList(2) = Recipient3
   
       
   
    '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
   
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & _
                 Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
   
    Dim dir As Object

    Set dir = Session.GetDbDirectory("")
    Set Maildb = dir.OpenMailDatabase()

       
    'Set up the new mail document
    Set MailDoc = Maildb.CreateDocument
   
    'MailDoc.Form = "Memo"
    'MailDoc.SendTo = arrRecipientList                     ' 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
   
 '   Set AttachME = MailDoc.CreateRichTextItem("body")
 '   AttachME.AddNewLine (2)
 '   AttachME.AppendText ("more text")
   
    'Send the document
    'MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    ' MailDoc.Send False, RecipientList
    'MailDoc.Send (False)
   
   
   
    MailDoc.ReplaceItemValue "Form", "memo"
   
    MailDoc.ReplaceItemValue "Subject", Subject
    MailDoc.ReplaceItemValue "Body", Bodytext
    MailDoc.ReplaceItemValue "SendTo", arrRecipientList                     ' Recipient"
   
    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.ReplaceItemValue "CreateRichTextItem", ("Attachment")
    End If
   
   
    'Send the document
    MailDoc.ReplaceItemValue "PostedDate", Now() 'Gets the mail to appear in the sent items folder
    MailDoc.Send (False)
   
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
   
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
   
   
Exit_LotusNotes2:
    Exit Sub

Err_LotusNotes2:
    MsgBox Err.Description
    Resume Exit_LotusNotes2
   
   
End Sub
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13736808
Attachment okay, that's good! :)

a) I assume somewhere in the original code vbCrLf is defined as
    Dim vbCrLf As String
    vbCrLf= Chr$(13) + Chr$(10)

b) Redim is indeed the way to go

c) No need to open Notes, because you can handle everything through the backdoor.

d) about multiple recipients: you might consider to pass an array of strings to the sub you wrote (it's commented out at the moment)
0
 

Author Comment

by:mytfein
ID: 13737320
Hi Sjef,

thank you....I really appreciate the help....

a) not sure I understand:
   vbCrLf is a vba reserved word that represents carriage return line feed

    to experiment, copied your lines of code as: ---str---CrLf
          Dim strCrLf As String
          strCrLf= Chr$(13) + Chr$(10)

Got the same results of line 1 and line 2 physically on same line, separated by unprintable characters.
Would be open to any ideas/work arounds

b) was re-reading your earlier comments.  As I was following a little blindly,
                     wondering if you could shed light on the quoted statements below.

     1) "As per Lotus Help, OpenMail is supported in LotusScript only. Since the language is much alike VB, most of the coding is the same"
       
      question:
      why did we make so many changes to the email script?
         If the original logic worked, (except for the password part) what kind of "language" was it written in? Standard vba?
         As your comments use the phrase "LotusScript", is the new logic written in Lotus Script?
                  If the answer is "yes", maybe Lotus Script does not recognize vba reserved words, such as vbCrLf?


      2) "If it's the .Form that is refused, for there is no early recognition of Notes types, then try to convert the line as follows"
        question:
            what does "no early recognition"     (are you referring to early/late binding?)
              and     "of Notes types" mean?      (are you referring to properties and methods?)

      3) As you can tell, I'm "visiting" the Lotus Notes/Domino world.....
          At work, we use Lotus for email, and Microsoft office suite. That's why using Access/vvba.
          In your work, how do you use Lotus and Lotus Script?

      4) In your initial posts, you recommended that I look at a similar discussion on EE.
          That discussion had a solution using API, that was a bit "above my head".
          Are the revisions that you assisted me with, something that was introduced in later versions of Lotus, and that
          previous versions of Lotus needed the API logic as a work around?

      5)  It is my understanding that we may upgrade to the latest version of Lotus in the near future.
           Do you think the revised logic will work, or further modifications at that time will be required?

thx again, Sandra





0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13737603
a) I'm not much of a VB/VBA person, I must admit. So if both strCrLf and vbCrLf aren't working, there's something wrong we can find a solution or work-around for. Best solution would be to create Bdoy as a Rich-text field, I'll try to supply some code later on

b) I expected it to be LotusScript, the programming language of Notes, but it was LotusScript-alike: VB with early-binding "makes Domino classes available as typed variables with compile-time checking". Now I don't know if Access-VBA permits early-binding, but that would have made your life somewhat simpler. Due to the lack of type information, you had to use late-binding, which permits only standard constructions of VBA. To make sure, the language you're using is VBA.

b.3) Notes is a database environment, much like Access, but quite different. Lots of things can be programmed in Notes, so maybe the Access application you have could easily be written and shared in Notes. Unlike many people think, Notes is much more than mail. But that's a different question.

b.4) Using the API would go far beyond a standard solution. If there was any API in the links I gave, just forget them, they are beside this point.

b.5) Most likely everything will work with the current Notes versions.
0
 

Author Comment

by:mytfein
ID: 13738027
Hi Sjef,

thanks....so glad about b3-b5

feedback, and questions, please


feedback:
==========
a) I need to come up with a workaround for crlf  -- would appreciate your help :)

b) I need doing more reading about early/late binding concepts. I have done ACCESS to EXCEL automation, and
                                                                                                              ACCESS to WORD automation
               however,
              most of the logic was copied off websites, so my understanding of early/late binding is a bit vague....

c)        I do believe that Access allows early binding, just looked up some ACCESS to WORD vba that I have:
              Dim appWord              As Word.Application
              Dim docs                     As Word.Documents

questions:
========
1)       Does early/late binding have something to do with how an item as dimmed?
       a)       dim x as object is        (late binding, because object is general?)
       b)       dim x as Notes.etc....   (early binding, because mentioning the applicatiion name of "Notes."?)

2)    a)   and therefore, these statements are late binding? because object is general?
      Dim Session              As Object 'The notes session
    'Start a session to notes
    Set Session = CreateObject("Lotus.NotesSession")

       b) which is why you explained:

     " Due to the lack of type information, you had to use late-binding, which permits only standard constructions of VBA"

3) From the statement above, what is "type information"?

4) This question is very close to resolution....so would this be ok?
     a) I'd like to award some point value from this question to EE expert: qwaletee, because I appreciate qwalettee's time and contribution
     b) in case other people read this thread in the future, I would like them to read the code with the vbCrLf workaround, if you were able to assist in this matter.          
    c) so I would open a new question for the crLf matter....
    d) when (c) is resolved, would append it to this post, with the entire finished routine as a comment, for future readers.....

    please advise....Sandra

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13738184
1) yes
2) yes
3) type information, maybe better: detailed type information; as you say, Object is very general, and Notes object have many methods and properties

About vbCrLf, that's not too much, although it involves (again) some testing. You need to use NotesRichTextItems, with their calls. Off my head, so not tested anywhere, to replace the line MailDoc...Body..:

    Dim rtitem As Object

    Set rtitem= MailDoc.CreateRichTextItem("Body")
    rtitem.AppendText "line1 with some text"
    rtitem.AddNewline 1
    rtitem.AppendText "another line"

The problem for you is that the text is specified in the calling sub, but RichText cannot handle return/linefeeds (although you might try).

Points? As you seem fit, always fair and square...
0
 

Author Comment

by:mytfein
ID: 13738283
Hi Sjef,

a) Thanks for your clarifications of my questions
b) Thanks for starting me off with a crLf workaround, It's lunch time, and I'm covering the front desk now, so I'll get back to you in an hour or so.....
c) This month, I'm rich in points to give away, because I bought a membership...so opening a new question for extras is a very easy thing for me to do..... :)

will be in touch shortly, thx again, Sandra
0
 

Author Comment

by:mytfein
ID: 13739375
Hi Sjef,

Those statements did the trick.....The body text looks great!
Thanks soooooo much for all your help.

As you know, currently I am settting the body's lines in the --- calling --- routine.
I would like to send parameter to email routine.....This way I hope to have a generic email routine for future use.....

It's time for me to learn how to send parameter array into a routine for:
      a) body lines
      b) recipient addresses

I remember that the reason why I commented out the parmRecipient array is because I was getting syntax errors that I cannot figure out.

By any chance, I know Lotus is your strength, yet can you spot anything wrong with these lines of VBA code:
I checked vba help, and surfed the web a little, yet it's not obvious to me.....

I have to leave early today and will close this question as successfully solved......

p-l-e-a-s-e, if I need to go to a vba bulletin board for help on the syntax error, can I contact you again, if I have any issues,
or to show the finished product,

please advise, and thanks so much, have a nice weekend, Sandra


getting syntax error on:
================
Public Sub LotusNotes2(Subject As String, _
                       Attachment As String, _
                       ParmArrary arrBodyText() As Variant, _
                       Saveit As Boolean, _
                       strBCC As Variant, _
                       Recipient1 As String, _
                       Optional Recipient2 As String, _
                       Optional Recipient3 As String)

rest of email logic that you helped me with.......
-----------------------------------------------------------------------------------------------
calling routine with the body text the old way: (with crlf)
==============================
Public Sub testLotusNotes1(strFileName As String)

Dim strLine1 As String
Dim strLine2 As String
Dim strBody As String

strLine1 = "Hi xxx, This is an automated email"
strLine2 = "Please review attached spreadsheet ...thanks!l"
strBody = strLine1 & vbCrLf & vbCrLf & strLine2

LotusNotes2 "Email auto...", _
            strFileName, _
            strBody, _
            True, _
            Null, _
            "test@test.com"

----------------------------------------------------------------------------------------------
want to change calling routine to use array, something like this:
========================================
Public Sub testLotus(strFileName As String)

Dim arrLine(4) As Variant  'offset 0, 5 elements

arrLine(0) = "text Line 1"
arrLine(1) = "text line 2"

LotusNotes2 "Email auto...", _
            strFileName, _
            arrLine, _
            True, _
            Null, _
            "test@test.com"
End Sub

           
End Sub





0
 
LVL 31

Expert Comment

by:qwaletee
ID: 13745999
Is your last name Fein?  Just wondering.

>                       ParmArrary arrBodyText() As Variant, _

I think this is your problem.  VB sees ParmArray, and thinks that is the name of the parameter, so it expects the next thing to be either:
    as TYPE
    , --for another parameter
    ) --for end of function
    () --to indicate that ParmArray is an array, followed by as comma or close parenthesis as above

But what does it see next? arrBodyText!  That doesn't fit the expected syntax.

So, which is it, a parameter (non array) named ParmArray?  Or an array parameter named arrBodyText?

The rest of what you have -- too confusing, I don't know what yuo have and what you want, and it is sort of a new question anyway.  On approach to you CRLF problem would be to pass an array of strings for the body, and the orutine could take care of looping through them and adding .Addtext(array(#)) followed by .AddNewLine

Don't worry about the early/late binding stuff.  The only advantage late binding gives you is that you can declare specific type in advance (instead of always using Object as type), which means that VB can deal with them more efficiently -- not enough of a difference to notice in light processing.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 13746003
Note VB.Net does have a keyword ParmArray, but that would not apply to regular VB, nor to VBA.
0
 

Author Comment

by:mytfein
ID: 13754553
Hi qwaletee,
a) you've come pretty close in intuiting the user name that I use.... :)
b) I re-read you response several times, I think I understand.....
    1) I coded parmArray based on a search I did in vba help text about using arrays as parameters.
    2) cut and pasted vba text, way-way  below
    3) a) based on your explanation: coded instead:
                                        arrBodyText() As Variant, _
        that worked nicely......
   
         b) modified email logic to work with bodytext as an array like this:
             (changed to option base 1)
           
                Dim rtitem As Object
               Set rtitem = MailDoc.CreateRichTextItem("Body")


               Dim intI As Long
               For intI = 1 To lngLineUsed
                    Debug.Print arrLine(intI)
             
                    rtitem.AppendText arrLine(intI)
                    rtitem.AddNewLine 1
               Next intI


    4) changed recipient array to be defined and initialized in calling module, instead of generic email routine
        finished scipt is below:

======================== calling routine
Public Sub testLotusNotes2()

Dim arrLine(5) As Variant  'offset option base 1, 5 elements
Dim lngLineUsed As Long


arrLine(1) = "test line 1"
arrLine(2) = "test line 2"

lngLineUsed = 2

Dim arrRecipient(2) As Variant     'offset option 1, 5 elements
arrRecipient(1) = "xxx@test.com"
arrRecipient(2) = "yyy@test.com"


Call EmailLotusNotes _
            ("Email auto..", _
            "c:\hr_term.xls", _
            arrLine, _
            lngLineUsed, _
            True, _
            Null, _
            arrRecipient)
End Sub

   
===================== generic email routine
Public Sub EmailLotusNotes(Subject As String, _
                       Attachment As String, _
                       arrLine() As Variant, _
                       lngLineUsed As Long, _
                       Saveit As Boolean, _
                       strBCC As Variant, _
                       arrRecipient() As Variant)
                       
                       
                       
                       
            '           ParmArrary RecipientList() As Variant)
            '  arrBodytext As Variant, _

On Error GoTo Err_EmailLotusNotes

                     
'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.


'Set up the objects required for Automation into lotus notes

    Dim Maildb               As Object 'The mail database
    Dim MailDoc              As Object 'The mail document itself
    Dim AttachME             As Object 'The attachment richtextfile object
   
    Dim Session              As Object 'The notes session
    'Start a session to notes
    Set Session = CreateObject("Lotus.NotesSession")
    Call Session.Initialize("mytfein")
   
       
    Dim EmbedObj             As Object 'The embedded object (Attachment)

    Dim UserName             As String 'The current users notes name
    Dim MailDbName           As String 'THe current users notes mail database name
   
   
   
   
       
   
    '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
   
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & _
                 Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
   
    Dim dir As Object

    Set dir = Session.GetDbDirectory("")
    Set Maildb = dir.OpenMailDatabase()

       
    'Set up the new mail document
    Set MailDoc = Maildb.CreateDocument
   
   
    MailDoc.ReplaceItemValue "Form", "memo"
   
    MailDoc.ReplaceItemValue "Subject", Subject
   
    Dim rtitem As Object
    Set rtitem = MailDoc.CreateRichTextItem("Body")
   
 
    Dim intI As Long
    For intI = 1 To lngLineUsed
              Debug.Print arrLine(intI)
             
              rtitem.AppendText arrLine(intI)
              rtitem.AddNewLine 1

    Next intI

       
    MailDoc.ReplaceItemValue "SendTo", arrRecipient                     ' Recipient"
   
    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.ReplaceItemValue "CreateRichTextItem", ("Attachment")
    End If
   
   
    'Send the document
    MailDoc.ReplaceItemValue "PostedDate", Now() 'Gets the mail to appear in the sent items folder
    MailDoc.Send (False)
   
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
   
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
   
   
Exit_EmailLotusNotes:
    Exit Sub

Err_EmailLotusNotes:
    MsgBox Err.Description
    Resume Exit_EmailLotusNotes
   
   
End Sub


5) I think I understood from Sjef:
     that the email routine likes an exact size recipient array (I guess without without empty elements)
so in the callling routine, would like to experiment with the REDIM statement....however I'm getting the following error:
        array already dimensioned.....on redim statement for the following:

        Dim arrRecipient(2) As Variant     'offset option 1, 5 elements
       ' ReDim arrRecipient(3)         <----  this line got the error, so commented it out

       

        If you'd like to have a look at this error, I would gladly post a new question.....

        Thx again, Sandra




================= from vba help
Understanding Parameter Arrays
   

A parameter array can be used to pass an array of arguments to a procedure. You don't have to know the number of elements in the array when you define the procedure.

You use the ParamArray keyword to denote a parameter array. The array must be declared as an array of type Variant, and it must be the last argument in the procedure definition.

The following example shows how you might define a procedure with a parameter array.

Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant)
    Dim intI As Integer

    Debug.Print strName; "    Scores"
    ' Use UBound function to determine upper limit of array.
    For intI = 0 To UBound(intScores())
        Debug.Print "          "; intScores(intI)
    Next intI
End Sub

The following examples show how you can call this procedure.

AnyNumberArgs "Jamie", 10, 26, 32, 15, 22, 24, 16

AnyNumberArgs "Kelly", "High", "Low", "Average", "High"

============== end vb a help
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 13836637
Sandra,

You still looking for help on this?  It sort of sounds like it is already in fein hands.
0
 

Author Comment

by:mytfein
ID: 13867430
Hi Qwaletee,

Thx for checking in with me....I was away for a few days...

The above script is working nicely.....
I followed your explanation as to how to define an array as a parameter into a procedure....based on howed you explained parameters....

It seems that your technique is different than the vba help info that I pasted above that describes parmArray,
so I was wondering if you agreed or feel otherwise.....

Anyway, I had another question related to the above that I would like to post. May I email you when it has been sent:

My question is related to sending the password "under the hood" to Lotus:
Call Session.Initialize("mytfein")

The vba script resides on my pc in an Access mdb.
When the lotus email get sets sent out, it has my name as the sender.
I really want the lotus email to have my colleague's name as the sender. I guess the script would need to run from her pc.

If I want the vba script to run on my colleague's pc.
I personally do not want to know her Lotus password.
I was thinking that I could retrieve the password from a password table that I could create.
But, again, I do not want to know her personal password.

I wonder if Access will allow password type fields that appear like "******"  that reallly represents a text value....

Would welcome ideas....please advise, if you would like me to email you when this question is posted...thx, Sandra
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13870988
Maybe post a question in the Access TA? I think you can build a form in Access with a password-type of field. Better ask the experts there :)

Nice to see that it works!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

825 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