Consulting

Results 1 to 3 of 3

Thread: "CC" field in mail has to be filled with userform's variable. It stays empty

  1. #1
    VBAX Newbie
    Joined
    Jan 2015
    Posts
    2
    Location

    "CC" field in mail has to be filled with userform's variable. It stays empty

    Hello there,

    I need a little help with my vba code.
    The idea is to have a userform with a multiple choice (option buttons) and an "ok" button.
    When the "ok" button is pressed, a new mail is created. The "CC" field has to be filled with an email adress depending on the choice made with the userform. (I replaced the mail adresses with [mail1] to [mail3].
    I tried to make different changes without success. Whatever I do, the "CC" field stays blanck. All the rest works fine.
    If anyone could have a look at my code bellow, and explain me what i'm doing wrong, it would really help me out.

    Thanks in advance.

    Best regards,

    Rvr.
    Public Sub CreateNewMessage()
        Dim strCustomer As String
        With CreateObject("Outlook.Application").CreateItem(0)
     
        UserForm1.Show
    
      .To = "[mail1]"
      .CC = strCustomer
    
      .Body = "xxx"
      .Subject = ""yyy"
       
      .Display
       Unload UserForm1
    End With
    Set objMsg = Nothing
    End Sub
    
    ---------------------------------------
    
    Private Sub UserForm_Initialize()
        optCustomer01.Value = True
    End Sub
    
    ---------------------------------------
    
    Private Sub cmdOK_Click()
           If optCustomer01.Value = True Then
        strCustomer = "[mail2]"
            End If
           If optCustomer02.Value = True Then
        strCustomer = "[mail3]"
            End If
        UserForm1.Hide
    End Sub

  2. #2
    VBAX Mentor skatonni's Avatar
    Joined
    Jun 2006
    Posts
    347
    Location
    This is one way to pass data from the userform.

    Option Explicit
     
    Public objMsg As mailitem    ' <----
     
    Public Sub CreateNewMessage()
       
        Dim strCustomer As String
           
        Set objMsg = CreateObject("Outlook.Application").CreateItem(0)
       
        With objMsg
                 
            UserForm1.Show
     
            .To = "[mail1]"
            .body = "xxx"
            .Subject = "yyy"
       
            Unload UserForm1
           
            .Display
            
        End With
       
        Set objMsg = Nothing
       
    End Sub
     
    Private Sub cmdOK_Click()
    
        If optCustomer01.Value = True Then
            strCustomer = "[mail2]"
        End If
    
        If optCustomer02.Value = True Then
            strCustomer = "[mail3]"
        End If
     
        objMsg.CC = strCustomer
     
        UserForm1.Hide
    
    End Sub

    This is a second way.

    Option Explicit
    
    Public Sub CreateNewMessage()
       
        Dim strCustomer As String
               
        With CreateObject("Outlook.Application").CreateItem(0)
                 
            .Display    ' <---
           
            UserForm1.Show
     
            .To = "[mail1]"
            .body = "xxx"
            .Subject = "yyy"
       
            Unload UserForm1
           
        End With
           
    End Sub
    
    Private Sub cmdOK_Click()
    
        Dim strCustomer As String
        Dim curritem As mailitem
    
        Set curritem = ActiveInspector.currentItem
     
        If optCustomer01.Value = True Then
            strCustomer = "[mail2]"
        End If
    
        If optCustomer02.Value = True Then
            strCustomer = "[mail3]"
        End If
    
        curritem.CC = strCustomer
    
        Set curritem = Nothing
    
        UserForm1.Hide
    
    End Sub
    To debug, mouse-click anywhere in the code. Press F8 repeatedly to step through the code. http://www.cpearson.com/excel/DebuggingVBA.aspx

    If your problem has been solved in your thread, mark the thread "Solved" by going to the "Thread Tools" dropdown at the top of the thread. You might also consider rating the thread by going to the "Rate Thread" dropdown.

  3. #3
    VBAX Newbie
    Joined
    Jan 2015
    Posts
    2
    Location
    Sorry, I did not had the time to reply earlier.
    Both solutions seem to work perfectly. Thanks a lot for your help, this will save me a lot of time over the next few months !

Posting Permissions

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