Outlook attachment & Message box via Excel VBA

[Sushiboy]

Good evening,

I have the following VBA code which opens up from Excel, an outlook outgoing mail and attaches a specific excel attachment located in the S drive.

How can I incorporate a message box (in the below code) to notify if the attachment cannot be found in the location?

Many thanks
Martin




Sub GRD()
​

​

​

​

On Error GoTo Errorhandler​

​

Dim OutApp, OutMail As Object​

​

Dim DateStamp As String​

​

Dim DateStamp2 As String​

​

​

​

DateStamp = Range("C1")​

​

DateStamp2 = Format(Range("C1"), "ddmmyyyy")​

​

​

​

Set OutApp = CreateObject("Outlook.Application")​

​

Set OutMail = OutApp.CreateItem(0)
​

​

​

​

With OutMail​

​

​

​

.To = "Sushibo@hotmai.com"​

​

.Cc = ""​

​

.Subject = "GRD's as " & DateStamp​

​

.HTMLBody = ""​

​

.ATTACHMENTS.Add "S:\Operations\Archive" & DateStamp2 & " GDR.xlsx"
​

​

Errorhandler:
​

​

.Display
​

​

​

​

End With​

​

​

​

Set OutMail = Nothing​

​

Set OutApp = Nothing​

​

​

​

End Sub​

 

[lrobbo314]

Re: Outlook attachement & Message box via Excel VBA

Something like this.

Sub GRD()


On Error GoTo Errorhandler
Dim OutApp, OutMail As Object
Dim DateStamp As String
Dim DateStamp2 As String
DateStamp = Range("C1")
DateStamp2 = Format(Range("C1"), "ddmmyyyy")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


'New Code ****
Dim Path As String
Path = "S:\Operations\Archive" & DateStamp2 & " GDR.xlsx"




With OutMail
.To = "Sushibo@hotmai.com"
.Cc = ""
.Subject = "GRD's as " & DateStamp
.HTMLBody = ""
'New Code ***
If Path <> "" Then
    .ATTACHMENTS.Add Path
Else
    MsgBox "File not found", vbOKOnly, "Error"
End If


Errorhandler:
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[welshgasman]

Re: Outlook attachement & Message box via Excel VBA

Surely that just does the same thing, if the file is not there.?

Test for the file like this https://stackoverflow.com/questions/16351249/vba-check-if-file-exists