Add "Ok/Cancel" message box to my email script

[GamerNeelie]

I have a working email script that I've been using for sometime.

Once the user clicks a button it will run the script and open a new window for the email

However i would like a message box to appear asking the user if they would like to continue with the email (If they clicked it by mistake etc)

I just unsure where in the script I would add it for it to work correctly

Example of the kind of box i want to add

Dim answer As Integer
answer = MsgBox("Are you sure you wish to email", vbOKCancel)

Else
    MsgBox "Email Cancelled"

Example of my Email Script:

Sub Email1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String
    Dim filename1 As String

    filename1 = Sheet2.Range("AF20").Value & " - " & Sheet2.Range("AC21").Value & " " & Sheet2.Range("AC22").Value & " - " & Sheet2.Range("AC23").Value

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

    'On Error Resume Next
    
    With OutMail
        .To = Range("AJ18").Value
        .CC = "******"
        .BCC = ""
        .Subject = "********
        .Body = "*********

        .Attachments.Add ThisWorkbook.Path & Application.PathSeparator & filename1 & ".xlsm"
        
        '.Send
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Kill ThisWorkbook.Path & Application.PathSeparator & filename1 & ".xlsm"
End Sub

 

[dmt32]

Hi,
try placing at beginning of your code & see if does what you want

Sub Email1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String
    Dim filename1 As String
    
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Are you sure you wish to email?", vbOKCancel + vbQuestion, "Continue")
    If answer = vbCancel Then Exit Sub
    
    'rest of code

Dave

 

[AC PORTA VIA]

Sub Email1()
Dim answer As Integer
answer = MsgBox("Are you sure you wish to email", vbOKCancel)
If answer = vbOK Then
 
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String
    Dim filename1 As String


    filename1 = Sheet2.Range("AF20").Value & " - " & Sheet2.Range("AC21").Value & " " & Sheet2.Range("AC22").Value & " - " & Sheet2.Range("AC23").Value


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


    'On Error Resume Next
    
    With OutMail
        .To = Range("AJ18").Value
        .CC = "******"
        .BCC = ""
        .Subject = "********"
        .Body = "*********"


        .Attachments.Add ThisWorkbook.Path & Application.PathSeparator & filename1 & ".xlsm"
        
        '.Send
        .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Kill ThisWorkbook.Path & Application.PathSeparator & filename1 & ".xlsm"






    Else
    MsgBox "Email Cancelled"
End If
End Sub

 

[GamerNeelie]

Hi,
try placing at beginning of your code & see if does what you want

Sub Email1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewWB As String
    Dim filename1 As String
   
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Are you sure you wish to email?", vbOKCancel + vbQuestion, "Continue")
    If answer = vbCancel Then Exit Sub
   
    'rest of code

Dave

Thank you dave. That worked perfectly.

On the box message, I have alot of text, how can I add a line space between the text so i can spread it out a bit more?

Example:
Are you sure you wish to send this email?

(Text Text Text Text Text Text Text Text Text Text Text)

 

[dmt32]

Apply Carriage return to your string

have a read here: How to Add a New Line (Carriage Return) in a String in VBA

Dave

 

[GamerNeelie]

Apply Carriage return to your string

have a read here: How to Add a New Line (Carriage Return) in a String in VBA

Dave

Thank you Dave Got it all working. much appreciated

 

[dmt32]

Thank you Dave Got it all working. much appreciated

most welcome - appreciate your feedback

Dave