Archive messages using Form and macro

[masonb404]

Hi everyone,
I know some VBA with excel and Outlook. I'm by no means an expert like you. I was wondering if you could help me, or offer suggestions, on my approach for a little project.

My co-worker and I need to archive and record e-mail message. I need to save the e-mail message and also document certain parts of that message in an excel sheet.
My approach is use a macro in Outlook and a customized Form. I want to set the Form as a default for replying to messages. The Form will contain two customizations: a drop down box and an empty text box. The drop down box will list 25 different sender's names and the empty text box will be used by my co-worker to help classify the e-mail type. The macro in Outlook will control two Excel sheets: the SAVE SHEET (for archiving) and the SUMMARY SHEET (for recording).

The SAVE SHEET uses two columns. Column A contains a list of Form’s 25 names. Column B contains a list of 25 different directory paths where the message are saved to. The macro in Outlook gets the value of name which is selected in the Form's dropdown box and uses it to find the correct name listed down Column A. Once found, the macro will use the corresponding save directory path listed Column B.

For the SUMMARY SHEET, each Row represents one e-mail message. Four Columns are used: Column A has the date of the message, Column B will have the sender's e-mail address, Column C will contain the e-mail’s message, and Column D has the e-mail classification from the Form’s text box.

What do you think? Will my approach work? Can I use macros in Outlook to retrieve data from a message and it’s Form, and put the data into excel? I have used macros in Excel before to move data and manipulate other applications (e.g., Adobe, Outlook, IE, etc.). But I just want to know if I’m conceptualizing the use of Outlook Forms in the correct manner.

Any thoughts or suggestions would be GREATLY appreciated!


Snip-its of code expressing my approach:

Code in Form for dropdown box:

' Sets the name of page on the form (Message)
Set FormPage = Item.GetInspector.ModifiedFormPage("Message")
' Sets Control to a drop down box called ComboBox1.
Set Control = FormPage.Controls("ComboBox1")
' Assign values to the list box.
Control.PossibleValues = "Name1;Name2;Name3;Name4"

Code for extracting messages data:

'needed to focus on Reply Message window:
Set objmailitem = Application.ActiveInspector.CurrentItem
'this gets VB to focus on reply message
If objmailitem.Class = olMail Then
Set oMailItem = objmailitem
End If
'needed to get text from message
Set oNS = Application.GetNamespace("MAPI")
'put message into String
sBody = oMailItem.Body
'put message into excel sheet
Sheet.Activate
Sheet.Range("C" & lRowCount).Value = sBody

I'd DIM (Declare) this stuff, but this message it to long already!

 

[SkipVought]

Hi,

"Can I use macros in Outlook to retrieve data from a message and it's Form, and put the data into excel?"

Yes. Use the CreateObject and GetObject methods to define an Excel Application object and open you Excel Workbook. VBA Help has some good examples.

"But I just want to know if I'm conceptualizing the use of Outlook Forms in the correct manner."

Hard to know.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}