Email a single attachment from folder of files each to a different person

by

Here is quick example, assuming col A = Email, Col B = Subject & Col C = Path

enter image description here

Option Explicit
Public Sub Example()
   Dim olApp As Object
   Dim olMail As Object
   Dim olRecip As Object
   Dim olAtmt As Object
   Dim iRow As Long
   Dim Recip As String
   Dim Subject As String
   Dim Atmt As String

   iRow = 2

   Set olApp = CreateObject("Outlook.Application")

   Dim Sht As Worksheet
   Set Sht = ThisWorkbook.Worksheets("Sheet1")

   Do Until IsEmpty(Sht.Cells(iRow, 1))

      Recip = Sht.Cells(iRow, 1).Value
      Subject = Sht.Cells(iRow, 2).Value
      Atmt = Sht.Cells(iRow, 3).Value ' Attachment Path

      Set olMail = olApp.CreateItem(0)

      With olMail
         Set olRecip = .Recipients.Add(Recip)
        .Subject = Subject
        .Body = "Hi "
        .Display
         Set olAtmt = .Attachments.Add(Atmt)
         olRecip.Resolve
      End With

      iRow = iRow + 1

   Loop

   Set olApp = Nothing
End Sub

More Related Contents:

  1. How to copy Outlook mail message into excel using VBA or Macros
  2. Copy Excel range as Picture to Outlook
  3. Paste Excel range in Outlook
  4. Update excel sheet based on outlook mail [closed]
  5. Sending mail using Outlook where the Send method fails
  6. How to send mail when the .Send does not work?
  7. How to add an attachment to an email using VBA in Excel
  8. How to send email to multiple recipients with addresses stored in Excel?
  9. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  10. Finding a workbook in one of multiple Excel instances
  11. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  12. Change HTML email body font type and size in VBA
  13. How to Send Email When Computer is Locked?
  14. Save excel file multiple times with file names in another workbook.
  15. Read/Parse text file line by line in VBA
  16. Excel VBA: Answer gets “stuck”
  17. filter out multiple criteria using excel vba
  18. Excel VBA autofilter all but three
  19. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  20. Excel VBA code to copy a specific string to clipboard
  21. Excel VBA – Range.Copy transpose paste
  22. How do you get a Range to return its Name?
  23. Refreshing all the pivot tables in my excel workbook with a macro
  24. Countif With Multiple OR Criteria
  25. Vlookup to copy color of a cell
  26. How to delete rows in Excel based on criteria using VBA?
  27. Macro to export MS Word tables to Excel sheets
  28. VBA: Modify chart data range
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment