Msgbox strange behaviour

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
Hi
I have the following code, which works as I want it until it reaches the end, when a msgbox is supposed to show, except that I actually get the file save dialogue box appear.
If I click on cancel it recycles and eventually comes up with an error
Any Ideas much appreciated

VBA Code:
Public Response As Variant

Sub CreateandSaveNewsletterToNewFile()
'
' saveNewsletterToNewFile Macro

    Unload Menu
    'Create Newsletter List
    ScreenUpdating = False
    Sheets("Newsletter").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Sheets("TMES Members").Copy After:=Sheets(3)
    Sheets("TMES Members (2)").Select
    Sheets("TMES Members (2)").Name = "Newsletter"
    Columns(10).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
    Columns(11).EntireColumn.Delete
  
    Range("A3").FormulaR1C1 = "Newsletter & E-News List"
    Application.DisplayAlerts = True
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    Selection.Cut
    Sheets("Newsletter").Copy
      
    Dim Path As String
    Dim Folder As String
    Dim Answer As VbMsgBoxResult
  
    'Update the path to a valid path on your PC
    Path = "D:\Tonbridge MES"
    Folder = Dir(Path, vbDirectory)
  
    If Folder = vbNullString Then
        Answer = MsgBox("Path D:\Tonbridge MES does not exist. Would you like to create it?        Yes = Create Path D:\Tonbridge MES & File will be attached to Emails          No = File Newsletter List.xlsm will be saved to your own location & will have to be attached to Email manually", vbYesNo, "Create the Path for Newsletter List.xlsm")
        Select Case Answer
            Case vbYes
                VBA.FileSystem.MkDir (Path)
                ActiveWorkbook.SaveAs FileName:="D:\Tonbridge MES\Newsletter List.xlsm", FileFormat:=52
                ActiveWorkbook.Close
                GoTo Ownsavelocation
            Case Else
               Response = InputBox("Please enter the location where you would like the file saved to.", _
               "Where would you like to save Newsletter List.xlsm ?")
               ' Create Folder using variable Response
               ' Check if cancel pressed
            Select Case StrPtr(Response)
                 Case 0
                 'OK not pressed
                 ActiveWorkbook.Close SaveChanges:=False
                 Exit Sub
                 Case Else
                 If Len(Dir(Response, vbDirectory)) = 0 Then
                 MkDir Response
              
                 End If
            End Select
                         
         On Error GoTo here

         ActiveWorkbook.SaveAs FileName:=Response & "\Newsletter List.xlsm", FileFormat:=52
         Workbooks("Newsletter List.xlsm").Close
         GoTo Ownsavelocation
       End Select
 
    End If
  
  
    ActiveWorkbook.SaveAs "D:\Tonbridge MES" & "\" & "Newsletter List.xlsm", FileFormat:=52
here:
    ActiveWorkbook.Close
  
    'ActiveWorkbook.FollowHyperlink Address:="mailto:[EMAIL]robinhoward@btinternet.com[/EMAIL]?to=[EMAIL]clivegil@btinternet.com[/EMAIL]&subject=Updated Membeship List&body=Hi All%0A%0APlease%20find%20attached%20an%20updated%20membership%20list.%0A%0ARegards%0A%0AColin", NewWindow:=True
  
Ownsavelocation:

    EmailClient = MsgBox("Do You Have Thunderbird Email Client installed", vbYesNo + vbQuestion, _
    "Create Emails to send < Newsletter List.xlsm >")
        Select Case EmailClient
           Case vbYes
              
                GoTo CreateEmail
            Case Else
                'MsgBox ("Sorry, can't create emails for you to send. Please create Emails manually  &  attach Newsletter List.xlsm from your saved location")
                Answer = MsgBox("Sorry, can't create emails for you to send. Please create Emails manually _      &  attach < Newsletter List.xlsm > from your saved location", vbOKOnly, "Thunderbird Email Client is not available")
              
            GoTo Label
             
         End Select

CreateEmail:

    ' Create 1st Email to send
    sCmd = "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird"
    Email = "[EMAIL]robinhoward@btinternet.com[/EMAIL]" 'Choose email address to send to
    Subject = "Updated Newsletter List"
    Content = "Hi Robin%0A%0APlease%20find%20attached%20an%20updated%20membership%20list.%0A%0ARegards%0A%0AColin"
    Attch = "D:\Tonbridge MES\Newsletter List.xlsm" ' File name and path, of file to attach
    sCmd = sCmd & " -compose " & "to=" & Email
    sCmd = sCmd & ",subject=" & Subject
    sCmd = sCmd & ",attachment=" & Attch
    sCmd = sCmd & ",body=" & Content
    'MsgBox sCmd
    Call Shell(sCmd, vbNormalFocus)

    ' Create 2nd Email to send
    sCmd = "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird"
    Email = "[EMAIL]clivegil@btinternet.com[/EMAIL]" 'fake email, change to a real one
    Subject = "Updated Newsletter List"
    Content = "Hi Clive%0A%0APlease%20find%20attached%20an%20updated%20membership%20list.%0A%0ARegards%0A%0AColin"
    Attch = "D:\Tonbridge MES\Newsletter List.xlsm" 'fake file name and path, change to a real one
    '
    sCmd = sCmd & " -compose " & "to=" & Email
    sCmd = sCmd & ",attachment=" & Attch
    sCmd = sCmd & ",body=" & Content
    'MsgBox sCmd
    Call Shell(sCmd, vbNormalFocus)
Label:
  
    Sheets("TMES Members").Activate
    MsgBox ("Newsletter.xlsm was saved to"), Response, vbOKOnly, "Your saved file location"
 
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
384
Office Version
  1. 2019
Platform
  1. Windows
Hi Guys
All sorted, I had used the same variable name for 2 different variables
 

Watch MrExcel Video

Forum statistics

Threads
1,127,640
Messages
5,626,046
Members
416,158
Latest member
CaliburBlade138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top