asked on

VBScript: Open ZIP>Extract ZIP from Within Lotus Notes

Hello Experts.

What I'm needing to do is fairly simple (on paper), however, script wise I'm not sure. I receive a .ZIP file everyday in my Lotus Notes Inbox. This .ZIP file is nothing more than a .CSV report. What I'd like the script to do is open the ZIP attachment from within My Lotus email. The subject line for this report I receive is always the same, so perhaps we could filter on that so it knows exactly what emails to act on.

Once the script is able to extract the .CSV from the ZIP, I'd like to then remove ALL COLUMNS in that .CSV file, but the following:

1.) Event TimeStamp (UTC)
2.) Network Node Address
3.) Target User
4.) Source Computer

It would drop the newly formatted .CSV file on my desktop. Finally, it would then do a "Sort By A to Z" function on column "Target User"

The end result output would look like this:

Sjef Bosman

Well, you can write some code that
- saves the attachment
- calls a batch file (using the Shell command) to extract the CSV from the ZIP file
- or use Shell directly to call the Unzip executable

You can open Excel from within Lotus Notes and handle the rest there.

RobSampson

Hi, here's some code that will go through your Inbox for a document containing the strSubjectToFind text, and detach each attachment.

Which zip program do you have available to unzip it?

Regards,

Rob.

strPassWd = InputBox("Enter your Lotus Notes password:", "Lots Notes Password")
strFolderName = "($Inbox)"
strSubjectToFind = "Consistent Subject of Message"
strTargetAttachmentFolder = "C:\Temp\"

If Right(strTargetAttachmentFolder, 1) <> "\" Then strTargetAttachmentFolder = strTargetAttachmentFolder & "\"
' Initialize Notes
Set objSession = CreateObject("Lotus.NotesSession")
Call objSession.Initialize(strPassWd)
' Open current user's mail file.
strServer = objSession.GetEnvironmentString("MailServer", True)
strFile = objSession.GetEnvironmentString("MailFile", True)
Set objDB = objSession.GetDatabase(strServer, strFile, False)
' Open and read the specified folder.
On Error Resume Next
Set objFolder = objDB.GetView(strFolderName)
If (objFolder Is Nothing) Then
	MsgBox "Error attaching to folder " & strFolderName
ElseIf (objFolder.AllEntries.Count = 0) Then
	MsgBox strFolderName & " contains no messages."
Else
	Err.Clear
	On Error GoTo 0
	Set objDoc = objFolder.GetFirstDocument
	WScript.Echo "Messages in " & strFolderName
	Do Until (objDoc Is Nothing)
		strSubject = objDoc.GetItemValue("Subject")(0)
		If InStr(strSubject, strSubjectToFind) > 0 Then
			strBody = Replace(objDoc.GetFirstItem("Body").Text, vbCrLf, vbLf)
			WScript.Echo strSubject
			For Each objFile In objDoc.GetFirstItem("Body").EmbeddedObjects
				WScript.Echo objFile.Name
				Call objFile.ExtractFile(strTargetAttachmentFolder & objFile.Name)
			Next
		End If
		Set objDoc = objFolder.GetNextDocument(objDoc)
	Loop
End If

WScript.Echo "Finished"

VBScript: Open ZIP&gt;Extract ZIP from Within Lotus Notes

VBScript: Open ZIP>Extract ZIP from Within Lotus Notes