Importing Email Messages into Excel

P

Patrick Stafford

I am working on a macro to search for email messages that contain "VZ-East"
in the subject and then importing the message into an excel document. I have
the following code, but cannot get it to work or no what else to do. Any
help will be greatly appreciated. Thanks!

Sub import()
Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim objReg As Outlook.MAPIFolder
Dim objPF As Outlook.MAPIFolder
Dim objItem As MailItem

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objPF = objNameSpace.Folders("Local Mailbox")
Set objReg = objPF.Folders("Reg_file")

' Open Excel file first
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
.ScreenUpdating = False
.Visible = False
.workbooks.Open ("C:\PON Email\master.xls")
.DisplayAlerts = False
End With

' For each message, check for subject containing VZ-East
For Each Item In myFol.Items
If InStr(Item.Subject, "VZ-East") > 0 Then
Set objItem = olApp.ActiveInspector.CurrentItem
objItem.SaveAs "C:\Windows\Temp\Postdata.att", olTXT
xlApp.Run "master.xls!DoTheImport"
xlApp.Save
Kill "C:\Windows\Temp\Postdata.att"
' Move msg to [Personal Folders/Reg_file]
Item.Move objReg
End If
Next Item
' Quit excel
xlApp.Quit
End Sub
 
M

Michael Bauer

1. "Item" is not a declared variable
2. There´s no relation between Item, used in the loop, and
Activeinspector.CurrentItem. Instead of the latter one you should save
"Item".
3. Because you want to remove "Item" from within the loop you can´t use
a For-Each loop. Instead you could use:

Dim i as Long
For i=myFol.Items.Count To 1 Step-1
Set Set objItem =myFol.Items(i)
...
Next
 
P

Patrick Stafford

Thanks for the help. I am now getting an error where the email is to be
saved as a text file. It gives me a Run-time error '91': Object variable or
With block variable not set.

I have pasted my code in below and the line with the error is in CAPS.

Sub patrick()

Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim objReg As Outlook.MAPIFolder
Dim objPF As Outlook.MAPIFolder
Dim objItem As MailItem
Dim Item As MailItem

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objPF = objNameSpace.Folders("Local Mailbox")
Set objReg = objPF.Folders("Bobs Stuff")

' Open Excel file first
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
.ScreenUpdating = False
.Visible = False
.workbooks.Open ("C:\PON Email\master.xls")
.DisplayAlerts = False
End With

' For each message, check for subject containing VZ-East
Dim i As Long
For i = myFol.Items.Count To 1 Step -1
Set objItem = myFol.Items(i)

' For Each Item In myFol.Items

If InStr(objItem.Subject, "VZ-East") > 0 Then
Set objItem = olApp.ActiveInspector
OBJITEM.SAVEAS "C:\PON EMAIL\POSTDATA.TXT", OLTXT
xlApp.Run "master.xls!DoTheImport"
xlApp.Save
Kill "C:\Windows\Temp\Postdata.txt"
' Move msg to [Local Mailbox/Inbox/Bobs Stuff]
Item.Move objReg
End If
Next
' Quit excel
xlApp.Quit

End Sub

Thanks for the help.

Michael Bauer said:
1. "Item" is not a declared variable
2. There´s no relation between Item, used in the loop, and
Activeinspector.CurrentItem. Instead of the latter one you should save
"Item".
3. Because you want to remove "Item" from within the loop you can´t use
a For-Each loop. Instead you could use:

Dim i as Long
For i=myFol.Items.Count To 1 Step-1
Set Set objItem =myFol.Items(i)
...
Next

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



Patrick said:
I am working on a macro to search for email messages that contain
"VZ-East" in the subject and then importing the message into an excel
document. I have the following code, but cannot get it to work or no
what else to do. Any help will be greatly appreciated. Thanks!

Sub import()
Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim objReg As Outlook.MAPIFolder
Dim objPF As Outlook.MAPIFolder
Dim objItem As MailItem

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objPF = objNameSpace.Folders("Local Mailbox")
Set objReg = objPF.Folders("Reg_file")

' Open Excel file first
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
.ScreenUpdating = False
.Visible = False
.workbooks.Open ("C:\PON Email\master.xls")
.DisplayAlerts = False
End With

' For each message, check for subject containing VZ-East
For Each Item In myFol.Items
If InStr(Item.Subject, "VZ-East") > 0 Then
Set objItem = olApp.ActiveInspector.CurrentItem
objItem.SaveAs "C:\Windows\Temp\Postdata.att", olTXT
xlApp.Run "master.xls!DoTheImport"
xlApp.Save
Kill "C:\Windows\Temp\Postdata.att"
' Move msg to [Personal Folders/Reg_file]
Item.Move objReg
End If
Next Item
' Quit excel
xlApp.Quit
End Sub
Click to expand...
Click to expand...
 
