Skip navigation
Ben756
Currently Being Moderated

Read PDF form from email attachment

May 5, 2010 6:51 PM

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

 

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points