Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Automatic Signature not appending in Lotus when email generated via VBA

Posted on 2007-07-31
8
Medium Priority
?
3,497 Views
Last Modified: 2013-12-18
Hi

Very new to VBA but from scanning the net and this forum I can't seem to find an answer to my problem(s).

I'm generating an email from Excel to Lotus notes with an attachment.  I can generate the email fine enough but my automatic signature does not append???  Is this because part of the signature file (picture) is html??

Also I was wondering if there is a way to append my exel file into the body of the email as opposed to embeding it in the memo itself?

Current code thus far

Dim Attachment                           As String
Dim ReinstatementRecipient         As String
Dim ReinstatementSubject           As String
Dim Msg                                       As String

Dim NotesSession                    as Object
Dim NotesDatabase                  As Object
Dim NotesDocument                 As Object
Dim NotesAttachment                As Object
Dim EmbedObj                           As Object


ReinstatementRecipient = InputBox("Enter the Customers email address", "Email Address")
ReinstatementSubject = "Reinstatement Form"


Body = "body text"

'Instantiate the Lotus Notes Objects.
    Set notessession = CreateObject("Notes.NotesSession")
    Set NotesDatabase = notessession.GETDATABASE("", "")
     
'If Lotus Notes is not open then open the mail-part of it.
    If NotesDatabase.IsOpen = False Then NotesDatabase.OPENMAIL
   
'Create the e-mail & the attachment
    Set NotesDocument = NotesDatabase.CREATEDOCUMENT
    Set NotesAttachment = NotesDocument.CREATERICHTEXTITEM("Attachment")
    Set EmbedObj = NotesAttachment.EmbedObject(1454, "", "\\filwlg02\InternetChan\IOLB\Fraud Reporting\Forms\Reinstatement Form.doc", "Attachment")
     
'Add values to the created e-mail main properties.
    With NotesDocument
        .Form = "Memo"
        .SendTo = ReinstatementRecipient
        .Subject = Subject
        .Body = Msg
        .SaveMessageOnSend = True
    End With

'Send the e-mail.
    With NotesDocument
        .PostedDate = Now()
        .Send 0, Recipient
    End With
     
'Release objects from the memory.
    Set NotesDocument = Nothing
    Set NotesDatabase = Nothing
    Set NotesSession = Nothing
    Set EmbedObj = Nothing
    Set NotesAttachment = Nothing
   
'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has been sent successfully.", vbInformation
   
    'End Sub
0
Comment
Question by:ngatirauks
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 19608204
(1) The product classes will not automatically append the signature unless you compose the memo in the UI.  You could code it, though, by reading the signature fields from the CalendarProfile profile document and using the MIME classes if necessary to import the html file (not as easy as it sounds).

(2) Appending attachments to the body of the email is the standard procedure for Notes email.  Your icon for the file attachment is displayed at the bottom of the form because there is no "Attachment" field on the Memo form.  Basically, Notes knows there is a file embedded in the document, but does not know where to display it.

You need to use the NotesRichText classes to assemble the Body as RichText (or MIME if you need it).  When you use statements like doc.Body = Msg, the body field is treated as plain text.  Here's an example of your code re-written to use the NotesRichText classes.  I have taken the liberty of removing unused variables and unnecessary lines of code.

      Dim ReinstatementRecipient As String
      Dim ReinstatementSubject As String
      Dim Body As String
      
      Dim NotesSession As Object
      Dim NotesDatabase As Object
      Dim NotesDocument As Object
      Dim NotesBody As Object
      
      ReinstatementRecipient = Inputbox("Enter the Customers email address", "Email Address")
      ReinstatementSubject = "Reinstatement Form"
      Body = "body text"
      
      'Instantiate the Lotus Notes Objects.
      Set notessession = CreateObject("Notes.NotesSession")
      Set NotesDatabase = notessession.GETDATABASE("", "")
      Call NotesDatabase.OpenMail
      
      'Create the e-mail
      Set NotesDocument = NotesDatabase.CREATEDOCUMENT
      With NotesDocument
            .Form = "Memo"
            .SendTo = ReinstatementRecipient
            .Subject = ReinstatementSubject
            .SaveMessageOnSend = True
      End With
      
      ' Add the message body.
      Set NotesBody = NotesDocument.CREATERICHTEXTITEM("Body")
      Call NotesBody.AppendText(Body)
      Call NotesBody.AddNewline(2)
      Call NotesBody.EmbedObject(1454, "", "\\filwlg02\InternetChan\IOLB\Fraud Reporting\Forms\Reinstatement Form.doc")
      
      'Send the e-mail.
      Call NotesDocument.Send()
      
      'Release objects from the memory.
      Set NotesDocument = Nothing
      Set NotesDatabase = Nothing
      Set NotesSession = Nothing
      Set NotesBody = Nothing
      
      'Activate Excel for the user.
      Appactivate "Microsoft Excel"
      Msgbox "The e-mail has been sent successfully.", vbInformation
0
 

Author Comment

by:ngatirauks
ID: 19613620
Awesome, thanks heaps I gathered I had to tinker with the RichTextItem as I have played around with this object to change text font etc but kept late binding the object in regards to appending the attachment??!!??  But it all makes sense now, newbie I know lol.

