Hi, been stuck on this one issue for some time.
I have below code that attaches files based on B5 cells down with "REVISED" on it. It pulls all the files needed in one email except when "REVISED" file is not there.
Where in the code do I need to place IF and below in red to pull file without REVISED?
Thanks
Special2 = ActiveWorkbook.Sheets("collateral").Cells(i, "B")
BRFile2 = Dir(BRFilePath & CFull & " " & "*" & Special2 & "*")
If Len(BRFile) = 0 Then
.Attachments.Add BRFilePath & BRFile2
BRFile2 = Dir
Function BRemailv2()
Dim OutApp As Object
Dim OutMail As Object
Dim Signature As String
Dim CDt As String
Dim CFull As String
Dim BRFilePath As String
Dim BRFile As String
Dim Special As String
Dim Special2 As String
Dim Rng As Range
i = 5
CDt = ThisWorkbook.Sheets("Emails").Range("B1").Text
CFull = ThisWorkbook.Sheets("Emails").Range("D1").Text
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Display
Signature = OutMail.Body
.To = "abcd@gmail.com"
.CC = "efgh@gmail.com"
.BCC = ""
.Subject = "This is a test" & " " & CDt
.Body = "Good afternoon, this is test" & Signature
.Attachments.Add ActiveWorkbook.FullName
Set Rng = ActiveWorkbook.Sheets("summary").Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each cell In Rng
Special = ActiveWorkbook.Sheets("summary").Cells(i, "B") & " REVISED"
BRFilePath = "S:\sosoosososos"
BRFile = Dir(BRFilePath & CFull & " " & "*" & Special & "*")
Do While Len(BRFile) > 0
.Attachments.Add BRFilePath & BRFile
BRFile = Dir
i = i + 1
Loop
Next cell
.Display
.Save
.Close olPromtForSave
Set OutMail = Nothing
Set OutApp = Nothing
End With
End Function
I have below code that attaches files based on B5 cells down with "REVISED" on it. It pulls all the files needed in one email except when "REVISED" file is not there.
Where in the code do I need to place IF and below in red to pull file without REVISED?
Thanks
Special2 = ActiveWorkbook.Sheets("collateral").Cells(i, "B")
BRFile2 = Dir(BRFilePath & CFull & " " & "*" & Special2 & "*")
If Len(BRFile) = 0 Then
.Attachments.Add BRFilePath & BRFile2
BRFile2 = Dir
Function BRemailv2()
Dim OutApp As Object
Dim OutMail As Object
Dim Signature As String
Dim CDt As String
Dim CFull As String
Dim BRFilePath As String
Dim BRFile As String
Dim Special As String
Dim Special2 As String
Dim Rng As Range
i = 5
CDt = ThisWorkbook.Sheets("Emails").Range("B1").Text
CFull = ThisWorkbook.Sheets("Emails").Range("D1").Text
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Display
Signature = OutMail.Body
.To = "abcd@gmail.com"
.CC = "efgh@gmail.com"
.BCC = ""
.Subject = "This is a test" & " " & CDt
.Body = "Good afternoon, this is test" & Signature
.Attachments.Add ActiveWorkbook.FullName
Set Rng = ActiveWorkbook.Sheets("summary").Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each cell In Rng
Special = ActiveWorkbook.Sheets("summary").Cells(i, "B") & " REVISED"
BRFilePath = "S:\sosoosososos"
BRFile = Dir(BRFilePath & CFull & " " & "*" & Special & "*")
Do While Len(BRFile) > 0
.Attachments.Add BRFilePath & BRFile
BRFile = Dir
i = i + 1
Loop
Next cell
.Display
.Save
.Close olPromtForSave
Set OutMail = Nothing
Set OutApp = Nothing
End With
End Function