Results 1 to 2 of 2

Thread: Poor Mans convert my AS400 to Word & e-mail

  1. #1
    Analyst GLS400's Avatar
    Join Date
    May 2006
    Location
    connecticut
    Posts
    1,902
    Rep Power
    5799

    Poor Mans convert my AS400 report to Word & e-mail

    Hi All:

    In my company we have a need to generate many small reports and e-mail them to our accounts. I have developed a word macro which will copy the text document generated in the AS400, add the company logo, and email it to the requestor as a word document attachment.

    The macro is stored in a word document (in my case as400rpt.doc), the logo is stored in a second word document (logo.doc) , the AS400 report is stored as a text file (qsysprt2.txt) and the e-mail address is stored in an excel document in cell A1 (ME1.XLS).


    Code:
      Private Sub Document_Open()
    ' open a text file and attach logo then email it to myself
        
    'First get the e-mail address located in cell A1 of ME1.XLS
        Dim SEND2 As String
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim EMAIL2 As String
    
    On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
       If Err.Number <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    
        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = xlApp.Workbooks.Open("H:\ME1.xls")
    ' me1.xls cell a1 will be my email address
        xlBook.Sheets(1).Range("A1:A1").Select
        EMAIL2 = Cells(1, 1).Value
    
        xlBook.Close
        xlApp.Quit
    
    
        Set xlBook = Nothing
        Set xlApp = Nothing
        
    'Second get the text file and copy it to the clip board
        Documents.Open FileName:="h:\QSYSPRT2.txt", ConfirmConversions:=False, _
            ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
            PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
            WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
        Selection.WholeStory
        Selection.Copy
    
     'third get the logo and paste the clip board data to it
        Documents.Open FileName:="h:\LOGO.doc", ConfirmConversions:=False, ReadOnly:= _
            False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _
            "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
            Format:=wdOpenFormatAuto, XMLTransform:=""
         
         'starting from the top of the page
         ' (when logo.doc is saved...leave your cursor at the very top of the page)
         '   go down "count" number of lines before you paste    Selection.MoveDown UNIT:=wdLine, Count:=6
        Documents("LOGO.DOC").Activate
        Selection.PasteAndFormat (wdPasteDefault)
          
          ' delete white space at bottom of the report
        Selection.Delete UNIT:=wdCharacter, Count:=56
        Application.Visible = False
          
          'document must be saved before it can be e-mailed
        ActiveDocument.SaveAs FileName:="h:\ISERIES REPORT.doc", FileFormat:= _
            wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
            True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
            False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
            SaveAsAOCELetter:=False
    
          'Re-Open the document just saved    Documents.Open FileName:="h:\ISERIES REPORT.doc", ConfirmConversions:=False, ReadOnly:= _
            False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _
            "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
            Format:=wdOpenFormatAuto, XMLTransform:=""
        Documents("h:\ISERIES REPORT.DOC").Activate
    
    
    'fourth start outlook attach document to new mail item and send it
        Dim bStarted As Boolean
        Dim oOutlookApp As Outlook.Application
        Dim oItem As Outlook.MailItem
    
        On Error Resume Next
    
        If Len(ActiveDocument.Path) = 0 Then
         MsgBox "Document needs to be saved first"
         Exit Sub
        End If
    
        Set oOutlookApp = GetObject(, "Outlook.Application")
        If Err <> 0 Then
          Set oOutlookApp = CreateObject("Outlook.Application")
         bStarted = True
        End If
    
        Set oItem = oOutlookApp.CreateItem(olMailItem)
    
        With oItem
         .To = EMAIL2
         .Subject = "ISERIES REPORT"
         .Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _
              DisplayName:="Document as attachment"
         .Send
        End With
    
        'end outlook
        If bStarted Then
         oOutlookApp.Quit
        End If
    
        Set oItem = Nothing
        Set oOutlookApp = Nothing
            
        'end word    Application.Visible = False
        Application.Quit
    End Sub
    On the 400 side you need to:
    1. cpysplf to qtemp
    2. ftp or cpytopcd to your document's drive (in the above example it is H: )
    3. strpco
    4. strpccmd 'h:as400rpt.doc'

    Hope someone can use this
    Last edited by GLS400; July 6th, 2006 at 11:16 AM. Reason: changed title

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12605

    Re: Poor Mans convert my AS400 to Word & e-mail

    Very nice - Thanks for posting


    Jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  4. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Age
    21
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Replies: 6
    Last Post: November 23rd, 2007, 04:41 AM
  2. Easy-Stored Proc on AS400- (New to AS400)
    By ermagarden in forum SQL
    Replies: 8
    Last Post: July 14th, 2006, 10:07 AM
  3. Question about JDBC?
    By Jackie in forum JAVA
    Replies: 21
    Last Post: June 29th, 2006, 12:47 PM
  4. Setting up the as400 as a POP mail server
    By jamief in forum Tips for the Iseries/AS400
    Replies: 0
    Last Post: August 17th, 2005, 12:59 PM
  5. Replies: 1
    Last Post: April 27th, 2005, 06:04 AM

Bookmarks

Posting Permissions

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