Solved

Detaching TEXT files from Lotus Notes using VBA

Posted on 2010-09-20
7
1,456 Views
Last Modified: 2013-11-27
I've seen several great posts on detaching a RICHTEXT attachment from a RICHTEXT Lotus Notes email using VBA.  What I'm trying to accomplish, however, is detaching a TEXT file (microsoft excel file; type = 1280, as opposed to type = 1 for RICHTEXT files).  The Note I'm reading was automatically generated from VBA in a spreadsheet.  Can anyone help?  Thanks in advance!  Attached is the code to create the email, and then the code to detail the email.

CODE THAT CREATES RICHTEXT EMAIL WITH ATTACHMENT:

    Dim UserName, MailDbName, MailMessage As String
    Dim Maildb, MailDoc, Session, EmbedObj1, AttachME As Object
    Dim RichTextBody, RichTextAttachment As Object
' Open and locate current LOTUS NOTES User
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    Else
        Maildb.OPENMAIL
    End If
    Set MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
    Recipient = "some email userid"
    MailDoc.SendTo = Recipient
    MailDoc.Copy = "some email user id"
    MailDoc.Subject = "Email subject"
    MailMessage = "Software audit results attached."
    Set RichTextBody = MailDoc.CreateRichTextItem("Body")
    MailDoc.Body = MailMessage
    MailDoc.SAVEMESSAGEONSEND = True
    Set RichTextAttachment = MailDoc.CreateRichTextItem("Attachment")
    Set NotesAttach = RichTextAttachment.EmbedObject(1454, "", Attachment1)
'    Set AttachME = MailDoc.CreateRichTextItem("Attachment1")
'    Set EmbedObj1 = AttachME.EmbedObject(1454, "Attachment1", Attachment1, "")
    MailDoc.PostedDate = Now()
    On Error GoTo ErrorHandler1
    MailDoc.Send 0, Recipient
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set Session = Nothing
    Set EmbedObj1 = Nothing
    Set AttachME = Nothing

CODE THAT READS AND TRIES TO DETACH FILE FROM LOTUS NOTE

Const sPathToSave = "C:\EMAIL_EXTRACTS\"
Dim View, nDoc As Object
Dim NotesSubject As Variant
Const RICHTEXT = 1
Const TEXT = 1280
Const EMBED_ATTACHMENT = 1454
Set s = CreateObject("Notes.Notessession")    'create notes session
Set db = s.GETDATABASE("", "")   'set db to database not yet named
Call db.openmail
Set View = db.GetView("($Inbox)")
Set nDoc = View.GetFirstDocument
Dim itm As Variant
While Not (nDoc Is Nothing)
    If nDoc.HasEmbedded Then
        Set itm = nDoc.GetFirstItem("Body")
        MsgBox itm.Type
        If itm.Type = RICHTEXT Then
            Dim attch As Variant
            For Each attch In itm.EmbeddedObjects
                If (attch.Type = EMBED_ATTACHMENT) Then
                    NotesSubject = nDoc.GetItemValue("subject")(0)
                    If NotesSubject = "*IBM Confidential: Software Audit Results" Then
                        attch.ExtractFile sPathToSave & attch.Name
                    End If
                End If
            Next attch
        End If
    End If
 Wend

0
Comment
Question by:amfierst
  • 3
  • 3
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33722279
Where is the code to "Create" a "TEXT" email with attachment?
(you only posted code for Rich Text)
This may be the key to the entire issue...

In any event,
Did you try:

    If itm.Type = TEXT Then

instead of:
   
    If itm.Type = RICHTEXT Then

...in the "Extract" code?

JeffCoachman
0
 

Author Comment

by:amfierst
ID: 33722865
Hi Jeff,

Yes, I did try "If itm.Type = TEXT"...it gets past if statement but then throws an error on the statement after that.

My code for creating the email was included in the original post.  It should be creating a RICHTEXT email with attachment, but instead creates a TEXT attachment.

Thanks,

Anne Marie
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33725997
But I thought you said it was working?
<CODE THAT CREATES RICHTEXT EMAIL WITH ATTACHMENT:>

First there are syntax issues with your code:
Code lines like this:
Dim Maildb, MailDoc, Session, EmbedObj1, AttachME As Object
...have multiple declarations in one line.
Despite what you might think, only the last variable, "AttachME", will actually be declared as an Object, all of the preceding variables will be "Variants"
So use the more modern syntax of:
Dim objMaildb as Object
Dim objMailDoc as Object
Dim objSession as Object
...
See here for more info on this:
http://www.vb6.us/tutorials/vb6-variable-scope

Where did you get this code?
You first need to first determine if it is working or not, *then* try to modify it.
You also need comments in the code so you understand what is being done and why.
Can you contact the author of this code?

Also, please click the "Request Attention" link and ask that the Lotus Notes zone be added to this question

;-)

JeffCoachman
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:amfierst
ID: 33726227
Jeff, thanks for your feedback.

The code to both create a Lotus Note with the attachment and detach an embedded object are working...I got the code somewhere on this message board.  When I run the code against my Lotus Notes inbox, I am able to detach, for example, a powerpoint presentation that was sent to me as an attachment.

What's not working is detaching the attachment I attached using vba.  I created a Lotus Note via vba and attached an excel file to it.  Now I'm trying to detach that excel file with vba and that's what's not working.  The Excel file attached previously through vba is showing as a TEXT file instead of a RICHTEXT file so when I go through the "detach" code, it skips over the IF statement "If itm.Type = RICHTEXT".  

Thanks for your tips...I'm new at all this so they're greatly appreciated!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33726847
ok,

That is why I recommend posting this to the Lotus Notes zone as well.

<I got the code somewhere on this message board>
Then return to that post and click the link to the author's profile.
See if they posted any contact information

;-)

JeffCoachman

0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 33730976
Could the error be that it's unlikely that a TEXT item will have any embedded objects?

It's just text after all.:)

Also have you considered looking at the EmbeddedObjects of the Document?

You might also want to look at GetAttachment, but you would need the filename for that.
0
 

Author Comment

by:amfierst
ID: 33851036
My code issue was resolved using ConvertMIME code...thanks everyone for your help!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

803 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