Msgbox strange behaviour

[colinharwood]

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

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

 

[colinharwood]

Hi Guys
All sorted, I had used the same variable name for 2 different variables