0 Replies Latest reply: May 5, 2010 6:51 PM by Ben756 RSS

    Read PDF form from email attachment

    Ben756

      Hope someone is able to help on this one...

       

      We are using a pdf form at work to gather some feedback and the user will be submitting the form via email...

      Using outlook 2003 I had a crack at writing a vba module to read the pdf form from the email attachment to update into an excel spreadsheet

       

      Is it possible to read pdf forms from the email attachment with outlook vba? Or does the attachment need to be saved to a directory first?

       

      I have in the past done a vba module to save the pdf form attachments to a directory and then using acrobat and it's in-built form data collection tool and exported as a csv, but this feedback form will be an ongoing thing that will be updated daily - and to minimise handling would like to run the outllook macro that reads straight from the email attachment instead of doing another 3 or so steps...

       

      Below is the snippet I am using - it's a mashup of different code I've found on the net

       

      Sub Feedback()
          On Error GoTo Feedback_err
          'Dim ns As NameSpace
          Dim objNS As NameSpace
          'Dim Inbox As MAPIFolder
          Dim objFolder As Outlook.MAPIFolder
          'Dim SubFolder As MAPIFolder
          Dim objExcel
          Dim oBook
          Dim oSheet
          Dim gApp As Acrobat.CAcroApp ' In Tools > References > Checked all Acrobat Libraries in VBA > Tools > References
          Dim pdDoc As Acrobat.CAcroPDDoc
          Dim jso As Object
          Dim Item As Object
          Dim Atmt As Attachment
          Dim filename As String
          'Dim i As Integer
          Dim myOrt As String ' Added 13/1/2010
          Set objApp = CreateObject("Outlook.Application")
          Set objNS = objApp.GetNamespace("MAPI")
          Set objFolder = objNS.PickFolder
      ' Check subfolder for messages and exit of none found
          If objFolder.Items.Count = 0 Then
          'If SubFolder.Items.Count = 0 Then
              MsgBox "There is no Feedback emails in this folder.", vbInformation, _
                     "Nothing Found"
              Exit Sub
          End If
      ' Check each message for attachments
          Set objExcel = CreateObject("Excel.Application")
          Set oBook = objExcel.Workbooks.Open("G:\Path\Filename.xls")
          Set oSheet = objExcel.Worksheets(1)
          oSheet.Range("A2").Select
          Set gApp = CreateObject("AcroExch.App") ' make acrobat session
          Set pdDoc = CreateObject("AcroExch.PDDoc")
          Set jso = pdDoc.GetJSObject ' set the Javascript object via this way we can fill in the PDF document fields
          For Each Item In objFolder.Items
              For Each Atmt In Item.Attachments
                  If Right(Atmt.filename, 3) = "pdf" Then
                      Do
                      If IsEmpty(objExcel.ActiveCell) = False Then
                          objExcel.ActiveCell.Offset(1, 0).Select
                      End If
                      Loop Until IsEmpty(objExcel.ActiveCell) = True
                          objExcel.ActiveCell.value = jso.getField("CurrentDocDate").value
                          objExcel.ActiveCell.Offset(0, 1).value = Item.Session.CurrentUser
                          objExcel.ActiveCell.Offset(0, 2).value = jso.getField("txtJobNo").value
                          objExcel.ActiveCell.Offset(0, 3).value = jso.getField("rbStatus").value
                          objExcel.ActiveCell.Offset(0, 4).value = jso.getField("rbFeedback").value
                          objExcel.ActiveCell.Offset(0, 5).value = jso.getField("txtComments").value
                  End If
              Next Atmt
          Next Item
          oBook.Save
          objExcel.Quit
      Feedback_exit:
          Set Atmt = Nothing
          Set Item = Nothing
          Set objNS = Nothing
          Set pdDoc = Nothing
          Set objExcel = Nothing
          Set jso = Nothing
          Exit Sub
      ' Handle Errors
      Feedback_err:
          MsgBox "An unexpected error has occurred." _
              & vbCrLf & "Please note and report the following information." _
              & vbCrLf & "Macro Name: Feedback" _
              & vbCrLf & "Error Number: " & Err.Number _
              & vbCrLf & "Error Description: " & Err.Description _
              , vbCritical, "Error!"
      Resume Feedback_exit
      End Sub

       

      I get a vba error 91 "Object Variable or With  block variable not set"

       

      Cheers

      Ben