Outlook VBA help

[boxboy30]

Could someone help me create a code in Outlook that would parse certain information from my email and then input it into an exsisting excel spreadsheet?

 

[boxboy30]

Aha! You are too smart Mister! I removed that part of the code...and without it, you are absolutely correct! It runs on whatever spread I have open.

Thanks a bunch!

 

[NewOrderFac33]

Good afternoon,

This topic is pretty much what I thought I was loking for - I have been developing in Excel VBA for many years but have never had the need to touch Outlook until now. Like the other chap, I need to be able to trap key attributes from an open email message such as date and time sent, sender, recipients and cc recipients and message body. I then need to be able to concatenate these into one string and past the result into a text box in an Excel worksheet.
Whilst I was hoping I could use this example with minimum modification, Excel reports "User defined Type Not defined" when I run the code, with "Dim OLF As Outlook.MAPIFolder" highlighted.

I know I should know what this means, but any asistance with this or in adapting the code to meet the requirement described above would be extremely welcome!

P.S. I can't see "References" under "Tools" in either the Excel or the Outlook menu - am I doing something wrong?

Thanks in advance

Pete

 

[Trevor G]

Welcome to the Forum Pete

In the Excel VBA Screen you have to select to use the Outlook object, so go to the Tools Menu > References > Scroll Down until you find Microsoft Outlook XX .Object Library and tick the box (XX being the verison number you are using), then based on the code in the thread it would work....

 

[NewOrderFac33]

Hi, Trevor - thanks for the swift reply!

I found the correct Tools References (Duh!), followed the instructions and restarted both Excel and Outlook, but I still get the same error message.
Any thoughts?

Pete

 

[Trevor G]

Can you show the code you are using and highlight the issue please.

 

[NewOrderFac33]

It's the code that you suggested to the previous poster (see below)

When I attempt to run it, Excel reports:

Compile error: user defined type not defined" with the line beginning "Dim OLF..." highlighted.

Obviously, this code is designed to (I think) take details from a group of emails and paste them to a worksheet - what I need to know is how to make it work for just one open email.

Thanks in advance.

Pete

Sub ListAllItemsInInbox()
'You have to Add the reference to Outlook
'Select Tools > References > Search for Microsoft Outlook XX.Object Library
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
Sheets.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Recieved"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A1:D1").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i < EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, "dd.mm.yyyy hh:mm")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A:D").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False
End Sub

 

[Trevor G]

Check the references again to see if the Microsoft Outlook appears near the top of list in the References.

You can always add a fitler to the code to give you a specific type of email once in an excel workbook.

 

[boxboy30]

hey Pete! i was able to figure it out!!!!

Try this code:


Sub ExportToExcel()

Dim msg As Outlook.MailItem
Dim nms As Outlook.NameSpace
Dim fld As Outlook.MAPIFolder
Dim itm As Object
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder

If fld Is Nothing Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
End If

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sRow As Integer

Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Add
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets(1)
wks.Activate
appExcel.Application.Visible = True
appExcel.Application.DisplayAlerts = False
Windows(appExcel.Application.ActiveWorkbook.Name).Activate


'On Error Resume Next

For Each itm In fld.Items
sRow = sRow + 1
Range("A" & sRow).Value = itm.SentOn
Range("B" & sRow).Value = itm.SenderEmailAddress
Range("C" & sRow).Value = itm.Subject
Range("D" & sRow).Value = itm.Body
Range("D" & sRow).Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("D" & sRow).Replace What:="" & Chr(13) & "", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("F" & sRow).Value = itm.SentOn
Range("G" & sRow).Value = itm.ReceivedTime
Next itm

appExcel.Application.DisplayAlerts = True
Set appExcel = Nothing
Set wkb = Nothing
Set wks = Nothing
Set msg = Nothing
Set nms = Nothing
Set fld = Nothing
Set itm = Nothing

End Sub

I think you might find it to be very empressive! I'm proud of myself on this one!

 

[NewOrderFac33]

Hi, BoxBoy - I pasted the code into Outlook, but it reported: "User defined Type not defined" for line: Dim appExcel As Excel.Application.

I have checked all the References and I have checks against:

Visual Basic for Applications
Microsoft Outlook 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Excel 11.0 Object Library

What am I doing wrong?

Pete

 

[NewOrderFac33]

Trevor,

You can see from my previous reply that I think that everything that I need is installed - not sure where I'm going wrong on this one...

Pete