?
Solved

Detaching TEXT files from Lotus Notes using VBA

Posted on 2010-09-20
7
Medium Priority
?
1,495 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 34

Accepted Solution

by:
Norie earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

765 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