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 |
Partager