Create multiple word Documents and email all attachments

[niteraven]

Hi all

I am having a problem getting this code to work. What I want to do is on a form (frmI) I want user to check a checkbox on each row (continuous form) and then click a command button and auto insert some information, then create a word document for that record, then save the word doc, then attach to email and display all the attached doc files in one email.

In the code below I cannot get the second word document to create - object variable not defined error.

Here is the code I have:

    Dim ADL As String
    Dim WdAppOpen As Word.Application
    Dim WdAppTemp As Word.Template
    Dim WdAppDoc As Word.Document
    Dim stDocName As String
    Dim StrSubEmail As String
    Dim Elook As New Outlook.Application
    Dim EnewLook As Outlook.MailItem
    Dim sfrmEmail As String
    Dim attachemail1 As String
    Dim myattach As Outlook.Attachment
    Dim myfilename As String
    Dim MyPath As String
    Dim adl1 As String
    Dim VarDate As String
    Dim ADL2 As String
    Dim varNcNum As String
    Dim VNCno As String
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim Vfolder As String
    Dim AttchNum As Integer
    
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryEmail", dbOpenDynaset)
    
    
    ADL = vbNewLine & vbNewLine
    adl1 = vbNewLine
    ADL2 = vbNewLine & vbNewLine & vbNewLine

    Vfolder = "BatchReturns"
    
       
    StrSubEmail = "Todays Returns"
    Set WdAppOpen = New Word.Application
    'new email item
    Set EnewLook = Elook.CreateItem(olMailItem)
    VarDate = Date
    varNcNum = Format(Forms!frmI!NonConNo, "0000")
    VNCno = "NC-1" & varNcNum
    
    'make folder for batch nc
    MkDir "C:\temp\" & Vfolder
    
    MyPath = "C:\temp\" & Vfolder
    myfilename = "Todays Returns -" & VNCno & ".doc"
    
    
    With rst
    If (rst.RecordCount) Then
    Attachnum = rst.RecordCount
    
    Do While Not rst.EOF
        
            rst.MoveFirst
            rst.Edit
            
            rst!NCAPSDate.Value = VarDate
            rst!NCSupRevDate.Value = VarDate
            rst!NCSupplier.Value = True
            rst!NCSUpplierName.Value = "IKUSI"
            rst!NCContactEmail = "someone@someplace.com"
            rst!NCContactName = "Ms Ruiz"
            rst!NCSuppType = "SUPPLIERS"
            rst!NCDisposition = "RETURN TO VENDOR"
            rst!NCRMANum = Me.PkgSlipNo
            rst!IEmail.Value = True
            rst!NCOnHold = "ON HOLD"
            rst!OHStartDate = VarDate
            
            rst.Update
            
            With WdAppOpen
            WdAppOpen.Visible = True
         
            Set WdAppDoc = .Documents.Open("\\Company\Compliance Systems\QA043.dot")
        
            varNcNum = Format(Forms!frmI!NonConNo, "0000")
            VNCno = "NC-1" & varNcNum
    
            'populate word doc fields
            WdAppOpen.ActiveDocument.Bookmarks("RMANUM").Select
            WdAppOpen.Selection.Text = VNCno
        
            WdAppOpen.ActiveDocument.Bookmarks("RMAISSUED").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCOriginator])
        
            WdAppOpen.ActiveDocument.Bookmarks("NCFailureType").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCFailureType])
        
            WdAppOpen.ActiveDocument.Bookmarks("NCSource").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCSource])
        
            WdAppOpen.ActiveDocument.Bookmarks("NCDescription").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCDescription])
        
            WdAppOpen.ActiveDocument.Bookmarks("modelno").Select
            WdAppOpen.Selection.Text = rst!NCItemNumber
            
            WdAppOpen.ActiveDocument.Bookmarks("NCItemDesc").Select
            WdAppOpen.Selection.Text = rst!NCItemName
        
            WdAppOpen.ActiveDocument.Bookmarks("NCAssignedto").Select
            WdAppOpen.Selection.Text = rst!NCSUpplierName
        
            WdAppOpen.ActiveDocument.Bookmarks("NCAPSDATE").Select
            WdAppOpen.Selection.Text = rst!NCAPSDate
        
            WdAppOpen.ActiveDocument.Bookmarks("NCSupplier").Select
            WdAppOpen.Selection.Text = rst!NCContactName
        
            WdAppOpen.ActiveDocument.Bookmarks("RMADATE").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCDate])
        
            WdAppOpen.ActiveDocument.Bookmarks("SerialNo").Select
            WdAppOpen.Selection.Text = Nz(([Forms]![frmI]![NCSerialNum]), "N/A")
        
            WdAppOpen.ActiveDocument.Bookmarks("NCQTY").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCQty])
        
            WdAppOpen.ActiveDocument.Bookmarks("NCMfgNo").Select
            WdAppOpen.Selection.Text = ([Forms]![frmI]![NCMfgNo])
        
            'WdAppOpen.ActiveDocument.Bookmarks("NCOriginalPo").Select
            'WdAppOpen.Selection.Text = ([Forms]![frmI]![NCOriginalPOnum])
        
            WdAppOpen.ActiveDocument.Bookmarks("NCRMANUM").Select
            WdAppOpen.Selection.Text = rst!NCRMANum
        
        
        
            If Me.NCSeverity = True Then
                WdAppOpen.ActiveDocument.Bookmarks("NCSeverity").Select
                WdAppOpen.Selection.Text = "SAFETY CRITICAL"
            End If
        
            WdAppOpen.ActiveDocument.SaveAs MyPath & myfilename
            WdAppOpen.ActiveDocument.Close
            WdAppOpen.Quit
            rst.MoveNext
            End With
        Loop
        
        rst.Close
        Set rst = Nothing
        WdAppOpen.ActiveDocument.Close
        
        Set WdAppOpen = Nothing
        End If
    End With
        
   'Create Batch Email
    With EnewLook
    
        .To = "Someone@someplace.com"
        .Subject = StrSubEmail
        .Body = "Good Morning" & ADL & _
                "If you could please arrange for pickup and let me know when to expect the driver i would appreciate it." & ADL2 & _
       		"Thank you!"
                
        .Attachments.Add (MyPath)
        
        .Display
    End With
    
    Response = acDataErrAdded

Any help or pointers would be greatly appreciated!
Thanks - Raven

 

[1DMF]

Looks like you are closing and quitting WdAppOpen, then trying to use it again in the rst loop?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[PHV]

Put WdAppOpen.Quit outside the Loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886