How do stop VBA pasting content under default signature in Outlook

[youngstubbs]

Hi all,

I'm completely new to VBA but have managed to piece together a few things I found online, and mostly understand what it's doing.

In order to copy specific cells from Excel and paste them into an Outlook email whilst retaining their appearance, I have the following code:

Private Sub CommandButton1_Click()

    Dim ol As Object 'Outlook.Application
    Dim olEmail As Object 'Outlook.MailItem
    Dim olInsp As Object 'Outlook.Inspector
    Dim wd As Object 'Word.Document
    Dim rCol As Collection, r As Range, i As Integer

     '/* if outlook is running use GO, create otherwise */
    Set ol = GetObject(Class:="Outlook.Application")
    Set olEmail = ol.CreateItem(0) 'olMailItem

    Set rCol = New Collection
    With rCol
        .Add Sheet1.Range("B6:C23") '/* add your ranges the same sequence */
    End With

    With olEmail
        .To = "firstemail@domain.com"
        .CC = "secondemail@domain.com"
        .Subject = "Data to action"
        Set olInsp = .GetInspector
        If olInsp.EditorType = 4 Then 'olEditorWord
            Set wd = olInsp.WordEditor
            For i = 1 To rCol.Count '/* iterate all ranges */
                Set r = rCol.Item(i): r.Copy
                wd.Range.InsertParagraphAfter
                wd.Paragraphs(wd.Paragraphs.Count).Range.PasteAndFormat 16
                '16 - wdFormatOriginalFormatting
            Next
        End If
        wd.Range.InsertParagraphAfter
        .Display
    End With

End Sub

This works perfectly except for one thing, and that is it pastes the content directly under the default signature, then places the cursor back above the signature.

I don't understand what's causing this, but would certainly be grateful for any suggestions. I have to retain use of the default signature as the workbook will be used by different users.

 

[Micron]

I presume the signature is already in the email object you created, so insertparagraphafter would automatically place the text after the signature? Word vba is not my thing, so that's just me trying to be logical. In testing, I find that if "signature" is at the top line of the email body and the cursor is on the line below, then wd.Range.InsertParagraphBefore seems to work. If there is no line below "signature" then that gets replaced with the range values.

       'wd.Range.InsertParagraphAfter
        wd.Range.InsertParagraphBefore
       ''wd.Range.InsertParagraph
         wd.Paragraphs(wd.Paragraphs.Count).Range.PasteAndFormat 16

An option might be to insert the signature after pasting?

 

[youngstubbs]

Sorry for the late reply. Thank you for that solution, which works, and is what has also been suggested elsewhere. I knew it would be something simple but having had to start my VBA journey by cobbling this together rather than learn the fundamentals from the beginning, I just couldn't quite see what to change. Thanks!!

 

[Micron]

Glad that you got it working and that I was able to help in some small way.