Open dialog box to add attachment for Outlook email, when attachment not found

Question

I would like to attach a file, when sending Outlook email, with Excel VBA code.

Sometimes the attachment doesn't meet the path criteria in the code.

In this case, I get:

Cannot find this file, verify the path and file name are correct

 Sub Confirmationemail()

  MsgBox ("The confirmation email will be sent now")

 Dim OutlookApp As Outlook.Application
 Dim OutlookMail As Outlook.MailItem
 Dim fs As Worksheet, bs As Worksheet
 Dim Filename As String, Name As String, Name2 As String, Name3 As String, Reason As String
 Dim Cost As String, PathFileName As String, PDFfile As String, FilePDF As String, NameA As String, 
 NameB As String
 Dim linecount2 As Long

 Set fs = Sheets("Frontsheet")
 Set bs = Sheets("BoM")
 linecount2 = 1

 Cost = Round(bs.Range("E79")(linecount2, 1), 2)

  Name = fs.Range("D10")
  Name2 = fs.Range("D18")
  Name3 = fs.Range("D38")
  NameA = fs.Range("D16")
  NameB = fs.Range("AA2")

  If fs.Range("D38").Value = 3 Then
  Reason = fs.Range("K8")
  ElseIf fs.Range("D38").Value = 4 Then
  Reason = fs.Range("P4")
  Else
  Reason = fs.Range("K4")
  End If

  Filename = Name & "_" & Name2

  FilePDF = "DPP_" & Name & "_" & NameA & "_" & NameB & "_V" & Name3 & ".0.xlsm"

  PDFfile = ThisWorkbook.Path & "/" & FilePDF & ".pdf"

  Set OutlookApp = New Outlook.Application
  Set OutlookMail = OutlookApp.CreateItem(olMailItem)

  With OutlookMail
 .BodyFormat = olFormatHTML
 .Display
 .HTMLBody = "The job is done"

 .To = "xxx@xx.co.uk"  
 .CC = "yyy@yyy.co.uk; zzzz@zzz.co.uk;"
 .Subject = Filename & "- Audit"
 If Not Filename Like "?_V?" Then
 MsgBox ("The attachment wasn't found. Add it manually.")

 Else
 .Attachments.Add PDFfile
 End If
 End With

 End Sub

The name of my file is set in the FilePDF variable. Once this file name cannot be found (e.g. because of lack one symbol) I need some IF statement covering this situation.

I don't want to display the error. I would like to open the Outlook dialog for attaching a file.

I looked at When adding a file as attachment in Outlook using VBA how can I make the open folder dialog window the active window?

I found something like this:

If FD.Show = True Then
    For Each vrtSelectedItem In FD.SelectedItems
        .Attachments.Add vrtSelectedItem
    Next
End If

Next I implemented something similar in my code:

If Not Filename Like "?_V?" Then
    MsgBox ("The attachment wasn't found. Add it manually.")
Else
    .Attachments.Add PDFfile
End If

Now I get the Msgbox, which is fine, but I still don't know how to trigger the MS Outlook dialog box for attachment selection.

Some solution here: How to open an Outlook excel attachment using Excel VBA, sent in a particular time range to a specific Outlook folder?

didn't bring an answer to me.

How can I open the Outlook dialog box from Excel VBA?

Can you not just check if the file exists before attempting to attach it to the email? i.e. `DIR(PDFfile)` will return an empty string if the file doesn't exist. If it doesn't, you can display a `Application.FileDialog` to get the file — Zac, Aug 05 '20 at 09:40

Geographos · Answer 1 · 2020-08-06T09:54:38.977

It looks like one of the solution is making the If statement inside of With OutlookMail section.

Next, we must set the variable for our manual attachment selection, where we use the Application.GetOpenFilename method as per the suggestion below.

https://www.mrexcel.com/board/threads/add-attachment-in-vba-macro-by-dialog-box.956227/

The Outlook section should look like this:

  With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
  .HTMLBody = "The job is done"

  .To = "xxx@xx.co.uk"  
  .CC = "yyy@yyy.co.uk; zzzz@zzz.co.uk;"
  .Subject = Filename & "- Audit"
  If Not Filename Like "?_V?" Then
  MsgBox ("The attachment wasn't found. Add it manually.")
  Dim Attac As Boolean  ' variable for our attachment adding manually
  Attac = Application.GetOpenFilename 
  .Attachments.Add Attac 
  Else
  .Attachments.Add PDFfile
  End If
  End With

As a result, our add attachment dialog box is opened with our ActiveWorkbook path.

If we want to have a specified file extension, like .pdf, then we must precise it in the Application.GetOpenFilename method like this:

     Attac = Application.GetOpenFilename("PDF Files, *.pdf")

Open dialog box to add attachment for Outlook email, when attachment not found

1 Answers1