Automatic Signature not appending in Lotus when email generated via VBA

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
ngatirauksAsked:
Who is Participating?
 
Bill-HansonCommented:
Isn't that a cool function?  :)
I about wet my pants when I got it to work!
0
 
Bill-HansonCommented:
(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
 
ngatirauksAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
qwaleteeCommented:
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
 
Bill-HansonCommented:
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
 
qwaleteeCommented:
Merging of the minds, eh?
0
 
qwaleteeCommented:
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
 
ngatirauksAuthor Commented:
Fantastic thanks guys

Bill some Champagne VBA there thanks alot very muchly appreciated!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.