email completed multipage userform

[zeus]

Hello,
I have a 2 page multipage userform. I would like one user to fill out the fields on the first page, email the workbook containing the completed userform to a second user that will complete page 2 of the userform (I would like all the fields filled out by person 1 to remain populated so user 2 can see the entries). Is this possible? If it is possible, could you please provide some guidance as to the code required.

Thank you for your help!

 

[DanteAmor]

The data captured in a userform exist only in the memory, so that they can be consulted after closing the book, they must first be saved in a sheet, then when the book is opened and the userform is executed the data of the sheet must be loaded in the userform.

Create a sheet called "Temp"
On userform put the following code:

Private Sub CommandButton1_Click()
    'save data from page1, save copy of workbook and send email
    Dim h As Worksheet
    Set h = Sheets("Temp")
    h.Cells.Clear
    'save data in sheet Temp
    If TextBox1.Value = "" Then
        MsgBox "Enter data in textbox1"
        TextBox1.SetFocus
        Exit Sub
    End If
    'Put all the fields filled out by person 1 to user 2 can see the entries
    h.Range("A2").Value = TextBox1.Value
    h.Range("A3").Value = TextBox2.Value
    h.Range("A4").Value = ComboBox1.Value
    '
    'save copy of workbook
    ThisWorkbook.Save
    ruta = ThisWorkbook.Path & "\"
    arch = Replace(ThisWorkbook.Name, ".xlsm", "") & " " & Format(Date, "mm-dd-yyyy") & ".xlsm"
    ThisWorkbook.SaveCopyAs ruta & arch
    '
    'send email
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    dam.to = "email@gamil.com"
    dam.Subject = arch
    dam.Body = "complete page 2 of the userform "
    dam.Attachments.Add ruta & arch
    dam.Send                                'send
    'dam.Display                             'show
    '
    h.Range("A:A").ClearContents
    ThisWorkbook.Save
    MsgBox "File sent"
End Sub
'
Private Sub UserForm_Activate()
    Dim h As Worksheet
    Set h = Sheets("Temp")
    If h.Range("A2").Value <> "" Then
        'Put all the fields filled out by person 1 to user 2 can see the entries
        TextBox1.Value = h.Range("A2").Value
        TextBox2.Value = h.Range("A3").Value
        ComboBox1.Value = h.Range("A4").Value
    End If
End Sub

 

[zeus]

Thank you for your response. Great information to have. Do you know if it's possible to code it to automatically cycle thru every object on the userform and enter its value on the Temp sheet or do you need to manually identify every object and its destination cell? I have a lot of objects on my form so I was hoping for a nice shortcut.

 

[DanteAmor]

Try the following:

Private Sub CommandButton1_Click()
    'save data from page1, save copy of workbook and send email
    Dim h As Worksheet
    Dim n As Integer
    '
    Set h = Sheets("Temp")
    h.Cells.Clear
    'save data in sheet Temp
    If TextBox1.Value = "" Then
        MsgBox "Enter data in textbox1"
        TextBox1.SetFocus
        Exit Sub
    End If
    'Put all the fields filled out by person 1 to user 2 can see the entries
    n = 2
    For Each wcontrol In Me.Controls
        If TypeOf wcontrol Is MSForms.TextBox Or _
            TypeOf wcontrol Is MSForms.ComboBox Or _
            TypeOf wcontrol Is MSForms.CheckBox Or _
            TypeOf wcontrol Is MSForms.OptionButton Then
                h.Range("A" & n).Value = wcontrol.Name
                h.Range("B" & n).Value = wcontrol.Value
                n = n + 1
        End If
    Next
    '
    'save copy of workbook
    ThisWorkbook.Save
    ruta = ThisWorkbook.Path & "\"
    arch = Replace(ThisWorkbook.Name, ".xlsm", "") & " " & Format(Date, "mm-dd-yyyy") & ".xlsm"
    ThisWorkbook.SaveCopyAs ruta & arch
    '
    'send email
    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    dam.to = "email@gamil.com"
    dam.Subject = arch
    dam.Body = "complete page 2 of the userform "
    dam.Attachments.Add ruta & arch
    'dam.Send                                'El correo se envía en automático
    dam.Display                             'El correo se muestra
    '
    h.Range("A:A").ClearContents
    ThisWorkbook.Save
    MsgBox "File sent"
End Sub
'
Private Sub UserForm_Activate()
    Dim h As Worksheet
    Set h = Sheets("Temp")
    If h.Range("A2").Value <> "" Then
        'Put all the fields filled out by person 1 to user 2 can see the entries
        For i = 2 To h.Range("A" & Rows.Count).End(xlUp).Row
            Me.Controls(h.Range("A" & i).Value).Value = h.Range("B" & i).Value
        Next
    End If
End Sub

 

[zeus]

Thank you SO much! You're awesome

 

[DanteAmor]

I'm glad to help you