M

Michael Bauer

Patrick,

please think about what you´re doing. In the loop you want to handle
each MailItem in a list called myFol.Items.

For that you set a reference, called objItem, at the beginning of each
loop on a specified list item, i.e. myFol.Items(i).

Now that you have the reference you want to do something with this
referenced item, i.e. check the item´s subject, save and move the item.

So after checking the item´s subject why do you want to change the
reference on a maybe totally different item? (Not to mention that
ActiveInspector isn´t a MailItem at all.)

And why do you want to move Item while objItem is the one you´re working
on?


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



Patrick said:
Thanks for the help. I am now getting an error where the email is to
be saved as a text file. It gives me a Run-time error '91': Object
variable or With block variable not set.

I have pasted my code in below and the line with the error is in CAPS.

Sub patrick()

Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim objReg As Outlook.MAPIFolder
Dim objPF As Outlook.MAPIFolder
Dim objItem As MailItem
Dim Item As MailItem

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objPF = objNameSpace.Folders("Local Mailbox")
Set objReg = objPF.Folders("Bobs Stuff")

' Open Excel file first
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
.ScreenUpdating = False
.Visible = False
.workbooks.Open ("C:\PON Email\master.xls")
.DisplayAlerts = False
End With

' For each message, check for subject containing VZ-East
Dim i As Long
For i = myFol.Items.Count To 1 Step -1
Set objItem = myFol.Items(i)

' For Each Item In myFol.Items

If InStr(objItem.Subject, "VZ-East") > 0 Then
Set objItem = olApp.ActiveInspector
OBJITEM.SAVEAS "C:\PON EMAIL\POSTDATA.TXT", OLTXT
xlApp.Run "master.xls!DoTheImport"
xlApp.Save
Kill "C:\Windows\Temp\Postdata.txt"
' Move msg to [Local Mailbox/Inbox/Bobs Stuff]
Item.Move objReg
End If
Next
' Quit excel
xlApp.Quit

End Sub

Thanks for the help.

Michael Bauer said:
1. "Item" is not a declared variable
2. There´s no relation between Item, used in the loop, and
Activeinspector.CurrentItem. Instead of the latter one you should
save "Item".
3. Because you want to remove "Item" from within the loop you can´t
use a For-Each loop. Instead you could use:

Dim i as Long
For i=myFol.Items.Count To 1 Step-1
Set Set objItem =myFol.Items(i)
...
Next

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook



Patrick said:
I am working on a macro to search for email messages that contain
"VZ-East" in the subject and then importing the message into an
excel document. I have the following code, but cannot get it to
work or no what else to do. Any help will be greatly appreciated.
Thanks!

Sub import()
Dim olApp As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim myFol As Outlook.MAPIFolder, myAtt As Attachment
Dim objReg As Outlook.MAPIFolder
Dim objPF As Outlook.MAPIFolder
Dim objItem As MailItem

Set olApp = CreateObject("Outlook.Application")
Set objNameSpace = olApp.GetNamespace("MAPI")
Set myFol = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objPF = objNameSpace.Folders("Local Mailbox")
Set objReg = objPF.Folders("Reg_file")

' Open Excel file first
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
.ScreenUpdating = False
.Visible = False
.workbooks.Open ("C:\PON Email\master.xls")
.DisplayAlerts = False
End With

' For each message, check for subject containing VZ-East
For Each Item In myFol.Items
If InStr(Item.Subject, "VZ-East") > 0 Then
Set objItem = olApp.ActiveInspector.CurrentItem
objItem.SaveAs "C:\Windows\Temp\Postdata.att", olTXT
xlApp.Run "master.xls!DoTheImport"
xlApp.Save
Kill "C:\Windows\Temp\Postdata.att"
' Move msg to [Personal Folders/Reg_file]
Item.Move objReg
End If
Next Item
' Quit excel
xlApp.Quit
End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

How to calculate the email statistics of 'on behalf of' (outbound) in amailbox using vba script? 0
Copy Outlook email body to Excel 1
Access Program Only Looping Part Way Through Outlook Inbox 2
Proof Of Looping Bug in Reading Outlook Inbox From Access? 2
Access Outlook from Word with VBA code 1
Setting appointment label 1
Error 91 on last line 2
Problem with AdvancedSearch method 15

Top