Back to jkp-ads.com |
Ron de Bruin
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
The code examples on this page are created by Excel MVP Dennis Wallentin
but are not on his site anymore. But Dennis allow me to publish it on my
site for all the Excel/Lotus Notes users in the world.
The
information in this article applies to:
Microsoft Excel 2000 and
later.
Lotus Notes 5.0 and later.
Task:
Iterate through all the e-mails in the Inbox,save the attached files and
delete the e-mails.
VBA code :
Option Explicit Const stPath As String = "c:\Attachments\" Const EMBED_ATTACHMENT As Long = 1454 Const RICHTEXT As Long = 1 Sub Save_Attachments_Remove_Emails() Dim noSession As Object Dim noDatabase As Object Dim noView As Object Dim noDocument As Object Dim noRemoveDocument As Object Dim noNextDocument As Object 'Embedded objects are of the datatype Variant. Dim vaItem As Variant Dim vaAttachment As Variant 'Instantiate the Notes session. Set noSession = CreateObject("Notes.NotesSession") 'Instantiate the actual Notes database. '(Here is the personal e-mail database used and since it's a 'local database no reference is made to any server.) Set noDatabase = noSession.GETDATABASE("", "mail\xldennis.nsf") 'Folders are views in Lotus Notes and in this example the Inbox 'is used. Set noView = noDatabase.GetView("($Inbox)") 'Get the first document in the defined view. Set noDocument = noView.GetFirstDocument 'Iterate through all the e-mails in the view Inbox. Do Until noDocument Is Nothing Set noNextDocument = noView.GetNextDocument(noDocument) 'Check if the document has an attachment or not. If noDocument.HasEmbedded Then Set vaItem = noDocument.GetFirstItem("Body") If vaItem.Type = RICHTEXT Then For Each vaAttachment In vaItem.EmbeddedObjects If vaAttachment.Type = EMBED_ATTACHMENT Then 'Save the attached file into the new folder. vaAttachment.ExtractFile stPath & vaAttachment.Name 'Set the e-mail object which will be deleted. Set noRemoveDocument = noDocument End If Next vaAttachment End If End If Set noDocument = noNextDocument 'Delete the e-mails which have an attached file. If Not noRemoveDocument Is Nothing Then noRemoveDocument.Remove (True) Set noRemoveDocument = Nothing End If Loop 'Release objects from memory. Set noRemoveDocument = Nothing Set noNextDocument = Nothing Set noDocument = Nothing Set noView = Nothing Set noDatabase = Nothing Set noSession = Nothing MsgBox "All the attachments in the Inbox have successfully been saved" & vbCrLf & _ "and the associated e-mails have successfully been deleted.", vbInformation End Sub
Comments:
The above example can be useful if users
regular receive e-mails with attachment
that will be used in other
processes.