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
125
126
127
128
| Sub mail1lotus()
'''''''''''' création copy
''''''''''''''''''''''''''''''''''''''''''''''
Workbooks("Envoi Mail par Lotus.xlsx").Activate
Sheets("feuil1").Copy
Sheets("feuil1").Select
ChDir "D:\Users\ggnamey\Documents\affaire_os\affaire\courrier"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"D:\Users\ggnamey\Documents\affaire_os\affaire\courrier\courrier1" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveWorkbook.Close False
Range("A1").Select
date2 = Workbooks("Projet_GRDF_TEST").Sheets("Accueil").Range("C3").Value
donnees2 = "recap_affaire" & date2 & ".xls"
num_semaine2 = Workbooks(donnees2).Sheets(1).Name
'envoie un mail si condition remplie
Workbooks(donnees2).Activate ' nom de fichier a remplir
Sheets(num_semaine2).Range("a1").Select
Dim Sujet As String, Corps As String
Dim CC As String, c As Range, PJ As String
Sujet = "Sujet du message"
Corps = "Corps du message"
CC = ""
' Desti = "****@*****.***"
PJ = Nom_Fichier1
SendNotesMail Sujet, Corps, Desti, CC, PJ
Nom_Fichier = "D:\Users\ggnamey\Documents\affaire_os\affaire\courrier\courrier1.pdf"
End Sub
Public Sub SendNotesMail(Sujet, Corps, Desti, CC, PJ)
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim Corps_Msg As String ' text du courriel
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim cell As Range
Dim tableauDestinataires() As String
Dim nbDestinataires As Integer
Dim Nom_Fichier As String
nbDestinataires = 0
Desti = tableauDestinataires
Application.ScreenUpdating = False
For Each cell In Columns("ES").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "FK").Value) <> "" Then
ReDim Preserve tableauDestinataires(nbDestinataires)
tableauDestinataires(nbDestinataires) = cell.Value
nbDestinataires = nbDestinataires + 1
End If
Next cell
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your Password
' Session.Initialize ("Password")
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string or using above password you can use othermailboxes.
''MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) -InStr(1, UserName, " "))) & ".nsf"
''UserName = Session.UserName
''SearchString = Recipient
''SearchChar = "@"
''MyPos = InStr(1, SearchString, SearchChar, vbTextCompare)
''Destinataire = Left(SearchString, MyPos - 1)
'Open the mail database in notes
Set Maildb = Session.GetDataBase("", "")
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OpenMail
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.Sendto = Desti
MailDoc.Subject = Sujet
MailDoc.copyto = CC
'MailDoc.Body = Corps
Set MailCorps = MailDoc.CreateRichTextItem("Body")
With MailCorps
.ADDNEWLINE 1
.APPENDTEXT Corps
.ADDNEWLINE 2
End With
MailDoc.SaveMessageOnSend = True
'Set up the embedded object and attachment and attach it
Set AttachME = MailDoc.CreateRichTextItem("Attachment")
'AttachME.AppendText "toto"
Call AttachME.EmbedObject(1454, "", PJ, "Attachment")
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.Send 0
Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Application.ScreenUpdating = True
End Sub |
