none
Add elements in email body depending on a form RRS feed

  • Question

  • Dear all,

    I'm calling for charity here as I have basically no knowledge at all in VBA. But I'm sure some of you will find it quite easy.

    I have created a form in Outlook

    This form will be launched from a specific button when replying to an email. So far so good.

    Here is what I would like to do :

    I want to insert a table in the email body :

    - Number of row depending on the form boxes checked
    - 2 columns (the first one with the name of the item checked and the second one empty

    I would also like to insert some text before the table and if possible a "Dear Mr/Ms" depending on the name of the person we are replying to.

    Maybe I'm asking a lot, let me know if this is hard to do or not.

    I found this link to insert directly in email body without creating a new email:
    https://stackoverflow.com/questions/42661190/how-to-insert-text-into-outlook-email-editor-using-vba

    Thanks in advance for your help.

    Regards,


    Wednesday, July 17, 2019 2:39 PM

Answers

  • It is not especially difficult. Basically you treat the message body as a Word document and program the document accordingly e.g. as follows.

    This is based on your userform illustration. The code goes in an ordinary Outlook module and the control button on the userform simply hides the form.

    As you have nopt indicated where the recipients name comes from, I have simply put Sir/Madam. If you want the name you need a text box on the userform to enter that name. Then you can replace Sir/Madam with the value from the text box e.g. oRng.Text = "Dear " & oFrm.TextBox1.Text & "," & vbCr & vbCr & strText

    Similarly you could add the e-mail address and message subject as required. 

    Option Explicit
    Sub Macro1()
    Dim olItem As MailItem
    Dim olInsp As Inspector
    Dim oFrm As UserForm2
    Dim oCtrl As Control
    Dim wdDoc As Object
    Dim oTable As Object
    Dim lngBorder As Long
    Dim oCell As Object
    Dim oRng As Object
    Dim i As Integer
    Dim iRow As Integer
    Const strText As String = "This is the text before the table." & vbCr & vbCr & _
          "It can be more than one paragraph as required." & vbCr & vbCr
    
        Set oFrm = New UserForm2
        With oFrm
            .Show
            iRow = 0
            For Each oCtrl In oFrm.Controls
                If TypeName(oCtrl) = "CheckBox" Then
                    If oCtrl.Value = True Then iRow = iRow + 1
                End If
            Next oCtrl
            Set olItem = CreateItem(olMailItem)
            With olItem
                .BodyFormat = olFormatHTML
                .Display
                .To = "someone@somewhere.com"
                .Subject = "This is the subject"
                Set olInsp = .GetInspector
                Set wdDoc = olInsp.WordEditor
                Set oRng = wdDoc.Range(0, 0)
                oRng.Text = "Dear Sir/Madam," & vbCr & vbCr & strText
                oRng.collapse 0
                Set oTable = wdDoc.Tables.Add(oRng, iRow, 2)
                With oTable
                    For lngBorder = -6 To -1
                        With .Borders(lngBorder)
                            .LineStyle = 1
                            .lineWidth = 4
                            .Color = 0
                        End With
                    Next lngBorder
                End With
                i = 0
                For Each oCtrl In oFrm.Controls
                    If TypeName(oCtrl) = "CheckBox" Then
                        If oCtrl.Value = True Then
                            i = i + 1
                            Set oCell = oTable.Cell(i, 1).Range
                            oCell.End = oCell.End - 1
                            oCell.Text = oCtrl.Caption
                        End If
                    End If
                Next oCtrl
            End With
        End With
    
        Unload oFrm
    lbl_Exit:
        Set oFrm = Nothing
        Set olItem = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
        Set oRng = Nothing
        Set oCell = Nothing
        Exit Sub
    End Sub
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by Chloroplaste Friday, July 19, 2019 12:34 PM
    Thursday, July 18, 2019 9:37 AM

All replies

  • It is not especially difficult. Basically you treat the message body as a Word document and program the document accordingly e.g. as follows.

    This is based on your userform illustration. The code goes in an ordinary Outlook module and the control button on the userform simply hides the form.

    As you have nopt indicated where the recipients name comes from, I have simply put Sir/Madam. If you want the name you need a text box on the userform to enter that name. Then you can replace Sir/Madam with the value from the text box e.g. oRng.Text = "Dear " & oFrm.TextBox1.Text & "," & vbCr & vbCr & strText

    Similarly you could add the e-mail address and message subject as required. 

    Option Explicit
    Sub Macro1()
    Dim olItem As MailItem
    Dim olInsp As Inspector
    Dim oFrm As UserForm2
    Dim oCtrl As Control
    Dim wdDoc As Object
    Dim oTable As Object
    Dim lngBorder As Long
    Dim oCell As Object
    Dim oRng As Object
    Dim i As Integer
    Dim iRow As Integer
    Const strText As String = "This is the text before the table." & vbCr & vbCr & _
          "It can be more than one paragraph as required." & vbCr & vbCr
    
        Set oFrm = New UserForm2
        With oFrm
            .Show
            iRow = 0
            For Each oCtrl In oFrm.Controls
                If TypeName(oCtrl) = "CheckBox" Then
                    If oCtrl.Value = True Then iRow = iRow + 1
                End If
            Next oCtrl
            Set olItem = CreateItem(olMailItem)
            With olItem
                .BodyFormat = olFormatHTML
                .Display
                .To = "someone@somewhere.com"
                .Subject = "This is the subject"
                Set olInsp = .GetInspector
                Set wdDoc = olInsp.WordEditor
                Set oRng = wdDoc.Range(0, 0)
                oRng.Text = "Dear Sir/Madam," & vbCr & vbCr & strText
                oRng.collapse 0
                Set oTable = wdDoc.Tables.Add(oRng, iRow, 2)
                With oTable
                    For lngBorder = -6 To -1
                        With .Borders(lngBorder)
                            .LineStyle = 1
                            .lineWidth = 4
                            .Color = 0
                        End With
                    Next lngBorder
                End With
                i = 0
                For Each oCtrl In oFrm.Controls
                    If TypeName(oCtrl) = "CheckBox" Then
                        If oCtrl.Value = True Then
                            i = i + 1
                            Set oCell = oTable.Cell(i, 1).Range
                            oCell.End = oCell.End - 1
                            oCell.Text = oCtrl.Caption
                        End If
                    End If
                Next oCtrl
            End With
        End With
    
        Unload oFrm
    lbl_Exit:
        Set oFrm = Nothing
        Set olItem = Nothing
        Set olInsp = Nothing
        Set wdDoc = Nothing
        Set oRng = Nothing
        Set oCell = Nothing
        Exit Sub
    End Sub
    


    Graham Mayor - Word MVP
    www.gmayor.com

    • Marked as answer by Chloroplaste Friday, July 19, 2019 12:34 PM
    Thursday, July 18, 2019 9:37 AM
  • Thanks a lot for you help.

    Your code seems to be a perfect fit except for the part where nothing happen when I click "Ok" on the form.

    I have an opened email which I am replying to.
    I clicked inside the email before launching the form. I can check several boxes, but then nothing happen when clicking "ok". I have to click on the red cross for the form to close and then it will open a new email with all the info in it.

    Indeed I see in your code

    Set olItem = CreateItem(olMailItem)"

    How can we proceed to insert the info in the active opened email ?

    Maybe something to do with that :

    http://www.vbforums.com/showthread.php?628044-Insert-text-into-current-outlook-message

    Thanks for your help.






    Thursday, July 18, 2019 12:27 PM
  • Ok, guess I was lucky, I manage to make it work. i just edited this part of the code by adding some lines of the link i posted on my message above. now here is the full code:

    Option Explicit
    Sub Reply_MissingInfo_EN()
    Dim oFrm As ReplyForm1_EN
    Dim oCtrl As Control
    Dim oTable As Object
    Dim lngBorder As Long
    Dim oCell As Object
    Dim oRng As Object
    Dim i As Integer
    Dim iRow As Integer
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
    Const strText As String = "This is the text before the table." & vbCr & vbCr & _
          "It can be more than one paragraph as required." & vbCr & vbCr
    
        Set oFrm = New ReplyForm1_EN
        With oFrm
            .Show
            iRow = 0
            For Each oCtrl In oFrm.Controls
                If TypeName(oCtrl) = "CheckBox" Then
                    If oCtrl.Value = True Then iRow = iRow + 1
                End If
            Next oCtrl
                Set objDoc = Application.ActiveInspector.WordEditor
            With objDoc
                Set objSel = objDoc.Windows(1).Selection
                Set oRng = objDoc.Range(0, 0)
                oRng.Text = "Dear Sir/Madam," & vbCr & vbCr & strText
                oRng.Collapse 0
                Set oTable = objDoc.Tables.Add(oRng, iRow, 2)
                With oTable
                    For lngBorder = -6 To -1
                        With .Borders(lngBorder)
                            .LineStyle = 1
                            .LineWidth = 4
                            .Color = 0
                        End With
                    Next lngBorder
                End With
                i = 0
                For Each oCtrl In oFrm.Controls
                    If TypeName(oCtrl) = "CheckBox" Then
                        If oCtrl.Value = True Then
                            i = i + 1
                            Set oCell = oTable.Cell(i, 1).Range
                            oCell.End = oCell.End - 1
                            oCell.Text = oCtrl.Caption
                        End If
                    End If
                Next oCtrl
            End With
        End With
    
        Unload oFrm
    lbl_Exit:
        Set oFrm = Nothing
        Set objSel = Nothing
        Set objDoc = Nothing
        Set oRng = Nothing
        Set oCell = Nothing
        Exit Sub
    End Sub




    One last thing, the "OK" button still not working. I have to close the form with the red cross.
    I tried both "Unload oFm" and "Unload UserFormName"
    Also tried the Hide function

    And try to paste this line of code either on module or directly in the code behind "OK" button.

    Not working in any case.



    Thursday, July 18, 2019 1:30 PM
  • Right click the OK button

    Select View Code.

    The Module should open at the sub.

    The code for the sub is simply 

    Hide

    or

    Me.Hide.

    'Unload' here will stop the macro .


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, July 19, 2019 3:00 AM
  • Thanks. It is working.

    Two additional questions :

    1) How can I add another text string after the table ?

    2) Instead of putting the code in an independant module, would it be possible to add it in the code behind the form (This would be more convenient for maintaining it and installing it on serveal computers) ?

    Thanks


    Friday, July 19, 2019 7:56 AM
  • Add another const to declare the second string e.g.

    Const strText2 As String = vbCr & "This is the text after the table." & vbCr & vbCr & _
          "It can be more than one paragraph as required." & vbCr

    Then locate the line i = 0 and above it add

    Set oRng = oTable.Range
        oRng.End = oRng.End + 1
        oRng.Collapse 0
        oRng.Text = strText2
    It would be possible to put the code in the form code, but it is preferable not to and it is not in any case difficult to export the module as well as the form.


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, July 19, 2019 11:31 AM
  • Thanks a lot for your help.

    Have a great weekend.

    Friday, July 19, 2019 12:34 PM