Hi
I have a script which basically produces a message if the file doesn't exist in the folder. However, the code seems to carry on and onto this part of the code and flags the error message. How can this be tweaked pls?
.Attachments.Add "S:\Bloomberg\Bloomberg.xls"
Sub MailMacro()
'On Error GoTo Errorhandler
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim lastRow As Long
Dim Datestamp As String
Datestamp = Sheets("Input II").Range("E1")
Dim Path As String
Dim Filename As String
Dim Datestamp2 As String
Datestamp2 = Format(Sheets("Input II").Range("E1"), "yyyymmdd")
Path = "S:\Bloomberg\"
Filename = "Bloomberg"
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.DisplayAlerts = False
'ActiveWorkbook.Save
Set rng = Nothing
' Only send the visible cells in the selection.
'On Error Resume Next
Set rng = Range("A1:I" & lastRow).SpecialCells(xlCellTypeVisible)
'Set rng = Union(Range("A2:P" & lastRow), Range("T2:T" & lastRow)).SpecialCells(xlCellTypeVisible)
'On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Exit if file does not exist
If Len(Dir("S:\Bloomberg\Bloomberg.xls", vbDirectory)) = 0 Then _
MsgBox Datestamp & " File not found, proceed to attach manually", vbOKOnly
With OutMail
'.SentOnBehalfOfName = "Pricing@lgim.com"
.To = "Martin.Sew@lgim.com"
.CC = ""
.BCC = ""
.Subject = "Bloomberg file have been saved down as at " & Datestamp
.HTMLBODY = "<BODY style=font-size:13pt;font-family:Calibri<p>" & _
"<i><b>Bloomberg.xls</b></i>" & " have been saved down." & _
"<BR>" & _
"<BR><BR>" & _
RangetoHTML(rng)
.Attachments.Add "S:\Bloomberg\Bloomberg.xls"
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
End Sub
I have a script which basically produces a message if the file doesn't exist in the folder. However, the code seems to carry on and onto this part of the code and flags the error message. How can this be tweaked pls?
.Attachments.Add "S:\Bloomberg\Bloomberg.xls"
Sub MailMacro()
'On Error GoTo Errorhandler
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim lastRow As Long
Dim Datestamp As String
Datestamp = Sheets("Input II").Range("E1")
Dim Path As String
Dim Filename As String
Dim Datestamp2 As String
Datestamp2 = Format(Sheets("Input II").Range("E1"), "yyyymmdd")
Path = "S:\Bloomberg\"
Filename = "Bloomberg"
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.DisplayAlerts = False
'ActiveWorkbook.Save
Set rng = Nothing
' Only send the visible cells in the selection.
'On Error Resume Next
Set rng = Range("A1:I" & lastRow).SpecialCells(xlCellTypeVisible)
'Set rng = Union(Range("A2:P" & lastRow), Range("T2:T" & lastRow)).SpecialCells(xlCellTypeVisible)
'On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'Exit if file does not exist
If Len(Dir("S:\Bloomberg\Bloomberg.xls", vbDirectory)) = 0 Then _
MsgBox Datestamp & " File not found, proceed to attach manually", vbOKOnly
With OutMail
'.SentOnBehalfOfName = "Pricing@lgim.com"
.To = "Martin.Sew@lgim.com"
.CC = ""
.BCC = ""
.Subject = "Bloomberg file have been saved down as at " & Datestamp
.HTMLBODY = "<BODY style=font-size:13pt;font-family:Calibri<p>" & _
"<i><b>Bloomberg.xls</b></i>" & " have been saved down." & _
"<BR>" & _
"<BR><BR>" & _
RangetoHTML(rng)
.Attachments.Add "S:\Bloomberg\Bloomberg.xls"
.Display
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = True
End Sub
