1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
| Public Sub Pub_Word(type_Cpt As String, num_centre As String, commune As String)
vChoix_Publi = MsgBox("Voulez-vous créer le Publipostage ?", vbYesNo + vbQuestion, "Publipostage")
If vChoix_Publi = vbYes Then
Dim AppWord As Word.Application
Set AppWord = CreateObject("Word.application")
AppWord.Visible = True
Dim LettreType As New Word.Document
If (type_Cpt <> "ICE") Then
AppWord.Documents.Open Filename:="E:\Donnees\Commun\Test - Publipostage\CRXXX-XXXXX.doc"
If ((num_centre = 214 Or num_centre = 215) And (commune = 95)) Then
If DCount("*", "Tbl_CR_" & type_Cpt & "_" & num_centre, "[Num_Centre] = '" & num_centre & "' and [Code Postal] like '95*'") = 0 Then
MsgBox "Il n'y a pas d'enregistrements pour les " & type_Cpt & " du " & num_centre & ". Le publipostage ne sera pas fait"
AppWord.Documents.Close
AppWord.Application.Quit
Exit Sub
End If
Set LettreType = AppWord.Documents("CRXXX-XXXXX.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre & " where Num_Centre = '" & num_centre & "' and [Code Postal] like '95*'"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
ElseIf ((num_centre = 214 Or num_centre = 215) And (commune = 0)) Then
If DCount("*", "Tbl_CR_" & type_Cpt & "_" & num_centre, "[Num_Centre] = '" & num_centre & "' and [Code Postal] not like '95*'") = 0 Then
MsgBox "Il n'y a pas d'enregistrements pour les " & type_Cpt & " du " & num_centre & ". Le publipostage ne sera pas fait"
AppWord.Documents.Close
AppWord.Application.Quit
Exit Sub
End If
Set LettreType = AppWord.Documents("CRXXX-XXXXX.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre & " where Num_Centre = '" & num_centre & "' and [Code Postal] not like '95*'"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
Else
Set LettreType = AppWord.Documents("CRXXX-XXXXX.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
End If
LettreType.MailMerge.Execute
LettreType.Close (wdDoNotSaveChanges)
Else
AppWord.Documents.Open Filename:="E:\Donnees\Commun\Test - Publipostage\CRXXX-XXXXX-ICE.doc"
If ((num_centre = 214 Or num_centre = 215) And (commune = 95)) Then
If DCount("*", "Tbl_CR_" & type_Cpt & "_" & num_centre, "[Num_Centre] = '" & num_centre & "' and [Code Postal] like '95*'") = 0 Then
MsgBox "Il n'y a pas d'enregistrements pour les " & type_Cpt & " du " & num_centre & ". Le publipostage ne sera pas fait"
AppWord.Documents.Close
AppWord.Application.Quit
Exit Sub
End If
Set LettreType = AppWord.Documents("CRXXX-XXXXX-ICE.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre & " where Num_Centre = '" & num_centre & "' and [Code Postal] like '95*'"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
ElseIf ((num_centre = 214 Or num_centre = 215) And (commune = 0)) Then
If DCount("*", "Tbl_CR_" & type_Cpt & "_" & num_centre, "[Num_Centre] = '" & num_centre & "' and [Code Postal] not like '95*'") = 0 Then
MsgBox "Il n'y a pas d'enregistrements pour les " & type_Cpt & " du " & num_centre & ". Le publipostage ne sera pas fait"
AppWord.Documents.Close
AppWord.Application.Quit
End If
Set LettreType = AppWord.Documents("CRXXX-XXXXX-ICE.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre & " where Num_Centre = '" & num_centre & "' and [Code Postal] not like '95*'"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
Else
Set LettreType = AppWord.Documents("CRXXX-XXXXX-ICE.doc")
With LettreType.MailMerge
.OpenDataSource Name:= _
CurrentProject.path & "\SAR.mdb", ConfirmConversions:=False, _
ReadOnly:=False, linktosource:=True, addtorecentfiles:=False, _
SQLstatement:="SELECT * from Tbl_CR_" & type_Cpt & "_" & num_centre
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With
End If
LettreType.MailMerge.Execute
LettreType.Close (wdDoNotSaveChanges)
End If
Else
MsgBox "Le Publipostage n'a pas été effectué !", vbCritical, "Arrêt d'execution !"
End If
End Sub |
Partager