0

I have a VBA in Excel where users complete a form and then click the button to send it to a team email address. I want to be able to have a box come up prior to sending the email that allows users to attach external attachments to the email before sending. 

Private Sub CommandButton1_Click()
    Dim user
    Dim bookName As String
    Dim mailAns

    user = Environ("UserName")
    bookName = "General Order Form 2018" + " " + Format(CStr(Date), "dd-mm-yyyy") + "  " + user

    If MsgBox("Do you wish to send this General Order Form to Print Services?", vbYesNo) = vbNo Then
    End If

    With Sheets("Order Form 2019")
        If .CheckBox1 = True Then

            ThisWorkbook.SendMail Recipients:=Array("Print.Services@nationwide.co.uk"), Subject:="New Print Request", Returnreceipt:=True
            MsgBox "The Print Order Form has been sent to the Print Services Team, you will be contacted within 1 working day"
            ThisWorkbook.Close
            Exit Sub

        Else
            MsgBox "You have not completed the Declaration. Please accept the statements by clicking the checkbox"

        End If
    End With

End Sub
ZygD
  • 22,092
  • 39
  • 79
  • 102
Adamlh77
  • 19
  • 1
  • 4
  • The `SendMail` method does not have such option. You will have to rewrite your code using another method. Take a look at [this question](https://stackoverflow.com/questions/37302602/sending-email-attachments-from-excel-via-vba). – ZygD Feb 05 '19 at 14:00
  • The current method you're using to build the email doesn't allow for the adding of attachments. If Outlook is installed, then you can build an Outlook object (MailItem) and populate it with the `ActiveWorkbook` and other files that are selected with a file selector. Have a look at https://stackoverflow.com/a/35878897/7446760 – CLR Feb 05 '19 at 14:01
  • Hello, look in the object library. There is a parent Object also member of ThisWorkbook. "SendForReview". It might be what you need. – MrDogme Feb 05 '19 at 14:01
  • If you don't have Outlook installed look into using SMTP/CDO. RDB has a good article about it here: https://www.rondebruin.nl/win/s1/cdo.htm – Frank Ball Feb 05 '19 at 15:31

0 Answers0