Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 6 of 6

Thread: Importing: Outlook Data to Excel

  1. #1
    Join Date
    5th August 2003
    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?

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    27th May 2005

    Re: Importing: Outlook Data to Excel

    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???

    Excel Video Tutorials / Excel Dashboards Reports

  3. #3
    Join Date
    18th May 2005

    Re: Importing: Outlook Data to Excel

    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???
    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 
             ' 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

    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    13th May 2005

    Re: Importing: Outlook Data to Excel

    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 ?


    Excel Video Tutorials / Excel Dashboards Reports

  5. #5
    Join Date
    17th March 2005
    Sunderland , UK

    Re: Importing: Outlook Data to Excel

    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


    Excel Video Tutorials / Excel Dashboards Reports

  6. #6
    Join Date
    18th May 2005

    Re: Importing: Outlook Data to Excel

    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...


    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Importing Outlook Address Book Data
    By theycallmepyro in forum Excel and/or Email Help
    Replies: 3
    Last Post: February 27th, 2007, 18:05
  2. Importing data in excel
    By SomeoneHere in forum Excel General
    Replies: 2
    Last Post: July 4th, 2006, 05:28
  3. Importing : Web Data to Excel
    By karlh2001 in forum Excel General
    Replies: 3
    Last Post: April 30th, 2005, 00:27
  4. Importing data from access to excel
    By jimk in forum Excel and/or Access Help
    Replies: 1
    Last Post: December 7th, 2004, 18:09
  5. importing data from outlook with excel
    By Ash in forum Excel and/or Email Help
    Replies: 7
    Last Post: February 12th, 2003, 03:20


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts