Consulting

Results 1 to 2 of 2

Thread: Pasting a range into an email, while also adding text to body and keeping signature

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    1
    Location

    Question Pasting a range into an email, while also adding text to body and keeping signature

    Hi,
    So I am trying to create a script that will copy data from a range of cells on my spread sheet, open up a new email, paste in the range, add some text to the email body and contain my default email signature.

    The email should ideally look like:

    "Hi
    Blablabla example text"

    *Pasted range from excel*

    Signature


    I have worked out how to do most of this with bits of my own knowledge and some helpful tutorials however I cannot seem to get both the pasted range and the text body in the email without it removing my signature. Code so far:

    Private Sub CommandButton2_Click()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim wdDoc As Object
        Dim oRng As Object        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)    
        Range("B55:L58").Select
        Selection.Copy        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)  
        With OutMail
             .Display
             .To = "TestEmailAddress"
             .CC = "TestEmailAddress"
             .BCC = ""
             .Subject = "test"
             Set olInsp = .GetInspector
             Set wdDoc = olInsp.WordEditor
             Set oRng = wdDoc.Range
             oRng.collapse 1
             oRng.Paste
        End With
            End Sub
    This works fine for the range + signature, but if I try to add text to the body of the email with .Body or .HTMLBody, it either gets rid of my signature or the excel range. I am too new to VBA to understand why or how to fix this, can anyone assist?
    Using Outlook 2016/Excel 2016.
    Many thanks,

    Apoc
    Last edited by Aussiebear; 01-28-2025 at 03:20 AM.

Tags for this Thread

Posting Permissions

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