+++Outlook Automation

[fi.or.jp.de]

Hi All,

I am using outlook 2003 & excel 2003.

I need a excel macro to identiy the current or Active mail clicked or
opend in outlook mail box.

The details like subject & sender name should be stored in my excel
workbook.

Please let me know how to achieve this.

Eagerly looking for response.

Thanks in advance

 

[dksaluki]

Here is the following code with macro in Outlook. You'll have to go
to Tools > References... and select the most recent Microsoft Excel X.
0 Object library. Also, in this example, Excel needs to be open and a
cell selected. (unless you want to add more debuggin code) I have
office 2007, but this should definately get you started.

Sub InfoToExcel()
Dim xlApp As Excel.Application
Dim objFolder As Outlook.MAPIFolder
Dim objItem As Outlook.MailItem
Dim myCell As Excel.Range

On Error GoTo err_handler

'Create a reference to active cell in Excel (must be open and cell
selected)
Set xlApp = GetObject(, "Excel.Application")
Set myCell = xlApp.ActiveCell

'set object to whatever is selected in Outlook
Set objFolder = _
Application.ActiveExplorer.CurrentFolder
Set objItem = objFolder.Items(1)

'put values into Excel
myCell.Value = objItem.SenderName
myCell.Offset(0, 1).Value = objItem.Subject

'Clear memory
Set xlApp = Nothing
Set objFolder = Nothing
Set objItem = Nothing
Set myCell = Nothing
Exit Sub

err_handler:
MsgBox "Make sure Excel is open and cell is selected"


End Sub

 

[fi.or.jp.de]

I am getting output but not the selected inbox item details.

thanks for your input.

Even If i get a message box of sendername or subject. that will be
fine.

I will do the rest piece what ever required.

 

[dksaluki]

what kind of output are you getting? Did you run this code from
within Outlook or Excel? Did you make sure that you had a message
selected in your Inbox before you ran code? we could try it from
within Excel: (this is about as bare bones as you can get) Need to go
to Tools>References... and this time pick Microsoft Outlook X.0
library.

Sub ListEmail()
Dim olApp As Outlook.Application
Dim folder As Outlook.MAPIFolder
Dim myItem As Outlook.MailItem

'Code run from Excel
'Create a reference to Inbox folder
Set olApp = GetObject(, "Outlook.Application")
Set folder =
olApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set myItem = olApp.ActiveExplorer.Selection.Item(1)

MsgBox myItem.Subject

End Sub


let me know if this doesn't work.

 

[fi.or.jp.de]

Amazing,....... You are genius

Believe it or not I have searched Web site for this one for more than
1 day....

you made my day....

thank you very much....

I want to learn excel to outlook automations, let me know the books to
refer.

 

[dksaluki]

glad it worked!
Some good VBA books for Excel are "Excel 2003 Power Programming with
VBA" by John Walkenbach. "Excel VBA Programming for Dummies" is also
a really good book. Just go through it all and do all the exampls.
Another really good tool is recording macros in Excel and messing
with the code to try random things. If you want to do more Outlook
things, just learn that you're always going to need to create objects
that "get" whatever it is in Outlook that you want. (i.e. Folders,
Calendars, Mail Messages, etc...). In order to do THAT...you need to
create object(s) that refer to Outlook itself.

Enjoy!