Search code examples
vbaoutlookformatselection

Add short signature + date to an existing paragraph of text and format the whole line


Problem:

I want to type a paragraph of text, add my short signature + date + time and format everything so another person would see I added this comment to the mail.

Example:

This is my personal comment on the topic // Signature Tom, 22.08.21, 14:00 (<- add the last part by VBA-Code and put this whole paragraph in red and italic by VBA)
Dear Sir or Madam
...-> mail body
Sincerely

What I have

So far it is two separate VBA sub routines I managed to create with trial and error from the web, but I would like it in one step (because I have to call them one after a time).

1st:

Option Explicit
Public Sub AddShortSignature()
    Dim xDoc As Object
    Dim xSel As Object
    
    On Error Resume Next
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            Set xDoc = Application.ActiveExplorer.Selection(1).GetInspector.WordEditor
        Case "Inspector"
            Set xDoc = Application.ActiveInspector.WordEditor
    End Select
    
    Set xSel = xDoc.Application.Selection
    xSel.InsertBefore Format(Now, "DD/MM/YYYY hh/mm")
    xSel.InsertBefore Format(" // Tom., ")
    
    Set xDoc = Nothing
    Set xSel = Nothing
    
    SendKeys "{End}", True
    SendKeys "+{Home}", True

End Sub

and 2nd, format everything to my liking:

Sub formateverything()

    Dim objDoc As Object
    Dim objSel As Object
    
    Set objDoc = ActiveInspector.WordEditor
    Set objSel = objDoc.Windows(1).Selection

    objSel.Font.Name = "Arial"
    objSel.Font.Italic = True
    objSel.Font.Bold = False
    objSel.Font.Underline = False
    objSel.Font.Color = RGB(0, 0, 0)
    objSel.Font.Size = 14
    
End Sub

Solution

  • As I can see in your pictures, your comment and signature comes at the top of the mail (first paragraph), so this is what I came up with.

    Public Sub OutlookMail_CommentAndSignature()
        Dim Ins As Outlook.Inspector
        Dim Doc As Object
        Dim mySignature As String
        Dim oPara As Object         ' paragraph
        Dim paraText As String      ' paragraph text
        Dim paraLength As Integer   ' paragraph length
        
        Set Ins = Application.ActiveInspector
        Set Doc = Ins.WordEditor
        
        ' Signature
        mySignature = " // Tom., " & Format(Now, "DD/MM/YYYY hh:mm") & vbCrLf
    
    '    ' First paragraph: comment + signature
    '    Set oPara = Doc.Paragraphs(1).Range
    '    paraLength = Len(oPara.Text)
    '    oPara.Text = Left(oPara.Text, paraLength - 1)   ' without vbCrLf
    '    oPara.Text = oPara.Text & mySignature
    
    '    ' format first paragraph
    '    oPara.Font.Italic = wdToggle
    '    oPara.Font.ColorIndex = wdRed
        
        ' Selected text
        Dim selRange As Object: Set selRange = Doc.Application.Selection.Range
    
        ' set text range to italic and red
        selRange.InsertAfter mySignature
    
        ' format first paragraph
        selRange.Font.Italic = wdToggle
        selRange.Font.ColorIndex = wdRed
    End Sub