Announcement

Collapse
No announcement yet.

Importing: Outlook Data to Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Importing: Outlook Data to Excel



    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.

  • #2
    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???

    Comment


    • #3
      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)


      Code:
      '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

      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

      Code:
      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

      Comment


      • #4
        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 ?

        Thanks..

        Comment


        • #5
          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


          Thanks

          Comment


          • #6


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

            Code:
            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

            Code:
            Set oExcelObjectRefBook = oExcelObjectRef
            Set oExcelObjectRefSheet1 = oExcelObjectRefBook.Worksheets("Workbook Info")
            And then

            Code:
            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

            Comment

            Working...
            X