Importing: Outlook Data to Excel

[barkley]

I have a custom Outlook form which is sent companywide. The users fill in their holiday requests and get it authorised by their team leaders. I would ideally like to import the data from this form into an excel spreadsheet so that reports and analysis can be made.
Once the data is in Excel I will be fine manipulating it how I want, the problem is getting it there. Can anyone help/suggest a method of how I would go about storing the results from the form and then exporting all fields to a row on a spreadsheet?
Thanks.

[Jayu]

I have a similar query... Does anyone know how i can copy some lements of th message into an Excel file from Outlook and validate it???

[jsteinbeck]

well you can capture the body, sendername, senderemail etc... by setting them to variable...

however the tricky part is then getting them into excel... there is an answer...
took me awhile for my project

make sure you have the refences (in VBE tools>> references then find MS Excel)

 'Dim vfiles                  As FileDialog
Dim oExcelObjectRefApp      As Excel.Application 
Dim oExcelObjectRefBook     As Excel.Workbook 
Dim oExcelObjectRefSheet    As Excel.Worksheet 
Dim oExcelObjectRefBook1    As Workbook 
Dim oExcelObjectRefSheet1   As Worksheet 
Dim oExcelObjectRefRange    As Object 'Range
Dim oExcelObjectRef         As Object ' Variable to hold reference ' to Microsoft Excel.
Dim ExcelWasNotRunning      As Boolean 
Dim dlg                     As Office.FileDialog 
 
 
This will get excel If Not running, If it Is running may be an error?? 
Set oExcelObjectRef = VBA.GetObject("", "EXCEL.APPLICATION") 
 
 'ensure excel is running???
DetectExcel 

here is detect excel code

Sub DetectExcel() 
     ' Procedure dectects a running Excel and registers it.
    Const WM_USER = 1024 
    Dim hWnd As Long 
     ' If Excel is running this API call returns its handle.
    hWnd = FindWindow("XLMAIN", 0) 
    If hWnd = 0 Then ' 0 means Excel not running.
        Exit Sub 
    Else 
         ' Excel is running so use the SendMessage API
         ' function to enter it in the Running Object Table.
        SendMessage hWnd, WM_USER + 18, 0, 0 
    End If 
End Sub 

here i think you need this in your declarations

Declare necessary API routines: 
Declare Function FindWindow Lib "user32" Alias _ 
"FindWindowA" (ByVal lpClassName As String, _ 
ByVal lpWindowName As Long) As Long 
 
Declare Function SendMessage Lib "user32" Alias _ 
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _ 
ByVal wParam As Long, _ 
ByVal lParam As Long) As Long 

then open the file, fill it, save it, close it

[sengkangboy]

Hi ..

I dun really quite understand how to implement it. I need to have a simliar application.

Meaning, i want to copy all the emails send to me in outlook and export the Subject and Body to a Excel Workbook.

Can you give a little bit more guidance ?

Thanks..

[timharding]

I have searched and searched this kind of thing and I am also stuck.
I need to create a form on outlook and then when data has been inputted on the form on outlook...this then queries an excel sheet....


This is to be used for staff to request annual leave...and for it to be granted / declined if the space being requested is available or not.


I am willing to pay a little for a solution..or donate a set amount to their charity..

Can anyone help?..or offer a solutiong


Thanks

[jsteinbeck]

1st. you have to have excel reference library... In VBE go to tools, reference's, and find microsoft excel 10 object library... (may be on your computers 8,9,10).

when you open excel in the code you find your range or set it and then you set your subject, body to a variable and then set your range in excel to equal that variable...

With oMessage.Attachments 
    iAttchCount = .Count 
    Set oAttachment = oMessage.Attachments 
    If iAttchCount > 0 Then 
        For iCtr = 1 To iAttchCount 
            sEmailSenderEmail = oMessage.SenderEmailAddress 
            sEmailSenderName = oMessage.SenderName 
            dRecvdTime = oMessage.ReceivedTime 
            sEmailSubject = oMessage.Subject 
            sEmailAttachName = .Item(iCtr).FileName 
             'So I know where it's saved to
             'Wait this isn't saving it that is above so conBVCW_EVAL_FOLDER may need to change
            sEmailFileSavedTo = conBVCW_EVAL_FOLDER & sEmailAttachName 

then set up excel

Set oExcelObjectRefBook = oExcelObjectRef 
Set oExcelObjectRefSheet1 = oExcelObjectRefBook.Worksheets("Workbook Info") 

And then

oExcelObjectRefSheet1.Range("A11") = sEmailSubject 

Don't forget the code I posted early... if you need more help let me know... best thing is to get is far as you can... then you can email me the module and I can help from there...

John