Detaching TEXT files from Lotus Notes using VBA

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

amfierstAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
amfierstAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
amfierstAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
amfierstAuthor Commented:
My code issue was resolved using ConvertMIME code...thanks everyone for your help!
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.