Results 1 to 5 of 5
Like Tree2Likes
  • 1 Post By lrobbo314
  • 1 Post By Smitty

VBA - How to include a popup message to confirm before executing the rest of the Macro?

This is a discussion on VBA - How to include a popup message to confirm before executing the rest of the Macro? within the Excel Questions forums, part of the Question Forums category; Hello, board! I am working on refining my macros... which you all have helped me develop and perfect. Now, I ...

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    181

    Default VBA - How to include a popup message to confirm before executing the rest of the Macro?

    Hello, board!

    I am working on refining my macros... which you all have helped me develop and perfect. Now, I would like to add one last function to the end of them that will automatically send the resulting report to the people who need to get it after it finishes saving. However, I have made the mistake of running the wrong Macro on a particular dataset, so I don't want my mistake blasted out to everyone in the universe. What I'd like to do is insert a command that will ask for me to confirm that I am peachy-keen before sending the file out, but I am not sure how to do that. Can someone tell me how to edit this to include a mandatory confirmation selection before going onto the outlook part of this macro?

    I think all of the code is correct, but I haven't actually tried it yet. Also, the code to create the report was condensed as I don't think it would be terribly relevant to anyone else. I've only included the very last part for saving - which I know works - and the new part for the outlook mail, which I haven't tested yet.



    The file saving part is put here because I need a particular date range in the file to appear in the file name along with the current date.
    Code:
        Dim Path As String
        Dim FileName1 As String
        Dim FileName2 As String
    
        Path = "C:\Documents and Settings\user\Me\Reports\Sample Report"
    
        FileName1 = "(" & Range("C3") & ")"
        FileName2 = Format(Now(), "mmddyy")
    
        ActiveWorkbook.SaveAs Filename:=Path & "_" & FileName1 & "_" & FileName2 & ".xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
        Rows("1:5").Select
        Selection.EntireRow.Hidden = True
       
    
    [[I'd like to have the popup message go here]]
    
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.logon
    Set OutMail = OutApp.CreateItem(0)
    
    On Error Resume Next
    With OutMail
    .TO = "email@email.com;emailxyz@email.com"
    .CC = "lorax@snoozing.com"
    .BCC = 
    .Subject = "Sample Report"
    .Body = "Hi%20All!%0A%0AAttached%20is%20the%20report%20for%20today.%20Let%20me%20know%20if%20you%20need%20anything%20else!%0A%0ALove,%0AVorlag"
    .Attachments.Add ActiveWorkbook.FullName
    .Display
    End With
    On Error Goto 0
    
    Set OutApp = Nothing
    Set OutMail = Nothing

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    895

    Default Re: VBA - How to include a popup message to confirm before executing the rest of the Macro?

    At the end of your code for the Outlook message, it says

    .Display

    Not .Send.

    So, the user will still need to hit the send button in Outlook. You don't really need to add this functionality to your code. But if you want something like this would work.

    Code:
    If MsgBox("Send Message", vbYesNo) = vbYes Then
        'code to send message
    Else
        Exit Sub 'terminate macro
    End If
    VorLag likes this.
    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

    If A is a success in life, then A equals x plus y plus z. Work is x; y is play; and z is keeping your mouth shut.

  3. #3
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    27,262

    Default Re: VBA - How to include a popup message to confirm before executing the rest of the Macro?

    You could try something along these lines:

    Sub foo()
        Dim ans As VbMsgBoxResult
        
        ans = MsgBox("Do you want to go ahead and send the report now?", vbQuestion + vbYesNoCancel, "Send Report?")
        
        If ans = vbYes Then
               Call SendMessage
        Else
            Exit Sub
        End If
        
    End Sub


    HTH,
    VorLag likes this.
    Smitty

    Every once in a while, there's a sudden gust of gravity...

    Check out my new book at the Mr. Excel Bookstore!

    Mr. Excel HTML Maker - Post a shot of your sheet

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    181

    Default Re: VBA - How to include a popup message to confirm before executing the rest of the Macro?

    Quote Originally Posted by lrobbo314 View Post
    At the end of your code for the Outlook message, it says

    .Display

    Not .Send.

    So, the user will still need to hit the send button in Outlook. You don't really need to add this functionality to your code. But if you want something like this would work.

    Code:
    If MsgBox("Send Message", vbYesNo) = vbYes Then
        'code to send message
    Else
        Exit Sub 'terminate macro
    End If


    Oh, I was wondering about that. Thanks for pointing that out!

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    181

    Default Re: VBA - How to include a popup message to confirm before executing the rest of the Macro?

    Quote Originally Posted by Smitty View Post
    You could try something along these lines:

    Sub foo()
    Dim ans As VbMsgBoxResult

    ans = MsgBox("Do you want to go ahead and send the report now?", vbQuestion + vbYesNoCancel, "Send Report?")

    If ans = vbYes Then
    Call SendMessage
    Else
    Exit Sub
    End If

    End Sub


    HTH,
    Good to know! Thank you!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com