Fill a letter with data from an Access form

Question

I have an Access software that has a form for numbering and indexing letters. When the operator opens a new form and press the new record, the database assign a number and date to this form, the operator enter the subject and addressee name , and the name of typist and the authority that sign the letter in text boxes in form. After entering necessary data, in the end of form I have a button that opens the Word application and the Access send the data (including Letter number, letter date and etc ) to Word. The operator or typist should enter the body of letter in several lines and save the letter. After completing the letter and saving it , by pressing another button the Word document will attach to this form. The letter have printed header and footer. I want that the letter date, Number, subject and Addressee be in the top of letter, after the printed header (about five centimeter lower that top of letter) and I want that the Manager name and the operator name and signature be in the end of letter (about 5 centimeters after body) over the printed footer. I can create the top of letter approximately but I don't know how can I put the name and signature of Manager and typist after the body (the part of letter that operator himself type in Word freely) in the end of letter. My code in Access VBA is as follow:

Function FnWriteToWordDoc()

    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objPara As Word.Paragraph
    Dim objSelection As Word.Section
    Dim strFileName As String
    Dim strLetterInfo As String
    Dim lngDate As Long
    Dim lngDay As Long
    Dim lngMonth As Long
    Dim lngYear As Long

   

    Set objWord = New Word.Application
    strFileName = Me.txtIndicatorNumber

    objWord.Visible = True
    objWord.windowState = wdWindowStateMaximize
    Set objDoc = objWord.Documents.add
    'Set the page setup properties of the document
    With objDoc.PageSetup
        .Orientation = wdOrientPortrait
        .PaperSize = wdPaperA4
        .TopMargin = objWord.CentimetersToPoints(4)
        .BottomMargin = objWord.CentimetersToPoints(5)
    End With


    'set the selection properties

    With objWord.Selection
        .Font.Name = "B Nazanin"
        .Font.Size = 14
        .ParagraphFormat.SpaceAfter = 5
    End With

    lngDate = Me.txtIndicationDate
    lngDay = Day(lngDate)
    lngMonth = Month(lngDate)
    lngYear = Year(lngDate)
    strLetterInfo = "Letter Date: " & lngDay & "/" & lngMonth & "/" & lngYear & Chr(11) & "Letter Number:  " & Me.txtIndicatorNumber
    objWord.Selection.TypeText strLetterInfo
    objDoc.Paragraphs.add
    objDoc.Paragraphs(1).indent
    objDoc.Paragraphs(2).Alignment = wdAlignParagraphRight
    Set objPara = objDoc.Paragraphs(2)
    objPara.Range.Text = Me.txtAddressee & Chr(11) & "Subject:" & " " & Me.txtSubject
    With objWord.Selection
        .Font.Name = "B Nazanin"
        .Font.Size = 16
    End With

    objDoc.SaveAs "Z:\Virsa\temp\" & strFileName


End Function

---

Karim Vaziri Regards,

Answer 1

I have an Access software that has a form for numbering and indexing letters. When the operator opens a new form and press the new record, the database assign a number and date to this form, the operator enter the subject and addressee name , and the name of typist and the authority that sign the letter in text boxes in form. After entering necessary data, in the end of form I have a button that opens the Word application and the Access send the data (including Letter number, letter date and etc ) to Word. The operator or typist should enter the body of letter in several lines and save the letter. After completing the letter and saving it , by pressing another button the Word document will attach to this form. The letter have printed header and footer. I want that the letter date, Number, subject and Addressee be in the top of letter, after the printed header (about five centimeter lower that top of letter) and I want that the Manager name and the operator name and signature be in the end of letter (about 5 centimeters after body) over the printed footer. I can create the top of letter approximately but I don't know how can I put the name and signature of Manager and typist after the body (the part of letter that operator himself type in Word freely) in the end of letter. My code in Access VBA is as follow:

Function FnWriteToWordDoc()

    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objPara As Word.Paragraph
    Dim objSelection As Word.Section
    Dim strFileName As String
    Dim strLetterInfo As String
    Dim lngDate As Long
    Dim lngDay As Long
    Dim lngMonth As Long
    Dim lngYear As Long

   

    Set objWord = New Word.Application
    strFileName = Me.txtIndicatorNumber

    objWord.Visible = True
    objWord.windowState = wdWindowStateMaximize
    Set objDoc = objWord.Documents.add
    'Set the page setup properties of the document
    With objDoc.PageSetup
        .Orientation = wdOrientPortrait
        .PaperSize = wdPaperA4
        .TopMargin = objWord.CentimetersToPoints(4)
        .BottomMargin = objWord.CentimetersToPoints(5)
    End With


    'set the selection properties

    With objWord.Selection
        .Font.Name = "B Nazanin"
        .Font.Size = 14
        .ParagraphFormat.SpaceAfter = 5
    End With

    lngDate = Me.txtIndicationDate
    lngDay = Day(lngDate)
    lngMonth = Month(lngDate)
    lngYear = Year(lngDate)
    strLetterInfo = "Letter Date: " & lngDay & "/" & lngMonth & "/" & lngYear & Chr(11) & "Letter Number:  " & Me.txtIndicatorNumber
    objWord.Selection.TypeText strLetterInfo
    objDoc.Paragraphs.add
    objDoc.Paragraphs(1).indent
    objDoc.Paragraphs(2).Alignment = wdAlignParagraphRight
    Set objPara = objDoc.Paragraphs(2)
    objPara.Range.Text = Me.txtAddressee & Chr(11) & "Subject:" & " " & Me.txtSubject
    With objWord.Selection
        .Font.Name = "B Nazanin"
        .Font.Size = 16
    End With

    objDoc.SaveAs "Z:\Virsa\temp\" & strFileName


End Function

                               

---

Karim Vaziri Regards,

Answer 2

Hi Karim, Sorry I didn't read your entire post but just wondering if you tried doing a mail merge instead. Hope it helps...

Answer 3

Hi,

No. I want to send some information to word, I want to automate word. Our company send a lot of letters and applications to authorities and other organizations. The secretary should type and number the letter, I want to automate this process of numbering and save the word file to Access database.

---

Karim Vaziri Regards,

Answer 4

Hi, I thought this is exactly what a mail merge will do for you.

Answer 5

Could you tell me how can I do that? Or refer me to an article.

Best regards

---

Karim Vaziri Regards,

Answer 6

Hi Karim, Look here for the simple Word Merge demo. http://kallal.ca/msaccess/msaccess.html

Answer 7

You could extend your existing code to output the name and signature of Manager and typist, just as you're already doing, before the typist actually types anything, then move the insertion point to where your image says 'the operator can type here' and save the document. Granted, the document will be saved before the typist inputs anything, but they'll be prompted by Word to save again before closing so that shouldn't be a problem.

---

Cheers
Paul Edstein
[MS MVP - Word]

Answer 8

Dear Dbguy,

You are right. It is exactly what I need.

Thank you for your help.

---

Karim Vaziri Regards,

Answer 9

Hi Karim,

Glad to hear you got it to work. Good luck with your project.