Adding data from user form to email

Hi there,

I am trying to get a little more information on how to get this user form to function as I require it.

I have got to a stage now where I can make the user form send an email, the body of which I am working on getting to function. However I am unsure as to how to attach the details placed in the user form automatically into the email ready to be sent. I have attached a test work book & it's the sim refresh option that I am working on.

Would it be possible for anyone to assist with how to get the data input automatically?

Hi all,

Just to add in the code I think I'm working with that will send the email. I am getting an error

"Compile error
Expected end sub"

Private Sub CommandButton1_Click()
    Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi there," & _
              "<br><br><b>Tel:</b><br>" & _
              "Contract:<br>" & _
              "<br>Could you please refresh this SIM and let me know the results please. If this SIM has been cancelled if you could please forward a reinstatement onto billing." & _
              ""

    SigString = Environ("appdata") & _
                "C:\Users\rcranny\AppData\Roaming\Microsoft\Signatures\RESOLVE_Signature.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If

    On Error Resume Next

    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "ADT RESOLVE SIM CARD REFRESH"
        .HTMLBody = strbody & "<br>" & Signature
        .Send    'or use .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Unload Me
    End Sub
    End Sub
    


Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

In the email body where you can see "Tel" and "Contract" these are the data I would like input from the form.

Form functions as expected now, just the code that's having issues

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "<p style='font-family:calibri;font-size:11pt'>Hi there,</p>" & _
              "<p style='font-family:calibri;font-size:11pt'><b>Tel:<br>Contract:</b></p>" & _
              "<p style='font-family:calibri;font-size:11pt'><br>Could you please refresh this SIM and let me know the results please. If this SIM has been cancelled if you could please forward a reinstatement onto billing.</p>" & _
              "& Signature"

    SigString = Environ("appdata") & _
                "Users\rcranny\AppData\Roaming\Microsoft\Signatures\RESOLVE_Signature.htm"

    On Error Resume Next

    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "SIM CARD TEST"
        .HTMLBody = strbody & "<br>" & Signature
        .Display    'or use .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Unload Me
    End Sub
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Updated now, this is getting an email through as expected and is formatted correctly. However I cannot get the signature section to work.

Nor do I know how to input the data from my form onto the email. Any help?

This worked perfect thanks!!

Any ideas as to why the signature isn't working?

Sadly not literally so close now to having this part of the project done aswell.

Any other ideas on a way to work this as this code is one I've plucked from google

Private Sub CommandButton1_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "<p style='font-family:calibri;font-size:11pt'>Hi there,</p>" & _
              "<p style='font-family:calibri;font-size:11pt'><b>Tel: </b>" & simNumber.Text & "<br><b>Contract:</b> " & conNumber.Text & "</p>" & _
              "<p style='font-family:calibri;font-size:11pt'>Could you please refresh this SIM and let me know the results please. If this SIM has been cancelled if you could please forward a reinstatement onto billing.</p>" & _
              "<br>"
              
    SigString = Environ("appdata") & "Microsoft\Signatures\RESOLVE_Signature.htm"
              
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    


    On Error Resume Next

    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "SIM CARD TEST"
        .HTMLBody = strbody & vbNewLine & Signature
        .Send    'or use .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Unload Me
    End Sub

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Private Sub Label1_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Click()

End Sub

God damn file path, literally missing the beginning \

Thanks a lot for the help bakerman, managed to get it all sorted and working as I want it to be now
+ rep

Solved! Thanks