PDA

View Full Version : Solved: Dont email if no attachment exists



Klartigue
04-19-2012, 09:09 AM
Right now I have the below VBA code to send emails with attachments..Can I incorporate a code in here that says if the attachment ("G:\Katherine Lartigue\Allocations\Fidelity Trades.xls") does not exist, then do not create an email at all?

Thanks for the help!!

CatDaddy
04-19-2012, 11:17 AM
you forgot to post your code :)

CatDaddy
04-19-2012, 11:18 AM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=559

Klartigue
04-19-2012, 12:02 PM
Sub EmailJPMPrivate()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim Lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("jpmpb.opm.support4@jpmorgan.com")
oRecipient.Type = 1

.Subject = "Avalon Trade Allocations Attached"

.body = "Please see the attached trade allocations." & vbNewLine & vbNewLine & _
"Let me know if you need anything else." & vbNewLine & vbNewLine & _
"Thanks, " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com 713-238-2088"

.Attachments.Add ("G:\Katherine Lartigue\Allocations\JPM Private Trades.xls")
.Save
End With

End Sub

Bob Phillips
04-19-2012, 03:45 PM
Sub EmailJPMPrivate()
Const FILE_ATTACH As String = _
"G:\Katherine Lartigue\Allocations\JPM Private Trades.xls"
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Dim Lastrow As Long
Dim bodyText As String
Dim i As Long


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)

With oMailItem

Set oRecipient = .Recipients.Add("jpmpb.opm.support4@jpmorgan.com")
oRecipient.Type = 1

.Subject = "Avalon Trade Allocations Attached"

.body = "Please see the attached trade allocations." & vbNewLine & vbNewLine & _
"Let me know if you need anything else." & vbNewLine & vbNewLine & _
"Thanks, " & vbNewLine & vbNewLine & _
"Katherine Lartigue " & vbNewLine & _
"klartigue@avalonadvisors.com 713-238-2088"

If Dir(FILE_ATTACH, vbNormal) <> "" Then

.Attachments.Add FILE_ATTACH
.Save
End If
End With
End Sub

Klartigue
04-20-2012, 09:39 AM
This works great, thanks!!