Excel97 macro to archive Outlook mailbox

G

Geo

I need a macro that will be able to read email from Outlook and create an
archive PST file according to certain date criteria.

I have a bit of code that will access my inbox and tell me the subject
headers of the mails within certain dates. However, I need to access the
inbox of another mailbox that I have access to and does not contain my
default inbox.

So first question is: How do I get the macro to access the inbox of another
Mailbox?

Secondly, what command do I need to use to make the PST file?

I'm limited to Excel97 and Outlook97.

Here is my function as it stands at the moment:

Private Function ArchiveEmail(StartDate As Date, EndDate As Date) As Boolean
Dim returnState As Boolean
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Dim EmailItemCount As Integer
Dim OLF As Outlook.MAPIFolder
Dim subjectLog As String
Dim em

'assume success
returnState = True

'set the error trap
On Error Resume Next 'GoTo Connect_Error

Set olApp = GetObject("Outlook.Application")
If Err <> 0 Then
'outlook not running so start it
Set olApp = CreateObject("outlook.application")
End If

Set OLF = GetObject("",
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
EmailItemCount = OLF.Items.Count

subjectLog = "Email count: " & EmailItemCount
Set olEmail = olApp.Item(olMailItem)
For Each em In OLF.Items
With em
If .ReceivedTime <= StartDate _
And .ReceivedTime >= EndDate _
Then
subjectLog = subjectLog & vbCrLf & .Subject
End If
End With
Next

MsgBox subjectLog

Archive_Exit:
ArchiveEmail = returnState
Exit Function

Connect_Error:
returnState = False
MsgBox Err.Number & ": " & Err.Description, vbOKOnly +
vbApplicationModal + vbCritical, "Archive Email"
Resume Archive_Exit
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top