Customer: I'd like to have a spreadsheet built which gives me the data in this view's format but does a calculation on this date to tell me how many days this item is overdue, and I'd like that spreadsheet to be emailed to me weekly.
Me: Well, I definitely know how to get the spreadsheet built including that date/days calculation, but I'm not aware of a way to grab that built spreadsheet and attach it to an email to you and send it all in one shot, especially with a scheduled agent.
Customer: Well, see what you can do..
Me: Alright, I'll look into it.
And within minutes on LDD I find this: E-Mail Attachments Using MIME
Me: Hmm, I think I may be able to do something with this...
So I do the spreadsheet build including reading a filename and path from a profile document referring to a share on the local drive of the Notes Server. Then I call a sub, passing the xlFileName and the handle to that profile document
Call SendMIMEAttachmentEmail(xlFilename, profiledoc)
And here's that sub:
Sub SendMIMEAttachmentEmail(fileName As String, profiledoc As NotesDocument)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim parent As NotesMimeEntity
Dim child1 As NotesMimeEntity
Dim child2 As NotesMimeEntity
Dim header As NotesMimeHeader
Dim stream As NotesStream
Dim filenameonly As String
Dim datestring As String
datestring = Format(Now(), "mmddyy")
filenameonly = Strrightback(fileName,"\")
session.ConvertMime = False ' We do want Notes to convert MIME to Rich Text
Set db = session.CurrentDatabase
Set doc = db.CreateDocument
Call doc.ReplaceItemValue("Form", "Memo")
Call doc.ReplaceItemValue("Subject", "Items Past Due as of " _
& Format$(Now, "mmm dd yyyy"))
' Create all the Entities - one parent, three children.
Set parent = doc.CreateMIMEEntity
Set child2 = parent.CreateChildEntity
Set child1 = parent.CreateChildEntity(child2) ' Create so that children 1 & 2 are siblings
' Add a couple of headers to child 2 (that will hold the attachment).
Set header = child2.CreateHeader("Content-Transfer-Encoding")
Call header.SetHeaderVal("binary")
Set header = child2.CreateHeader("Content-Disposition")
Call header.SetHeaderVal("attachment; filename=" + datestring + "_" + filenameonly )
' Build the HTML message content. This HTML goes into child 1
Set stream = session.CreateStream
Call stream.WriteText({Here is an excel file of the current Past Due Items for your reference.
})
Call child1.SetContentFromText(stream, "text/html", ENC_NONE)
Call stream.Truncate
' Bring the attachment into its entity (child 2)
Set stream = session.CreateStream
If Not stream.Open(fileName, "binary") Then
Msgbox "Could not open " & fileName, 16, "Open failed"
Goto ExitSub
End If
If stream.Bytes = 0 Then
Msgbox "File " & fileName & " has no content", 16, "No Content"
Goto ExitSub
End If
Call child2.SetContentFromBytes(stream, "text/plain", ENC_NONE)
Call child2.EncodeContent(ENC_IDENTITY_8BIT)
Call stream.Close
doc.SendTo = profiledoc.P_CoordGroup
Call doc.Send (False)
Print "Email with Excel file sent"
ExitSub:
session.ConvertMime = True ' Reset the value
End Sub
After returning to the main agent, the following is done to remove the created file, so that no footprint of the built excel file is left on the server share directory.
On Error 75 Resume Next 'just in case we get an error with the kill command if the file doesn't exist
Kill xlFilename
Print "Excel file purged from server"
Customer: Wow!
Me: Yeah, I was quite surprised to find a way to do it, but there it is...
This may be old-hat to some of you, but it was definitely news to me. Thought I'd share for anyone else who may not have come across it but has a need to do something like this.
Have a good weekend everyone!
