Pause Email Macro to Allow File Attachment

[MWeber2222]

All,

I have a Macro that automatically sends an email. I would like to include the following:

1. If the user selects "yes" in response to "do you need to include an attachment?", then a browser window opens inside of Excel and allows the user to select the appropriate file. Once selected the mailing Macro continues and the final email includes the attachment selected. [The answer to the question will reside in a named range (MergeRange15) ]
2. If the user selects "no", then the mailing Macro continues uninterrupted.

I have included the email-related part of the code I am using.

I appreciate any help that may be offered.

Thanks!
MW

    'Prepare for mailing
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Range("MergeRange12").Value
        .CC = ""
        .BCC = ""
        .Subject = "NSP Notification_XPS " & Range("MergeRange10").Value & " Assets for_" & Range("B15").Value & "_" & Range("A15").Value
        .HTMLBody = "Please acknowledge receipt of this device update." & RangetoHTML(rng)
        .Send  'or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

 

[Chihiro]

Stick this code before "With OutMail"

Msg1 = MsgBox("Do you need to include an attachement?", vbYesNo, "Attachment?")

Then edit "With OutMail" to "End With" part as following.

With OutMail
        .To = Range("MergeRange12").Value
        .CC = ""
        .BCC = ""
        .Subject = "NSP Notification_XPS " & Range("MergeRange10").Value & " Assets for_" & Range("B15").Value & "_" & Range("A15").Value
        .HTMLBody = "Please acknowledge receipt of this device update." & RangetoHTML(rng)
        If Msg1 = vbYes Then
            .Display
        Else
            .Send  'or use .Display
        End If
  End With

 

[MWeber2222]

Chihiro,

Worked beautifully. Thanks!

Regards,
MW