Results 1 to 8 of 8

GoTo or Run Code???

Threaded View

  1. #1
    Registered User
    Join Date
    04-09-2008
    Posts
    5

    GoTo or Run Code???

    What I am needing is a way to jump from one section of the module to another section. It runs a validation step first then once completed I need it to run the email portion of the code. I have colored "RED" the two sections I need it to go from and to. Any help on this would be appreciated I have searched all over the forum and the net trying to find a way to make this work. This module runs when one button is pushed. Unless theres a way to assign two modules to a button in excel

    Thanks
    Jennifer

    Sub test()
    Dim requiredCells As Variant
    Dim prompts As Variant
    Dim i As Long
    
     requiredCells = Array("a3", "d3", "h3", "k3")
     prompts = Array("Date", "Accounty Manager", "Warehouse From", "Warehouse To")
    
     For i = LBound(requiredCells) To UBound(requiredCells)
         If Range(requiredCells(i)).Value = vbNullString Then
             Range(requiredCells(i)).Value = userInput(prompts(i))
             If Range(requiredCells(i)).Value = vbNullString Then Exit Sub: Rem Cancel pressed
         End If
     Next i
    
    End Sub
    Function userInput(promptString As Variant) As String
    Do
        userInput = Application.InputBox("You must enter a " & promptString, Type:=2)
        If userInput = "False" Then userInput = vbNullString: Exit Function
    Loop Until userInput <> vbNullString
    End Function
    
    Sub Mail_workbook_Outlook_2()
    'Working in 2000-2007
        Dim wb1 As Workbook
        Dim wb2 As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim OutApp As Object
        Dim OutMail As Object
     
        Set wb1 = ActiveWorkbook
     
        If Val(Application.Version) >= 12 Then
            If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
                MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                       "Save the file first as xlsm and then try the macro again.", vbInformation
                Exit Sub
            End If
        End If
     
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        'Make a copy of the file/Open it/Mail it/Delete it
        'If you want to change the file name then change only TempFileName
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
        FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
     
        wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
        Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
     
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        Set OutMail = OutApp.CreateItem(0)
     
        On Error Resume Next
        With OutMail
            .To = "fizgigjk@yahoo.com"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add wb2.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
        wb2.Close SaveChanges:=False
     
        'Delete the file
        Kill TempFilePath & TempFileName & FileExtStr
     
        Set OutMail = Nothing
        Set OutApp = Nothing
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    End Sub
    Last edited by jkauffman; 04-09-2008 at 05:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1