Solved

Detaching TEXT files from Lotus Notes using VBA

Posted on 2010-09-20
7
1,424 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now