Consulting

Results 1 to 8 of 8

Thread: code to open outlook

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    29
    Location

    code to open outlook

    Hi
    I have a phone book that I adapted from the internet which is sorting and working well
    I wanted to email straight from this form based on the search . I added " open email " command button and entered code from
    http://www.rondebruin.nl/win/winmail/Outlook/tips.htm . As I am brand new I have made an error but cant problem solve as I dont know enough

    Could I ask for some assistance here

    undergrad detail search 2019 - Copy.xlsm




    Sub Mail_small_Text_Outlook()'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Working in Office 2000-2016
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
    
        On Error Resume Next
        With OutMail
            .To = "txtMobile"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub

  2. #2
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    446
    Location
    In your form code

    Private Sub Openemail_Click()
        Mail_small_Text_Outlook txtMobile
    End Sub
    Change the red bits

    Sub Mail_small_Text_Outlook(Recipient As String)
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Working in Office 2000-2016
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
    
        On Error Resume Next
        With OutMail
            .To = Recipient
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            '.Send   'or use .Display
            .display
        End With
        On Error GoTo 0
    
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Aug 2019
    Posts
    29
    Location

    Red face

    Ok I am very literal and VBA illiterate .
    I have changed the code as per advice but nothing , probably , obviously doing something wrong .Outlook not even opening
    Sub Mail_small_Text_Outlook(Recipient As String)
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Working in Office 2000-2016
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
    
        On Error Resume Next
        With OutMail
            .To = "Recipient"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    I have added a button to the user form named openemail . I would like to have outlook open and have the "To' filed populated by what ever address is in the "email' box

    Advice please
    Attached Files Attached Files

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,889
    Location
    Why not attach the excel file rather than a word file? We have to guess at some things. The code should have worked but not with your specifics. You should use .Display rather than .Send for testing purposes. That is why the comment is there.

    Making a guess that email textbox is textbox4:
    .To = TextBox4

  5. #5
    VBAX Regular
    Joined
    Aug 2019
    Posts
    29
    Location
    Hi Kenneth
    I think the info got a bit twisted some where . I have a phone book which searches based on a " The select field" when selected the info presents in 6 fields below.
    I would like to:
    1. open out look via the open email button
    2. have a generic email which will open -- this is in rondiburin code
    3. have the "to " field populated based on the email address found in the email field of the user form which I think is text box TX mobile ( i haven't changed the names perhaps i should )

    I dont want to attach a word doc or file . The word doc was to show which filed I am getting the email address from

    Not sure where .To text box 4 is ?

    many thanks again

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,889
    Location
    Looking at your first file in the userform PhoneList, you can see that the textbox with label caption value of Mobile, that the control's name it txtEmail. So:

    .To = txtEmail
    I selected First Name in the userform and Tarek as first name. Nothing in the userform filled in and it said nothing was found. Looking at the data, the filtered range did show Tarek's data.

  7. #7
    VBAX Mentor paulked's Avatar
    Joined
    Apr 2006
    Posts
    446
    Location
    Quote Originally Posted by mml View Post
    Ok I am very literal and VBA illiterate .
    I have changed the code as per advice but nothing , probably , obviously doing something wrong .Outlook not even opening
    Sub Mail_small_Text_Outlook(Recipient As String)
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Working in Office 2000-2016
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
    
        On Error Resume Next
        With OutMail
            .To = "Recipient"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    I have added a button to the user form named openemail . I would like to have outlook open and have the "To' filed populated by what ever address is in the "email' box

    Advice please
    You've put Recipient in quotes, remove the quotes!

    Did you add?
    Private Sub Openemail_Click()
        Mail_small_Text_Outlook txtMobile
    End Sub
    undergrad detail search 2019 - Ked.xlsm
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Regular
    Joined
    Aug 2019
    Posts
    29
    Location
    Once again this forum has made my day !!! Thanks to Paulked and Kenneth for taking the time to respond ,your patience and assistance is greatly appreciated. You are adding to my knowledge along the way
    This solution works perfectly . Just hope the boss doesn't want anything more
    kindest regards

Posting Permissions

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