VBA Code For Outlook Attachment

[arnold]

I am trying to program Access to automatically attach files to an
email if there is a file at a specified path. The following code
works perfectly until the second If Not Missing statement. How can I
make Access skip the second attachment if there is no such file
there? I would like for it to attach both assuming there are two
files. I am sure this is an easy fix, but I cannot get past it. Any
help would be greatly appreciated!

Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookAttach2 As Outlook.Attachment
Dim strFolderPath As String
Dim superName As String

AttachmentPath = "I:\Applicants\Applications\" & "SDOC" &
Me.AppFileName & ".pdf"
Attachment2Path = "I:\Applicants\Interview\" & "SDOC" & Me.AppFileName
& ".pdf"

Me.SentSupervisor = Date

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(Me.Combo197)
objOutlookRecip.Type = olTo
.Subject = Me.FirstName & " " & Me.LastName
.Body = "This is a potential new hire. Take a look at the
application and let me know if you have any questions. If there are
two files attached, one i " & Me.Notes & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
If Not IsMissing(AttachmentPath2) Then
Set objOutlookAttach2 = .Attachments.Add(Attachment2Path)

 

[vbasean]

I am trying to program Access to automatically attach files to an
email if there is a file at a specified path.  The following code
works perfectly until the second If Not Missing statement.  How can I
make Access skip the second attachment if there is no such file
there?  I would like for it to attach both assuming there are two
files.  I am sure this is an easy fix, but I cannot get past it.  Any
help would be greatly appreciated!

Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookAttach2 As Outlook.Attachment
Dim strFolderPath As String
Dim superName As String

AttachmentPath = "I:\Applicants\Applications\" & "SDOC" &
Me.AppFileName & ".pdf"
Attachment2Path = "I:\Applicants\Interview\" & "SDOC" & Me.AppFileName
& ".pdf"

Me.SentSupervisor = Date

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
      Set objOutlookRecip = .Recipients.Add(Me.Combo197)
         objOutlookRecip.Type = olTo
     .Subject = Me.FirstName & " " & Me.LastName
      .Body = "This is a potential new hire.  Take a look at the
application and let me know if you have any questions.  If there are
two files attached, one i  " & Me.Notes & vbCrLf & vbCrLf
      .Importance = olImportanceHigh  'High importance
      ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        If Not IsMissing(AttachmentPath2) Then
            Set objOutlookAttach2 = .Attachments.Add(Attachment2Path)

if variable attachmentpath and variable attachment2path are variants
then

attachmentpath = iif(me.appfilename<>"", "I:\Applicants\Applications\"
& "SDOC" & Me.AppFileName & ".pdf", null)
attachment2path = iif(me.appfilename<>"", "I:\Applicants\Interview\" &
"SDOC" & Me.AppFileName & ".pdf" , null)

instead of:
if not ismissing

use:
if not isnull(attachmentpath) then

if variabel attachmentpath and variable attachment2path are strings
then
attachmentpath = iif(me.appfilename<>"", "I:\Applicants\Applications\"
& "SDOC" & Me.AppFileName & ".pdf", "")
attachment2path = iif(me.appfilename<>"", "I:\Applicants\Interview\" &
"SDOC" & Me.AppFileName & ".pdf" , "")

instead of:
if not ismissing

use:
if attachmentpath<>"" then
etc

 

[vbasean]

another thing

is there a reason you have a teared if statement?

if this then
do this
if this then
do this
End if
end if

you realize that the second if statement never fires if the first if
statement criteria is not met.

 

[arnold]

another thing

is there a reason you have a teared if statement?

if this then
do this
if this then
do this
End if
end if

you realize that the second if statement never fires if the first if
statement criteria is not met.

Thanks for the help. I still cannot get it to avoid the second path,
but I am not going to worry about that. I can work around it. The
nested If was the beginning of trying to fix it. I should have left
it off. Thanks!

 

[arnold]

I made some changes and came up with the following. The person
helping me with this project put this together with me. We are still
trying to make it send the email even when there is no "Attachment2"
or "Attachment3." Right now we just come up with an error that says
that file cannot be found. We would like to avoid this error message
and just send the email with as many attachments as there are in the
folder. We tried to come up with other ways to do this, but have
settled on really needing this way to work. Any suggestions?


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim strFolderPath As String
Dim superName As String
Dim objOutlookAttach1 As String
Dim objOutlookAttach2 As String
Dim objOutlookAttach3 As String
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem

If [ContactNotesSubform].Form![Combo18] = "Supervisor Review" Then

Me.SentSupervisor = Date

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(Me.Combo197)
objOutlookRecip.Type = olTo
.Subject = Me.FirstName & " " & Me.LastName
.Body = "This is a potential new hire. Take a look at the
application and let me know if you have any questions. If there are
two files attached, one is the app and the other is the interview
notes. " & Me.Notes & vbCrLf & vbCrLf
.Importance = olImportanceHigh

objOutlookAttach1 = "I:\Applicants\Applications\" & "SDOC" &
Me.AppFileName & ".pdf"
objOutlookAttach2 = "I:\Applicants\Applications\" & "SDOC" &
Me.AppFileName & "i.pdf"
objOutlookAttach3 = "I:\Applicants\Applications\" & "SDOC" &
Me.AppFileName & "t.pdf"

If Len(objOutlookAttach1) <> 0 Then
objOutlookMsg.Attachments.Add (objOutlookAttach1)
If Len(objOutlookAttach2) <> 0 Then
objOutlookMsg.Attachments.Add (objOutlookAttach2)
If Len(objOutlookAttach3) <> 0 Then
objOutlookMsg.Attachments.Add (objOutlookAttach3)
End If
End If
End If