In regards to the automatic signiture i have tried to program the code pulling the info from the calender profile but I get errors, vague I know but I'm not at my usual desk so can't check my macro.  I remember something about it stating I dont have access to a particular calender profile if I recall.  I also have found on my travels many instances of people stating you cannot import html into lotus it just doesn't like it??  Is this in fact not the case??  Any tips would be great for this.  To work around this  i've decided to stay with a rudimentary system of appending a variable with some set txt for the signature instead not very elegant but it works.

Also I've found some code that I played with for creating a new document in UI but it's all early bound and my system just won't take it so I have to late bind everything, but I'm not very good with the object classes so get tons of errors.  Do you have any code for creating an email with UI that I can play with?

Thanks again.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19616452
The addition on the signature is controlled by code in the Notes Memo form.  Your VB code is not using the form, it os creating the message directly in the mail file.

The same thing can happen in Outlook -- if you use an external program to create a message, the signature will appear only if the program requests Outlook to create the message, not if it creates the message directly.

So, you either have to change your code to use the Notes UI and its memo form (not create the message directly), or else you need to recreate the signature processing code in your VB script.  Bill gave you an option to do the latter.  It may b e just as easy to do the former.  Here's a code sample:

Body = "body text"

'Instantiate the Lotus Notes Objects.
    Set notessession = CreateObject("Notes.NotesSession")
    Set NotesDatabase = notessession.GETDATABASE("", "")
    NotesDatabase.OPENMAIL
    Set notes_UI = CreateObject("Notes.NotesUiWorkspace")
   
'Create the e-mail & the attachment
    Set NotesMemo= Notes_UI.ComposeDocument ( NotesDatabase.Server, NotesDatabase.FilePath, "Memo" )
     
'Add values to the created e-mail main properties.
    With NotesMemo
        .EditGotoField "EnterSendTo"
        .InsertText ReinstatementRecipient
        .EditGotoField "Subject"
        .InsertText Subject
        .EditGotoField "Body"
        .InsertText Msg
        .Send
        .Save
    End With
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 22

Expert Comment

by:Bill-Hanson
ID: 19616928
The problem with using the UI is that you loose the ability to insert the file attachment.  Also, you cannot format any of the text in the body.  The solution is to use both methods in tandum:  A temp doc for creating the rich text and a uidoc for the signature.  Try this function.  It has been tested using VBA within MS Excel.

Sub SendFileMemo(ByVal sendto As String, ByVal subject As String, ByVal body As String, ByVal filePath As String, ByVal saveMemo As Boolean)

    Dim ws As Object
    Dim sess As Object
    Dim db As Object
    Dim docTemp As Object
    Dim uidocTemp As Object
    Dim uidocMemo As Object
    Dim rtBody As Object
   
    Const EMBED_ATTACHMENT = 1454
   
    ' Init
    Set sess = CreateObject("Notes.NotesSession")
    Set ws = CreateObject("Notes.NotesUiWorkspace")
    Set db = sess.GetDatabase("", "")
    Call db.OpenMail
    filePath = Trim(filePath)
   
    ' Create a new temporary NotesDocument in the user's mail file (Form="Memo").
    Set docTemp = db.CreateDocument
    docTemp.Form = "Memo"
    Set rtBody = docTemp.CreateRichTextItem("Body")
    Call rtBody.AppendText(body)
    If (filePath <> "") Then
        Call rtBody.AddNewline(2)
        Call rtBody.EmbedObject(EMBED_ATTACHMENT, "", filePath)
    End If
    Call docTemp.save(True, False)
   
    ' Display the temp doc in the UI.
    Set uidocTemp = ws.EditDocument(True, docTemp)
   
    ' Copy the rich text to the clip board, close the window, and delete the temp doc.
    Call uidocTemp.GotoField("Body")
    Call uidocTemp.SelectAll
    Call uidocTemp.Copy
    uidocTemp.Document.SaveOptions = "0" ' prevent prompt.
    uidocTemp.Document.MailOptions = "0" ' prevent prompt.
    Call uidocTemp.Close
    Call docTemp.Remove(True)

    ' Compose the real email document.
    Set uidocMemo = ws.ComposeDocument(db.Server, db.filePath, "Memo")
    Call uidocMemo.FieldSetText("EnterSendTo", sendto)
    Call uidocMemo.FieldSetText("Subject", subject)

    ' The memo now has everything but the rich text from the temp doc.  The signature should be at the bottom of the memo.  Now, we just paste the rich text into the body.
    Call uidocMemo.GotoField("Body")
    Call uidocMemo.Paste
   
    ' Send the memo.
    Call uidocMemo.Send
    If (saveMemo) Then Call uidocMemo.save
    Call uidocMemo.Close
   
End Sub
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19619147
Merging of the minds, eh?
0
 
LVL 22

Accepted Solution

by:
Bill-Hanson earned 2000 total points
ID: 19619175
Isn't that a cool function?  :)
I about wet my pants when I got it to work!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 19619932
Obviously, your work Depends on Lotus Notes

I've done something similar, but in  the opposite order.  I create the memo in the UI, save it, close it, open the back end and add the attachment, and send.  Doesn't look as nice, but avoids the extra temp doc.

You can also fool around with the no-longer-documented DDE interface, in which case, you just use VB on the uiDocument (never the back end document), and when all is ready except the file attachment, issue the [EditInsertFileAttachment], [Send], and [Save] @Commands. Have to e careful though, the interface is kinda fragile.
0
 

Author Comment

by:ngatirauks
ID: 19635664
Fantastic thanks guys

Bill some Champagne VBA there thanks alot very muchly appreciated!!
0

Featured Post

Technology Partners: 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

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Suggested Courses

580 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