Access Mail Merge to different documents based on specific criteria

I'm trying to figure out if there is a way to take a code that I have for merging to a document and set up an if...then that will merge to a different document based on a specific text in a field in a table.

My code that works.

Sub SendConfirmation_Click(CourseNumber As Index)
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConfirmationMailMerge"

        Dim LevelIConf As String
        Dim OpenWord As Object

        'Path to word document
        LevelIConf = "G:\POSTPROFESSIONAL\NAIOMT\Classes\PTH536 Level I\LevelIConf.doc"

        'Create instance of Word
        Set OpenWord = CreateObject("Word.Application")
        OpenWord.Visible = True

        'Open the document
        OpenWord.Documents.Open FileName:=LevelIConf

DoCmd.SetWarnings True
End Sub

I have several Courses that I send out confirmation letters for and each letter is different based on the course. I would like to be able to push a button on the form and have correct document come up based on the course number.

Any help is appreciated. I am a self taught co开发者_运维技巧der and still have lots to learn.

Thanks,

Please note I provide generic solution that may need to be adapted depending on your actual requirements/setup.

Option 1: Your approach (using Word's mail-merge)

You need the following setup:

• Two related tables:

• Query that returns path to the Conf Letter based on Course ID:

• Function that calls the query from previous step:

  Function GetConfLetterPathByCourseID(CourseID As Integer) As String
  
      GetConfLetterPathByCourseID = ""
  
      Dim qdf As QueryDef
      Dim rs As Recordset
  
      Set qdf = CurrentDb.QueryDefs("GetConfLetterPathByCourseId")
      qdf.Parameters("CourseID_par") = CourseID
      Set rs = qdf.OpenRecordset
  
      If rs.RecordCount > 0 Then
          GetConfLetterPathByCourseID = rs("ConfLetterPath")
      End If
  
  End Function
  

• Form with the Send button. Something like this:

• And, finally, the Sub for the Send Button:

  Sub ConfLetterButton_Click()
  
      DoCmd.SetWarnings False
  
      Dim LevelIConf As String
      Dim OpenWord As Object
  
      'Path to word document
      LevelIConf = GetConfLetterPathByCourseID(Me.CourseID)
  
      'Create instance of Word
      Set OpenWord = CreateObject("Word.Application")
      OpenWord.Visible = True
  
      'Open the document
      OpenWord.Documents.Open FileName:=LevelIConf
  
      DoCmd.SetWarnings True
  
  End Sub
  

Please note, I altered slightly your code (e.g. removed Index type, removed Docmd.OpenQuery)

Option 2: Compose email in VBA code and attach the Conf Letter doc file using the Query/Function from option 1. I think this link from Microsoft can provide some details. I did implement similar solution in the past. Worked pretty well.