Attachment.Add Outlook If missing Add another

shoyu

New Member
Joined
May 26, 2016
Messages
10
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,217,371
Messages
6,136,173
Members
449,996
Latest member
duraichandra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top