Advertisement
Guest User

Untitled

a guest
Nov 9th, 2018
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.29 KB | None | 0 0
  1. Sub SendMail()
  2.  
  3. 'Column B holds the email addresses
  4. 'Column G holds the file name
  5. 'Column H holds Yes or No to send
  6. 'Column F holds Contract Number
  7.  
  8. Dim cell As Range
  9.  
  10. Dim i As Long
  11. Dim getfile As String
  12. Dim rngOffset As Range
  13. Dim rng As Range: Set rng = Application.Range("$F$2")
  14. Dim iRowOffset As Integer: iRowOffset = a
  15. Dim iColumnOffset As Integer: iColumnOffset = 1
  16.  
  17. Application.ScreenUpdating = False
  18.  
  19. 'On Error GoTo EndofSub
  20. Sheets("Sheet1").Activate
  21. For Each cell In Columns("B").Cells.SpecialCells(2, 2)
  22. If cell.Text Like "?*@?*.?*" And _
  23. LCase(Cells(cell.Row, "h").Text) = "yes" Then
  24.  
  25. On Error Resume Next
  26. With CreateObject("Outlook.Application").CreateItem(0)
  27. .To = cell.Text
  28. .Subject = "Invoice - COMPANYNAME"
  29. .Body = "Please see the attached file."
  30. a = 2
  31. Set rngOffset = rng.Offset(RowOffset:=iRowOffset, ColumnOffset:=iColumnOffset)
  32. getfile = Sheets("sheet1").Range(rngOffset).Value
  33. a = a + 1
  34. .Attachments.Add ("C:\Users\USERNAME\Documents\" & getfile & ".pdf")
  35. .Display
  36. End With
  37. 'On Error GoTo 0
  38. End If
  39. Next
  40. 'EndofSub:
  41. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement