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
| Sub envoiemail()
Dim CustomerNum As String 'permet de récupérer la donnée de la colonne A
Dim costumername As String 'permet de récupérer la donnée de la colonne B
Dim contactname As String 'permet de recuperer la donnée de la colonne C,E,G,I,K
Dim Email As String 'permet de récupérer la donnée de la colonne D,F,H,J,L
Dim consultant As String 'permet de récupérer la donnée de la colonne M
Dim rem_amount As Integer 'permet de récupérer la donnée de la colonne H 234 dans la fenetre "Unpaid_details
Dim Monsujet As String
Dim Mondestinataire As String
Dim Mondestinatairecc As String
Dim Moncontenu As String
Dim MaPieceJointe As String
Dim MaPieceJointe2 As String
Dim CP As Worksheet 'variable permettant de stocker la valeur du classeur "Companies"
Dim UD As Worksheet 'variable permettant de stocker la valeur du classeur "Unpaid_detail"
Dim LineNB, ColNB As Long 'variable permettant de récupérer la ligne sélectionnée
Dim i As Integer
Dim outlookObj As Object
Dim R As Excel.Range
Dim Num_invoice As Variant
Dim rng As Range
' copier_tab Macro
For Each R In Selection
If R.Value = "Company concerned" Then
costumername = Left(Range("F2"), 31)
Else
costumername = Left(R, 31)
End If
If WorksheetExists(costumername) = False Then '(verifie que la feuille correspondant au ticker d'une société n'existe pas afin de la créer
Sheets.Add , Sheets(Sheets.count)
Sheets(Sheets.count).Name = costumername 'ajoute une nouvelle feuille
End If
If WorksheetExists(costumername) = True Then
Application.DisplayAlerts = False
'Worksheets(costumername).Delete
Application.DisplayAlerts = True
End If
Sheets("Unpaid_details").Select
Range("f1").Select
Selection.AutoFilter
ActiveSheet.Range("$D$1:$K$229").AutoFilter Field:=3, Criteria1:= _
R.Value
Range("F1:K230").Select
Selection.Copy
Sheets(costumername).Select
ActiveSheet.Paste
'afficher les noms et mails des personnes à qui envoyer le mail.
Set UD = Worksheets(costumername)
UD.Range("H1") = "Contact Name"
UD.Range("I1") = "Email Address"
UD.Range("J1") = "Consultant"
UD.Range("H2") = Application.WorksheetFunction.VLookup(UD.Range("A2"), Sheets("Unpaid_details").Range("$f$1:$n$230"), 7, False) 'contactname
UD.Range("I2") = Application.WorksheetFunction.VLookup(UD.Range("A2"), Sheets("Unpaid_details").Range("$f$1:$n$230"), 8, False) 'consultant
UD.Range("j2") = Application.WorksheetFunction.VLookup(UD.Range("A2"), Sheets("Unpaid_details").Range("$f$1:$n$230"), 9, False) 'mail
Sheets("Unpaid_details").Select
Application.CutCopyMode = False
Selection.AutoFilter
UD.Select
concatene = UD.Range("e2").Value
For n = 3 To UD.Range("e65536").End(xlUp).Row - 1
concatene = concatene & "; " & Sheets(costumername).Range("e" & n).Value
Next
UD.Range("k1") = "Invoices N°"
UD.Range("k2").Value = concatene 'Invoices number
contactname = UD.Range("H2")
Email = UD.Range("I2")
consultant = UD.Range("J2")
' MsgBox = vbOK("Please don't forget to deselect the cells before you start again")
'
'Nous utilisons la feuille de chaque compagnie "
linemb = UD.Cells(Rows.count, "f").End(xlUp).Row
rem_amount = UD.Cells(linemb, 6)
Num_invoice = UD.Range("K2")
Monsujet = R.Value + " Invoices - " + Num_invoice
Mondestinataire = Email
Mondestinatairecc = "accounting@1254855655.eu;candy@515616516556.fr;Nunknjer@bubububvuerv.eu;" + consultant
Set rng = UD.Range("A1:f7")
Moncontenu = "Dear Mr " & contactname & "," & vbCrLf & "We would like to draw your attention to the fact that, errors and omissions excepted, our records show that you owe us the amount of " & rem_amount & ",00 € corresponding to the following invoice:" & vbCrLf & "Please find enclosed the above mentioned invoice for your reference." & vbCrLf & "We are kindly asking you to proceed to the due payment at your earliest convenience and we thank you in advance. We remain at your disposal for any further queries." & vbCrLf & "Thank you in advance and please do not hesitate to contact us at <a href="mailto:accounting@1254855655.eu">accounting@1254855655.eu</a> should you have any questions."
MaPieceJointe = "S:\PARIS-VAT\VATSystems_PRODUCTION\PROCESS_ACTIVITY\CLIENTS\PLMJ\Invoicing\2018\M2 - nothing to invoice\SVD - M2 2018 - nothing to invoice.msg"
Call envoi_email(Monsujet, Mondestinataire, Mondestinatairecc, Moncontenu, MaPieceJointe, MaPieceJointe2)
Next R
End Sub |
