Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub SendMail()
- 'Column B holds the email addresses
- 'Column G holds the file name
- 'Column H holds Yes or No to send
- 'Column F holds Contract Number
- Dim cell As Range
- Dim i As Long
- Dim getfile As String
- Dim rngOffset As Range
- Dim rng As Range: Set rng = Application.Range("$F$2")
- Dim iRowOffset As Integer: iRowOffset = a
- Dim iColumnOffset As Integer: iColumnOffset = 1
- Application.ScreenUpdating = False
- 'On Error GoTo EndofSub
- Sheets("Sheet1").Activate
- For Each cell In Columns("B").Cells.SpecialCells(2, 2)
- If cell.Text Like "?*@?*.?*" And _
- LCase(Cells(cell.Row, "h").Text) = "yes" Then
- On Error Resume Next
- With CreateObject("Outlook.Application").CreateItem(0)
- .To = cell.Text
- .Subject = "Invoice - COMPANYNAME"
- .Body = "Please see the attached file."
- a = 2
- Set rngOffset = rng.Offset(RowOffset:=iRowOffset, ColumnOffset:=iColumnOffset)
- getfile = Sheets("sheet1").Range(rngOffset).Value
- a = a + 1
- .Attachments.Add ("C:\Users\USERNAME\Documents\" & getfile & ".pdf")
- .Display
- End With
- 'On Error GoTo 0
- End If
- Next
- 'EndofSub:
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement