Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1514
  • Last Modified:

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

0
amfierst
Asked:
amfierst
  • 3
  • 3
1 Solution
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
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 your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now