VB 45
Excel VBA MOM By mike on 28th March 2021 03:51:49 PM
  1. + Load email from template (body, distribution list and so)
  2. + Look for newest PDF file in set directory
  3. + Attach that PDF file
  4. + Set subject and add date
  5.  
  6. ============================================================
  7.  
  8. Sub MOM()
  9. ' You need to set a reference to the scripting object
  10. Dim objMail As Outlook.MailItem
  11. Dim fso As Scripting.FileSystemObject
  12. Dim strFile, sNew As String, dtNew As Date
  13. Set fso = New Scripting.FileSystemObject
  14.  
  15. strFile = "Z:\11 Admin\02 Operations\05 MoMs"
  16. Set fsoFldr = fso.GetFolder(strFile)
  17.      
  18. For Each fsoFile In fsoFldr.Files
  19. ' check the extension and age
  20.   If fsoFile.DateLastModified > dtNew And Right(fsoFile.Name, 4) = ".pdf" Then
  21.     sNew = fsoFile.Path
  22.     dtNew = fsoFile.DateLastModified
  23.     Debug.Print sNew & dtNew
  24.   End If
  25. Next fsoFile
  26.  
  27. 'Create e-mail item
  28. Set objMail = Application.CreateItemFromTemplate("C:\Users\Administrator\AppData\Roaming\Microsoft\Templates\MOM.oft")
  29. With objMail
  30. .BodyFormat = olFormatHTML
  31. .Subject = "Minutes of Meeting " & Format(Date, "dddd dd-mm-yyyy")
  32. .Attachments.Add sNew
  33. .Display
  34. End With
  35. End Sub

Hasta la pasta! is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.